Is there a way to look up the latest column (most recent data)

Topic Labels: Base design
1524 6
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

I’ve seen a few answers to this in terms of looking up the most recent row, but how do I get a field that will link to the most recently created column?

I am setting up an Airtable to manage the 31 preschools that our organisation feeds. On one table we will keep track of the number of children attending monthly (so we know how much food to order). So far it looks like this:

Screenshot 2022-01-26 at 12.38.17

How do I get a field in the master table that can update the current number of children at the creche by pulling in the most recents months’ data?

Screenshot 2022-01-26 at 12.38.27

Does that make sense as a question? You are all always so awesome , so thanks in advance!

6 Replies 6

Notice that as time goes by, you keep needing to add new columns. In general, this is very common for a spreadsheet, but bad practice for a database for like Airtable.

Instead I recommend a two table system. On table for your preschool names, and another linked table for the months. The months table would have three columns: the link to the preschool, the month (probably represented by a date field with the first of the month), and the number of children.

This way as time passes, your table gets taller instead of wider. And you are back to the process of finding the latest row in a set of linked records.

Thanks so much. So like this?
Screenshot 2022-01-26 at 15.05.17

And then just add a new set of rows for each month?

Almost. Instead of having your preschool names in the primary field, create a new linked record field to a new table with one row for each preschool. The primary field can then be a formula field that combines the preschool name with the month.

Then in the table of preschools, you can have rollup fields that tell you things like, average enrollment per month, enrollment for the current month, etc. This support page can help you get started with rollup fields. You can also look at the other posts on getting the lastest linked record.

Screenshot 2022-01-27 at 08.35.57

Is this better?

Then I would do the roll up under the other table (ECDs)?

Screenshot 2022-01-27 at 08.47.11

I think I got it! Does this look right?

Looks good!

Depending on your data entry process, you might need to adjust your rollup configuration.

I also recommend that you combine the two tables of numbers into a single table with both/all years. That way you will not need to modify your base structure as the years go by. To see the individual years, use filtered views.