Is There a Way to Get the Most Recent Prior Value of a Field in a Table (from Prior Row)?

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.

Hope this helps! If you’d like to hire the best Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

Does this look right?

If so, I’ve set it up here for you to check out!

Set up might get confusing, so I suggest duplicating the base into your own workspace and poking at it heh

1. Create a new table where you’ll create a single record to link all your existing records to.  I called mine ‘Rollup’
2. In ‘Rollup’, create a rollup field on the ‘Autonumber’ field with the formula ‘MAX(values)’ to display the latest Autonumber value from Table 1
3. In ‘Table 1’, create a rollup field on the field from step 2, with the formula ‘SUM(values) = Autonumber’ that’ll help you identify the latest record
4. In ‘Rollup’, repeat step 2, but this time use a conditional filter to only display the Autonumber where the field from step 3 is not equals to 1

   1. 

5. In ‘Table 1’, create a rollup field on the field from step 4, with the formula ‘SUM(values) = Autonumber’ that’ll help you identify the latest record

6. In ‘Rollup’, create a lookup field to display the value you want by using a conditional:

   1. 

       

7. In ‘Table 1’, create a lookup field to display the field we created in step 6