Mar 10, 2021 05:18 AM
Let’s say I have two fields sitting next to each other in a table. One is called Calculated and one is called Static. The Calculated field is, of course, a formula type field and the Static field is a currency type field.
I want to automatically save the result of the Calculated field as a static number in the Static field.
For example; if the Calculated field is a formula consisting of =10+5, then I want the static number 15 to appear in the Static field. I don’t want to formula itself in the Static field…just the result (15) of the formula from the Calculated field.
Does anyone know how I can accomplish this in Airtable?
Mar 10, 2021 05:27 AM
Welcome to the Airtable community!
You could use an automation with the “update” action to copy the value from the formula field to the editable field. However, you need to be careful about the interaction between data input and automation trigger. Airtable considers a field to be updated with each key press. So if you type a three digit number, that is three updates, not one. How to handle the situation depends on your workflow.
Another option is to use a button field that calls a script that copies the value. This method isn’t as automatic, but it puts a human in charge of knowing when data entry is complete.
Mar 10, 2021 06:57 AM
Thanks for the super helpful reply, @kuovonne! And thanks for welcoming me to the community. :slightly_smiling_face:
I’d prefer to have it automated and not require a button field to be clicked.
This is more or less the scenario I’m looking to achieve: " A computed field shows the price of an item in a linked record and you want to lock in the current price when a sale is made."
You mentioned that I need to be careful about the interaction between data input and automation trigger. I don’t think I’d actually be typing anything in this case because the Calculated Total Order Price rollup field is atomically calculated when a new “Order” is created. Then the automation would copy the result to the Static Total Order Price field so that we can lock it in as a static number.
Would something like that work with an automation or would I still run into trouble with the interaction between input and trigger?
Mar 10, 2021 07:14 AM
How you configure the trigger for the automation depends on how the order is created and when the order is “finished”. For example, if you add line items manually, then the rollup will be recalculated as each line item is added. This means that you probably do not want to directly use the “when updated” trigger, as the automation would run for each line item, not just when the final total is calculated.
I recommend examining your workflows to determine how to best identify when an order is “finished”. Sometimes this is when a particular field is filled in. Sometimes it is when a certain amount of time has passed since the last input. Sometimes it is when all required fields have valid values. Sometimes it is when a user pushes a button.