Automatically Populate One Cell in Last Row of a Data Set

Topic Labels: Formulas
2649 2
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

I have created a long list of concert dates in AirTable. I group the list based on artist name, and sort by concert date, so each concert date for a given artist appears chronologically in one data set.

For an integration with another system, I need the last date listed for an artist to have a check box checked in the same row as that final concert date. Keep in mind also that as concert dates are announced, they get added to the list, at which point the check box for the previous last date must be unchecked, and the new final date check box should be checked.

My process now is that I manually review the list and uncheck the previous last concert date, and check the updated last concert date.

Any thoughts on automating this process?

2 Replies 2
Airtable Employee
Airtable Employee

You can set this up as follows:

  1. Convert the Artist name to a linked table, if it isn’t already
  2. Create a rollup field in the Artists table, rolling up the concert date field of the concerts table, with the formula MAX(values). This should give you the last concert date for each artist.
  3. Create a lookup field in the Concerts table, looking up the last concert date for each Artist.
  4. Create a formula field in the Concerts table, comparing the concert record’s date field with the last concert date for each artist. This will serve as the checkbox field that you want - it will automatically show one thing if the concert is most recent, another thing otherwise.

For more examples of this workflow, see these other threads:

I see Matt slipped in as I was answering this. :winking_face:

You can also check my reply to a similar question from a couple of days ago, which included a simple base to demonstrate how this works.