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

[oli]

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

[kev1n]

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');

[oli]

woorks great ... thanks for your fast and great reply !

[oli]

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

[kev1n]

Can you visualise a little, show screenshots, sql statements etc? It's hard to tell what's going wrong without having more details.

[oli]

Please find below some more information:

This is the object „auf_fahrzeug“ which is defined on the form „frm_auftrag“ (Table: „auftrag“).

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 (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 (142.33 KiB) Viewed 39 times

Please let me know if further information is required.

[kev1n]

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()

[oli]

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"]"]

[kev1n]

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

?

[oli]

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?