Hello, I work with ships and would like to create a grams/day formula which takes the date from our last interaction (order) with that ship to measure from. I cannot work out how to have it search for the previous record to take that as the date to give the days passed value for the equation.
Anyone have any tips?
Hey
Airtable works in a different way! You cannot reference a different record on a formula. To solve this, you’ll need to link the records (either manually or via automation) and have a lookup for the date field. For more info on lookups you can take a look at this article.
Only then you’ll be able to use such date on your formula!
Mike, Consultant @ Automatic Nation
Does this look right? I’ve set it up here for you to check out. We can’t use formula fields for this so I ended up using a linked field + automation to handle it, and the automation looks for the latest ‘Interaction’ record for the selected Ship and links it together
The caveat here is that it assumes the record you’re running it on is always the latest record though, i.e. if you ran the automation on a record for 1 June, and you had another unlinked record for 2 June, then it’d link to the 2 June one instead
The automation’s kind of tricky to set up and requires a Scripts action; you can duplicate the base into your own workspace to copy the automation settings so you should be good!
Even though Airtable looks like a spreadsheet, it is really a database. In a database, each row is separate. Airtable formulas only work with data in the same row.
That means if you want to use the record before, you will need to do a few extra steps. I have written a detailed article on how to do this, you can check it out.
Taha, Airtable Advisor
If you DM me an invite link to your base I’d be happy to help you take a look at it!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.