Skip to main content

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

  • January 26, 2022
  • 6 replies
  • 39 views
K
Forum|alt.badge.img+4

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:

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?

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

6 replies

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • 6009 replies
  • January 26, 2022

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.

K
Forum|alt.badge.img+4
  • Kate_Jacobs
  • Author
  • Known Participant
  • 12 replies
  • January 26, 2022

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?

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

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • 6009 replies
  • January 26, 2022

Thanks so much. So like this?

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.

K
Forum|alt.badge.img+4
  • Kate_Jacobs
  • Author
  • Known Participant
  • 12 replies
  • January 27, 2022

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.


Is this better?

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

K
Forum|alt.badge.img+4
  • Kate_Jacobs
  • Author
  • Known Participant
  • 12 replies
  • January 27, 2022

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.


I think I got it! Does this look right?

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • 6009 replies
  • January 27, 2022

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.


Terms & ConditionsAccessibility statement