Is There a Way to Get the Most Recent Prior Value of a Field in a Table (from Prior Row)?
In order to compute a change in a value between a prior record and a newly inserted record, is there a way to get the most recent prior value for a field in a table?
Assume table is sorted (e.g., by DateTime).
Ideally with a formula something like the pseudocode below where the “Prior_Value” and “Current_Value” are a number, such as: an amount, a quantity, a weight, a height, a length, et cetera.
That sort of formula works in a spreadsheet, but not in Airtable because formulas can only see data within the same record.
Additionally. there is no inherent order to the records — even if they are sorted. So there is no concept of “prior record” or “next record”.
You would need to manually tell Airtable what you consider to be the “previous/old record”.
To do this, you would need to create a linked record field and manually link it to whatever you consider the “previous/old record” to be.
Then, you can pull in values from that old/previous record using lookup fields or rollup fields. From there, you can create a formula that looks at the lookup fields/rollup fields.
One other thing you could do is setup an automation to create your new records for you. As a part of your automation, you can set it to automatically link the old record for you.
On your previous/old record, you would trigger your automation with a checkbox or a single-select field. Then, your automation can create the new record for you, and your automation can also set the linked record field to link to the old/previous record that triggered the automation.
