Set value from other table in Lookup Field for a new Record

Questions related to using nuBuilder Forte.

Set value from other table in Lookup Field for a new Record

Unread postby oli » Tue Apr 06, 2021 11:54 pm

Dear All,

as I couldn't find a working solution for following problem i hope you can help me.

If a new record on form "rechnung" is created, a default value should be set for a lookup field on this form.

The ID which should be set as a default value could be found with following SQL statement:
SELECT firmendaten_id FROM firmendaten WHERE fir_default = 1

I get a result with this statement by testing the query in phpMyAdmin but I couldn't set this ID into the lookup field "rech_firma" on form "rechnung".

Any ideas how to solve this?

Thanks in advance!

BR, Oli
oli
 
Posts: 35
Joined: Sat Mar 20, 2021 11:52 pm
nuBuilder Version: 4.5

Re: Set value from other table in Lookup Field for a new Rec

Unread postby kev1n » Wed Apr 07, 2021 12:40 am

2 possibilities:

1. Retrieve the ID with a Procedure and call nuGetLookupId().

Or:

2. Create a Display Object with your SQL. When the form loads, call

Code: Select all
nuGetLookupId($('#yourDisplayObjectID').val(), 'rech_firma');
kev1n
nuBuilder Team
 
Posts: 1622
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Set value from other table in Lookup Field for a new Rec

Unread postby oli » Wed Apr 07, 2021 2:07 am

woorks great ... thanks for your fast and great reply !
oli
 
Posts: 35
Joined: Sat Mar 20, 2021 11:52 pm
nuBuilder Version: 4.5

Re: Set value from other table in Lookup Field for a new Rec

Unread postby oli » Fri Apr 09, 2021 12:00 am

Hello,

thanks for the previous solution!

What I still couldn't solve is to update a select field (mutliselect) by the ID from another Multiselect field.

e.g.
In table "auftrag" I have the field "auf_fahrzeug" which is containing one or many ID's of table "fahrzeug".
After creating a new "rechnung" and assigning a "auftrag" to this new "rechnung" I also want to derive the "auf_fahrzeug" content (all ID's) into the field "rech_fahrzeug" (Mutliselect field on "rechnung").

This field stays empty. I can fill the ID's into another temp display field by using a SQL SELECT statement ... but I need the ID's in the Mutliselect field ... any ideas ??

Thanks in advance!

BR,
Oli
oli
 
Posts: 35
Joined: Sat Mar 20, 2021 11:52 pm
nuBuilder Version: 4.5

Re: Set value from other table in Lookup Field for a new Rec

Unread postby kev1n » Fri Apr 09, 2021 1:15 am

Can you visualise a little, show screenshots, sql statements etc? It's hard to tell what's going wrong without having more details.
kev1n
nuBuilder Team
 
Posts: 1622
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Set value from other table in Lookup Field for a new Rec

Unread postby oli » Fri Apr 09, 2021 3:39 am

Please find below some more information:

This is the object „auf_fahrzeug“ which is defined on the form „frm_auftrag“ (Table: „auftrag“).
1.jpg
1.jpg (117.66 KiB) Viewed 39 times

It is possible to assign multiple cars (fahrzeuge) to one record „auftrag“.

In a next step a new record „rechnung“ should be created: Form „frm_rechnung“ (Table: „rechnung“)
Here I can assign the previous created „auftrag“ record (yellow marked LOOKUP field „Auftrag“):
2-Auftrag.jpg
2-Auftrag.jpg (120.47 KiB) Viewed 39 times

All red marked fields should be inherited and filled from the selected „Auftrag“ and it works for field "Kunde".
I added following PHP code to the LOOKUP Object „Auftrag“ on the FORM for „Rechnung“:

$l = nuLookupRecord();
nuSetFormValue("rech_fahrzeug", $l->auf_fahrzeug);
nuSetFormValue("rech_kunde", $l->auf_kunde);


This works for the field „Kunde“ (another Lookup on Form „Rechnung“)
But it doesn’t work for the field „Fahrzeug“ (which is a Multi-Select Field on Form „Rechnung“).
3-Rechnung.jpg
3-Rechnung.jpg (142.33 KiB) Viewed 39 times


Please let me know if further information is required.
oli
 
Posts: 35
Joined: Sat Mar 20, 2021 11:52 pm
nuBuilder Version: 4.5

Re: Set value from other table in Lookup Field for a new Rec

Unread postby kev1n » Fri Apr 09, 2021 4:24 am

I haven't tested it myself but I believe that you can't set a value of a select using nuSetFormValue(). IIRC there has been a post about that in the forum.

You'd have to set to value into a temporary field and from there transfer it to the select:

Code: Select all
$('#$auf_fahrzeug  option[value="'+$('#temp_field').val()+'"]').prop("selected", true)
$('#auf_fahrzeug').change()
kev1n
nuBuilder Team
 
Posts: 1622
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Set value from other table in Lookup Field for a new Rec

Unread postby oli » Fri Apr 09, 2021 4:31 pm

I used following script in the JS section of the lookup object for "auftrag":
Code: Select all
$('#$auf_fahrzeug  option[value="'+$('#rech_fahrzeug_temp').val()+'"]').prop("selected", true);
$('#auf_fahrzeug').change();


This is the content of the temp field "rech_fahrzeug_temp"
Code: Select all
["605703003789f26","6058badcf273770"]


I get following error in the browser console:
Code: Select all
jquery.js?ts=20210409063013:2 Uncaught Error: Syntax error, unrecognized expression:
#$auf_fahrzeug  option[value="["605703003789f26","6058badcf273770"]"]
oli
 
Posts: 35
Joined: Sat Mar 20, 2021 11:52 pm
nuBuilder Version: 4.5

Re: Set value from other table in Lookup Field for a new Rec

Unread postby kev1n » Fri Apr 09, 2021 5:13 pm

What I don't understand is why the temp field contains an array with 2 values.

Is that values really coming from the After Save ?
Code: Select all
$l->auf_fahrzeug

?
kev1n
nuBuilder Team
 
Posts: 1622
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Set value from other table in Lookup Field for a new Rec

Unread postby oli » Fri Apr 09, 2021 5:26 pm

kev1n wrote:What I don't understand is why the temp field contains an array with 2 values.

Is that values really coming from the After Save ?
Code: Select all
$l->auf_fahrzeug

?


I fill the field in the After Browse of the Lookup object for "auftrag":
Code: Select all
nuSetFormValue("rech_fahrzeug_temp", $l->auf_fahrzeug);


Is this the wrong place?
oli
 
Posts: 35
Joined: Sat Mar 20, 2021 11:52 pm
nuBuilder Version: 4.5

Next

Return to General

Who is online

Users browsing this forum: No registered users and 16 guests