I Want to look at one field's contents and based on data in that populate another field

[grexr]

I am working on a database with info taken from an old access database. There are five Tables.

Every one of them has a number assocated with a record along with a name or description. 

Example

Contacts: Contact ID, First Name, Last Name

College: College ID, College Name

Products: Product ID, Description

On the Contacts table I created a new forumula field that joins the Contact ID, First and Last Names to make Field "Name | Contact ID"

This method was followed for the other tables.

When I want to link the contact to the college on the Contacts Table, I have the original College ID field (that came from Access) but I want to link the "College ID | School Name". So, I can see the original College ID and I have to type for what / search for the number and automatically have it populate with the correct school 

I want to write a script or something or an automation that looks that the data in the College ID column and goes and finds the matching College ID | School Name data and inserts it.

Can that be done?

[TheTimeSavingCo]

Yeap, that's doable.  If this is a one off thing I'd suggest just doing this manually though, and you can try:

1. Change the "College" table's primary field to be the ID field
2. In the "Contacts" table, click the field header for the "College ID" field in the "Contacts" table, selecting the entire column
3. Hit CMD / CTRL + C
4. In the "Contacts" table, click the field header for the linked field to the "College" table
5. Hit CMD / CTRL + V

This should automatically link everything up for you, and you can swap out the primary field of the "College" table again
---

If it's not a one off, then yeah, an automation would work really well for this.  You could set it up so that it triggers when the "College ID" field is not empty and the link to the "College" field is empty, and its action would be to:
1. Find a record in the "College" table with the same "College ID" value
2. Update the triggering record by linking it to the found record

Airtable has a guide on how to do a Find record + Update record action here: https://support.airtable.com/docs/use-automations-to-timestamp-status-updates

[grexr]

I will try this. Thanks. It is not a one off there are other tables as well. I just gave one example

[jsep]

If you need help applying Adam's recommendations, feel free to schedule a free call with me for assistance.