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]

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.

[Chris_Dukes]

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.

[Carl]

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