How to set value in a record based on another record

[Drew_Jackson]

Hi guys! New user here, feel like this should be a simple one but just can’t figure it out.

I have a table for Athletes and a table for their AthleteRecords. On the AthleteRecords table each record has an ID, and lists the Athletes schools (HS and College). I have a field in the Athlete table that is set to the ID of the most recent AthleteRecord for that athlete, and I would like to pull the SchoolType field from the AthleteRecords table that matches that ID and set it to a field in the Athlete table. This way I can have a field in the athlete table that shows the athletes “Current School Level” based on their most recent school entry. Thank you for the help I cant figure this one out!

[ScottWorld · Accepted solution]

Welcome to the community, @Drew_Jackson!

Airtable doesn’t use ID’s for linking… all linking is done manually by you in Airtable. So the first step will be for you to manually link all of your records between the 2 tables.

To learn all about linking records, you may want to check out my free Airtable training course.

After that, however, you will still have the issue of only showing the information from the most recent linked record.

To do this, you’ll need to use a trick which I describe in detail on this episode of the BuiltOnAir podcast:

[TheTimeSavingCo]

Hey Drew, I’ve put something together here that does what you’re looking for I think

When the Get School Type checkbox is marked, it’ll look for the record in the AthleteRecords table where its Record ID value matches the ID of the most recent record that you’ve set, and then populate the SchoolType field via an automation

===

While the above would do the trick, you can consider this alternate method as well if you’d like. With this method, you won’t need to manually set the ID of the most recent record yourself; as long as the AthleteRecords records are linked to the appropriate Athlete records, the school type will also be updated to display the latest school type via a lookup field

The idea is that we use a rollup field in the Athletes table on the Created Date field of the AthleteRecords table with the formula MAX(values) to identify the most recent entry. (This would be the field Newest Record Created Time)

We then pass that value back into AthleteRecords via a lookup field (This would be the field Newest Record Created Time - Lookup), and use a formula field to identify the latest submission record for that person (This would be field Is Newest Record). We then use that formula field (Is Newest Record) in our conditional lookups to only display the data from that latest submission

To view the formulas, you can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button

Slightly more complicated than what you were looking for, but would save you a bunch of manual work! Feel free to message me if you have any questions!

[Drew_Jackson]

First let me start with @ScottWorld is amazing, and let me say this is a great way to get a new subscriber! I already have to say one week in and this community is amazing. I am definitely going to have to throw you some support and I will check out the training course good sir. I really appreciate it. Your podcast really brokedown the issue in detail and with context explaining the platform and how linking and everything works. You sir are awesome!

[ScottWorld]

Haha, that is so awesome to hear, @Drew_Jackson!! Thank you so much for your kind words!! :grinning_face_with_big_eyes:

[ScottWorld]

p.s. Also, @Drew_Jackson, I don’t run that podcast, but if you want to subscribe to my Airtable tips & tricks newsletter, you can do that on my website at the link below. I don’t send out too many emails — maybe one email every 2 months.

ScottWorld • Best Airtable Consultants, Airtable Experts, Airtable Consulting,...

ScottWorld • Best Airtable Consultants, Airtable Experts, Airtable...

ScottWorld provides the world's best Airtable consultants, expert Airtable training, best Airtable consulting, and Claris FileMaker Pro developers.

[Drew_Jackson]

Just subscribed! And I am sure in the future I will need some Airtable consulting, added you in my CRM :slightly_smiling_face:

[Drew_Jackson]

Super smart solution @Adam_TheTimeSavingCo thanks for all the help, going to test this one out tonight too!! Thanks!