Formula to use Checkbox to "grab" value

Working on a budget & tracking base which has several phases (Not-Submitted, Submitted, Executed, Reconciled)

Trying to find a way to “lock” a value (i.e. grab the value of a cell when the checkbox is checked). Goal is to take the values when they are submitted and use them in the “executed” phase of the table without them ever changing.

Situation:
I build an order, then set the status to “submitted,” I’d like a new column to take the total of the order when I clicked “submitted” so that I can track performance.

Any help is greatly appreciated!

You could try something like this!

IF({Status} = "Submitted", {Order Total})

However, you can’t “lock” a value in most cases. If the {Order Total} field changes, so will the value of the above formula field. Airtable won’t remember what the value was when the checkbox was checked.

2 Likes

@Neads_Admin nailed it. Formulas can never lock values. They will always be driven by other field values. To truly lock a value, you will either have to enter it manually, or use some form of external integration to set a value in a non-formula field. This could be custom API code, or a service like Zapier or Integromat.

In this case, I would recommend Zapier. Create a new view in Airtable that uses a filter to only show records with a status of “Submitted,” then create a zap using the “New record in view” Airtable trigger, targeting that view. This will give you everything from the record that just changed status. The second step would set the value of a specific field in that same record to whatever is in the {Order Total} field.

1 Like