Formula to get a field from another record based on it's date

[Chris_Dukes]

• My table lists all the video episodes that my company releases.
• Each Episode’s Record has an Air Date date field.
• Each Episode’s Record also has a Previous Episode field that has the Project Name of the last released episode.
• The last released episode is based on the Air Date of the most recent previous episode.

Currently, I manually add the previous episode’s Project Name to the Previous Episode field of each Episode’s Record. I want to use a formula to fill the Previous Episode field automatically.

Can you even use a formula to reference a field in a different record from the same table?

[Carl]

I don’t know of a way to use a formula to reference another record in the table. That’s typically through automations. I know how to set up such an automation, but it involves creating a handful of formulas & a linked table. Someone else might know a quicker way, but you can see how I did it here: Airtable - Testing Base

Set up a new table with a record that you link every episode to

In that new table, create a Rollup field looking at the Date field with formula MAX(values)

In the original table, add a lookup field pulling the Max Date Rollup you just created.

Create a formula field with the formula: IF(DATETIME_DIFF({Max Date Rollup (from Dummy Table)},Date,‘days’)>0,DATETIME_DIFF({Max Date Rollup (from Dummy Table)},Date,‘days’))

This formula gives the difference between the max date, as long as the difference is greater than 0 (because you don’t want the current episode)

Go back to that new linked table and do another rollup field looking at the difference formula, and use MIN(values).

Returning to the original table, create a lookup pulling that Min Values rollup.

Now you can create a formula field to identify the previous episode. IF({min date diff lookup}={date difference},‘Previous Episode’)

Lastly, create an automation that when a record in the original table matches condition, Date is not empty, run action “find records” where “is previous episode” is not empty. Then a second action “update record” using the record ID from the trigger, and then you can pull the name from the Find Records step to update the Previous Episode field.

[Chris_Dukes]

Carl:

Set up a new table with a record that you link every episode to

How do I do that? Will this article help?

Airtable Support

Linking existing records using automations

The more comfortable you get with automations, the more you’ll come to realize their flexibility in being leveraged in complex workflows. This article will go over how to set conditions to update e...

[Carl]

Yea, that will show you + a bit more. But you should be able to click on the testing base and make a duplicate, which has all the formulas and the automation I mention set up. Your examples are the table called “All Episodes” and “Dummy Table.”

For the new table, just create a table and call it “Dummy Table” or something. Then in the episodes table you create a new field and there’s an option to “Link to another record” so click on that. Link to that Dummy Table. And then create a Dummy Record that you then put in that field for every episode. (To do that quickly, fill the top one and then click the right corner of the cell and drag downwards. It will fill down like in Excel.)

[Chris_Dukes]

There are a lot of episodes so I think I need an automation to populate the Table 1 field for the Dummy Link record in the Dummy Table…or am I the dummy? :grinning_face_with_sweat:

[Carl]

Change the formula for “Is Previous Episode” to

IF({date difference} > 0,IF({min date diff lookup}={date difference},'Previous Episode'))

Now you don’t need to go back and link every record, just the most recent two episodes & any record going forward. You could create an automation that automatically links new records.

I assume you aren’t trying to update each record with its previous episode?

[Carl]

You might also play with the trigger for automating the previous episode text if you don’t want it triggering too fast while you’re adding an episode.

[Chris_Dukes]

I don’t enter the new records so the field needs to be populated when the record’s Air Date field is filled. I used that article and figured out how to automate linking all of the episodes in the original table. The automation’s triggered when the Air Date is added.

I went through all your instructions but I’m having a strange problem where the Max Date Rollup field in my Original Table is skipping over records. The Max Date Rollup field in the New Table works fine.

[Carl]

Did you confirm that the skipped records are linked to the same record in PROMO TEST?
That’s the only reason the lookup shouldn’t be working.

[Chris_Dukes]

Yup, that’s the problem. I think something’s wrong in my automation trigger.