How can I set an Airtable formula variable of a field that only change when other field change

[Mestyz]

Hi guys....

I have two columns "Old Price" and "New Price".

I want to achieve something like this:

When "New Price" updates, then the previous "New Price" before the update becomes the "Old Price".

Here (below) is how the code I want to achieve look like in programming.

Var = Old Price

Var = Prev New Price = 0

IF(New Price updates)

Old Price == Prev New Price

Prev New Price == New Price

[TheTimeSavingCo]

Hmm I think this can't be done with a formula and you'll need an automation and a historical field for this I'm afraid:

The idea is to log the 'New' price so that we can check whether the new value matches the old one, and then if it doesn't to trigger an automation that'll log the old price before the change

 Link to base

In the gif above, the automation's set to trigger when the formula field outputs 'Update', and the formula is as follows:

IF(
  New!={New - Historical},
  'Update'
)

Note that this is a simplified example and that there might be issues running this automation if the price change is keyed in manually digit by digit.  To get around that, you'd either trigger the automation manually via a checkbox, or check how long it's been since the field's been modified and trigger the automation 5 min after the price is updated.  The latter option is automated, but could cause performance issues in very large bases due to its usage of NOW()