Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 07, 2022 07:31 PM
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!
Solved! Go to Solution.
Aug 07, 2022 07:44 PM
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:
Aug 07, 2022 07:44 PM
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:
Aug 07, 2022 09:18 PM
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!
Aug 08, 2022 12:01 PM
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!
Aug 08, 2022 01:01 PM
Haha, that is so awesome to hear, @Drew_Jackson!! Thank you so much for your kind words!! :grinning_face_with_big_eyes:
Aug 08, 2022 01:03 PM
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.
Aug 08, 2022 02:34 PM
Just subscribed! And I am sure in the future I will need some Airtable consulting, added you in my CRM :slightly_smiling_face:
Aug 08, 2022 04:21 PM
Super smart solution @Adam_TheTimeSavingCo thanks for all the help, going to test this one out tonight too!! Thanks!