Foreign Key Lookups in an Edit form

Forgive me if I have missed this in a lesson or documentation. I’m still feeling like a baffled newbie.

I have a “Status” table that I want to use to show the status of a “Job”. The “Status” table is a simple lookup: “status_id” is a long, “status_name” is a string. There is a 1:Many relationship with “Job” , and it also has a field called “status_id”.

On the edit form I would like to populate the “status_id” field from a combo box with the relevant “status_name” showing along with a hidden column that would have the corresponding status_id value. “Open”, “In Progress” and “Completed” are more user-friendly than 1, 2 or 7.

I tried adding a “COMBO” control, but got lost with the “From” property because if I click on the “…” button for the property it only allows me to select one field from the foreign table, and I need 2.

I also experimented with adding a “FieldLookupButton” Control Template, but didn’t have much success there either.

I feel I’m so close but so far at the same time.

You want a control template called FileDrop.
1st Define the relationship and add to the file list the same as you did for the browse. Add the fileDrop template. Choose your Status table for the file. Make sure you choose a description key. On the template prompts theres a place to assign field values. Thats where you assign the code. Check the box to populate if empty.
The control properties use variable receives the first field in the list. I make this the status table decription field.
All should now work

1 Like