Calculate a link to another record by matching values

[Jonathan_McCarv]

I have 2 tables. One is for form submissions where people attending trainings enter their information and the numeric ID of the training they attended. The second table is key value pairs matching a training ID to its name.

I need to look for the ID that matches the number the user entered and if found display the training name. If not found flag the record for followup.

I will not have the people filling out the form select from the list of trainings as it will eventually be thousands of lines long.

[Justin_Barrett · Accepted solution]

Welcome to the community, @Jonathan_McCarver! :grinning_face_with_big_eyes: While I can appreciate your desire to avoid showing the form users such a long list, setting up Airtable to make that match for you can be a bit tricky. Because Airtable will let users search in a list of linked records in a form, I feel that the link option might actually be easier than you think.

Your training session table could probably be modified fairly easily. While you didn’t offer many specific details about its setup, my hunch is that the ID is in the primary field, with the name in another field. If that’s the case, duplicate the primary field to put the IDs into another non-primary field, then turn the primary field into a formula that combines both ID and name, like this (guessing the field names):

ID & ": " & Name

That will create records with primary field names like this (again using dummy data for this example):

0001: Session Name A
0002: Session Name B
0003: Session Name C
…

In your table that’s populated by the form, you would have a link field to select from that training table. If someone using your form attended “Session Name ZZSQW”, they just have to type its number—or even part of its number—and it will be isolated for selection:

[Jonathan_McCarv]

Thank you, that will work well for users.

[Justin_Barrett]

Glad to know that you got the answer you were seeking! If you would, please mark my comment above as the solution to your question. This helps others who may be searching with similar questions. Thanks!