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

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.

How do I do that? Will this article help?

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.)

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:

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?

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.

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.

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.

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

Did you end up solving that?

If you change the formula for “Is Previous Episode” in my example so that it only looks at things that have a date difference, then you don’t need to go back and link all the old episodes. Just new ones going forward. And that automation can be triggered anytime a record is created, update the link field.

Hi, thanks for sticking with me Carl! Yeah, I figured out the trigger. That was a good point about the trigger firing too fast, but I’m going to tweak that more later on. Right now, I’m using a Checkbox field to trigger the automation and I’m using a view that only has a dozen records.

Now, everything is working perfectly according to your instructions!

But, I think I failed to clearly describe my goal. I realized this method only marks a single record with “Previous Episode”. I need all of the records to have the Project Name of its preceding episode. I’ll make a screenshot with desired results on it or I’ll share a test base.

Yea, I misunderstood your original goal there. Since you said you currently manually add the previous episode, I assumed you had already manually added all the previous episodes and you just wanted to automate updating that same field going forward. This process marks the previous episode to the current episode, and then updates your existing field with the previous episode title. It also doesn’t create a link if you’re trying to create a linked record (though you could just convert the previous episode name field into a linked field and link to the same table).