When linking two tables, a lookup field is created by TrackVia in the child table that populates a drop down based on what you select as the ID in a parent.
For example, if you have a Company table (parent) and an Employee table (child), where the ID in the Company table is the Company Name, when adding records to the Employee table, there will be a field that lists all the Company Names. Selecting a Company Name will connect the Employee record to the Company table.
This parent child link is the only way to populate a field with drop down options from another table. Please let us know if you have any further questions.
Regards,
Jake
0
Permanently deleted user
Alexcommented · ·
Thanks Jake,
What I have is a town table containing town, zip & 3x character town abbreviation which I need to populate a drop down in the "From & "To" fields of the Bookings table where freight/consignment notes are entered or imported.
I will also need the same scenario with customers, loading/unloading docks, etc. being in individual tables of which one field is to be the data validation requirement (obviously the same methodology would be used for all instances...yes?)
Can you just step me through this this once please?
Cheers mate...
0
Permanently deleted user
Jake Marwilcommented · ·
Hi Alex,
If you click the Admin button in the top of your screen and click EDIT next to your app name, you'll be taken to the table relationship page. Next to your parent table name (Towns), there is a small symbol to create the hierarchy between two tables. When you do this, you will be able to name the lookup field that will populate your drop down. You can name this "From". Once you click Save, you can then click the small hierarchy symbol again to create a second lookup field which you will call "To". Click save.
Based on your description, if the Town table is your parent table, in the table builder you could create a calculated text field that concatenates the town, zip and 3x character town abbreviation fields to make one long string of text. You could separate it using hyphens with a formula like this:
town & " - " & zip & " - " & 3x character town abbreviation
Then you can change the ID to be the new field you created. Once you do this, the lookup field in the child Bookings table will be a drop down of that newly created field in the Bookings table.
Your Bookings table will now contain the "From" and "To" lookup fields which will be drop down fields populated with the text field you just created. These parent child lookup fields are used to connect records between the Town and Bookings table. Please let us know if you have any questions.
Comments
3 comments
Hi Alex,
When linking two tables, a lookup field is created by TrackVia in the child table that populates a drop down based on what you select as the ID in a parent.
For example, if you have a Company table (parent) and an Employee table (child), where the ID in the Company table is the Company Name, when adding records to the Employee table, there will be a field that lists all the Company Names. Selecting a Company Name will connect the Employee record to the Company table.
This parent child link is the only way to populate a field with drop down options from another table. Please let us know if you have any further questions.
Regards,
Jake
Thanks Jake,
What I have is a town table containing town, zip & 3x character town abbreviation which I need to populate a drop down in the "From & "To" fields of the Bookings table where freight/consignment notes are entered or imported.
I will also need the same scenario with customers, loading/unloading docks, etc. being in individual tables of which one field is to be the data validation requirement (obviously the same methodology would be used for all instances...yes?)
Can you just step me through this this once please?
Cheers mate...
Hi Alex,
If you click the Admin button in the top of your screen and click EDIT next to your app name, you'll be taken to the table relationship page. Next to your parent table name (Towns), there is a small symbol to create the hierarchy between two tables. When you do this, you will be able to name the lookup field that will populate your drop down. You can name this "From". Once you click Save, you can then click the small hierarchy symbol again to create a second lookup field which you will call "To". Click save.
Based on your description, if the Town table is your parent table, in the table builder you could create a calculated text field that concatenates the town, zip and 3x character town abbreviation fields to make one long string of text. You could separate it using hyphens with a formula like this:
town & " - " & zip & " - " & 3x character town abbreviation
Then you can change the ID to be the new field you created. Once you do this, the lookup field in the child Bookings table will be a drop down of that newly created field in the Bookings table.
Your Bookings table will now contain the "From" and "To" lookup fields which will be drop down fields populated with the text field you just created. These parent child lookup fields are used to connect records between the Town and Bookings table. Please let us know if you have any questions.
Regards,
Jake
Please sign in to leave a comment.