[Solved] Data dropdown list fails to save entry linked to foreign key

[Ale_110]

Hi there,
I have been experiencing troubles with a silly problem but I cannot get it fixed.
I would simply like to use a data dropdown function to select a value that is linked to another table. That is,
I have EVENT table that has contact_id label that is link to CONTACT table. I have get contact_id (the primary key lable of the CONTACT Table) to show me the value linked to the primary key which is first_and_last_name. Now whenever I select the value that is shown as a first_and_last_name and try to save the record the value gets deleted in the form and resets to empty. If i changed the table directly with the CONTACT_ID numbers linked to the other table it gets saved. If I also change the control configuration to view the CONTACT_ID to show me the actual IDs of the referenced table and select any number in the form, the entry does not get registered and the data validation box resets to empty once again. Below are the configuration of the data field:

Capture2.PNG (30.47 KiB) Viewed 4399 times

Capture.PNG (35.17 KiB) Viewed 4399 times

the sql in list content is: SELECT "first_and_last_name", "first_and_last_name" FROM CONTACT

[FJCC]

The query for your list box should be

Code: Select all

SELECT "first_and_last_name", "CONTACT_ID" FROM CONTACT

with the Bound Field on the Data tab of the control dialog set to 1. That will display the first_and_last_name field but store the CONTACT_ID.
This is explained with a little more detail in the Getting Started Guides's chapter on Base . Look for the section Adding a List Box that is well down the page.

[Villeroy]

You may use this demo as a reference for one-to-many and many-to-many relations. In this demo, each person has one animal.

[Nick N,]

Hello Alex,

Could you please take a look at following form?

Please make sure PK and FK cascade option is ON. If you choose the combox, your cell will display only the ID value.
Better to select listbox because you may display the VARCHAR.

Please find enclosed a test ODB file for yr reference.

Regards

Nick

[Ale_110]

Hi there,
Thank you very much for the guides, tutorials and test files, I will take a deeper look whenever I have time. I think that the solution that worked for me was to first of all set the variable as text [varchar] rather than integer (which as far as I remember was the problem) and change the following SQL code : SELECT "first_and_last_name", "first_and_last_name" FROM CONTACT ORDER BY "first_and_last_name". I believer that main problem was the variable type which, coupled with the SQL code, prevented the entry to be saved. Anyway, so far so good, I will dig deeper in order to tune it better but it is all good now! Thanks for the support.

[Ale_110]

Thank you Villeroy and Nick for the database shared, I checked them and quite understand the difference in putting ID compared to directly inserting the name as I did. This explains the code inserted in both databases, in relation2listbox.odb: "SELECT "Name", "ID" FROM "Animals" ORDER BY "Name" ASC"
and in testify.odb this: "SELECT "client_name", "ID_client" FROM "Clients"".

Cheers,
Ale