save bound value from dropdown

[Ewald]

I have a 1:1 relation between table 1 and table 2 .
Table 1 has the primary key from table 2 as foreign key.
On a form populated with table 1 Values I want a dropdown pulling a value from table 2 and I want the primary key value from table 2 (which is the bound value of the drop down) saved in the foreign key field of table 1.
How do I do that?

[admin]

Ewald,

You can use a Lookup for picking the FK for table1.

But I'm not sure I understand the question.


Steven

[kev1n]

Using a select object (dropdown), you can enter an SQL:

https://wiki.nubuilder.cloud/ ... b_-_Select

Something like:

Code: Select all

SELECT PRIMARY_KEY_TABLE2, SOME_VALUE_TABLE2 FROM TABLE2

The first selected value (PRIMARY_KEY_TABLE2) is the dropdown's bound value, the second one (OME_VALUE_TABLE2) its display value. While the display value is displayed in the drop-down list, the bound value is stored in the database.

[Ewald]

well, that's what I read too in the handbook and in the posts about this topic. However, if I try to do it that way it doesn't work.

I have a form based on table 1 which has a foreign key for the table 2 value, based on which I create the dropdown. I use the SQL code and the display value is visible, but the foreign key is not transfered. When I select a value from the dropdown it is visible on the form but when I hit save it dissapears again and the foreign key value in the table doesn't change. Obviously I am missing something.

[kev1n]

It would certainly help if you could upload some screenshots of the object settings where we see the SQL, the Id of the object.

[Ewald]

OK, here are some details:
Table 1 has among other fields has a foreign key field IDTypeAppraisal.
Table 2 has a primary Key IDTypeAppraisal and a field TypeAppraisal.

The form (see Attachment) shows two fields and the foreign key field of table 1. The dropdown shows the TypeAppraisal from table 2.
What I want to do is to save the bound field of the dropdown in the foreign key field of table 1.

What happens is: I select a TypeAppraisal value, it is shown properly (see Fig. 1) but upon hitting save the dropdown is empty again and the value in the foreign key field in table 1 doesn't change.

[kev1n]

I tried to reproduce your issue but I was not able to. The saving works for me.

First, I created two tables:

Table 1: tbTest (this corresponds to your "Table 1")

tbTest.png

Table 2: tbTypeAppraisal:

tbTypeAppraisal.png

Then I added two rows with dummy data in table: tbTypeAppraisal

tbTypeAppraisal_data.png

Then I created a nuBuilder form with an object IDTypeAppraisal:

All.png

..with this SQL:

sql.png

Finally, I picked a value from the dropdown and saved the form

As you see, the data is saved in the table:

tbTest_data.png

[Ewald]

well, it looks to like I did exactly what you did. I'll start from scratch following your outline and keep you posted.
Thanks for the effort.

[Ewald]

It works now, the problem was that I had the foreign key field that I wanted to change according to the selection from the dropdown on the same form. When I removed that field everything worked as expected.
In conclusion, you can't have the the field which is supposed to be populated or changed on the same form as the dropdown.

[kev1n]

Great! I am glad you figured it out!
Do you mean there where 2 objects on your form with the same Object Id? Yes, this leads to problems when saving.