Automatically Populate One Cell in Last Row of a Data Set

[Steve_Hoffman]

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?

[Matt_Bush]

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:

Most Recent Date from another table Ask the community

Hi Arlo! There’s a way to do this, but it’s a bit convoluted, so bear with me :slight_smile: Use the MAX(values) trick described above to pull the most recent transaction date into your clients table. Create a lookup field in your transactions table that looks up the most recent date you just calculated in the client table. Create a formula field in your transactions table that compares the most recent transaction date that you just looked up to the date of this specific transaction. If they’r…

Embedding other data in 'most-recent' date Show and tell

@W_Vann_Hall nice hack! It is in fact possible to set this up using a few intermediate lookup and rollup fields: On the item table, add a Rollup of the date field from the outRecord table (which let’s call “Checkout date”), with the formula MAX(values). Let’s call it “Most Recent Checkout”. On the outRecord table, add a Lookup of the “Most Recent Checkout” field from the item table. On the outRecord table, create a Formula field IF({Checkout date} = {Most Recent Checkout}, Status, ''), call i…

[W_Vann_Hall]

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.

Lookup to show latest entry

There is a somewhat clunky way of doing this: In your 'Notes' table, create a field called "createdTime". Make it a Formula field with the formula CREATED_TIME(). In your 'Companies' table, create a field called "latestNoteDate". Make it a Rollup field that rolls up the "createdTime" field of the 'Notes' table with a method of MAX(values). This will store the date and time of the most recent note for each company. In your 'Notes' table, create a field called "latestNoteDate". Make it a Look…