I’m certainly no expert in matters of formula computation rules internal to Airtable but it seems that the one aspect missing from this particular solution is the ability of formula fields to be able to know if they have already performed the calculation at least once. Since a formula field – by definition – uses the formula results as a proxy for the “field” value, it has no way to recursively inspect itself, right?
Formula Fields Lack Inspectable State
Lacking an ability to inspect the value of itself nor the ability to write values into a real data field, it cannot process the logic required to know if it has already computed the needed geo-encoding value once before. Ergo, it keeps processing and running up a tab for this customer’s account. It is simply a dumb function that has no idea if it has previously executed once, 20 times, 2,000 times, or never before.
That said, I’m pretty sure (correct me if I’m missing something) you can’t create a formula scenario with logic that says simply -
If this formula has already computed a value, stop computing a value.
What if …
What if we hypothesize for a moment and imagine that formulas are simply attributes of actual data fields. A real field with an attached formula could inspect the value in the field and if NULL, execute the formula repeatedly and as the computation engine calls for, but update the field itself only once. A lovely benefit of this model also emerges because deleting any existing value in such a field would cause the formula to reassert its logic, but only once if it was created with the specified conditional logic as noted in the diagram.
The design might look something like this:
My observation is simple - formula fields are not able to leverage the current value of themselves; ergo - they lack the ability to leverage their own state. In cases like this – and I assert, there are many others – the ability to use the previous results of a formula in future formulaic processing makes it possible to address many complex requirements involving computations and formula logic.
Workaround?
I tried to imagine a number of workarounds for this user’s situation and all of them led [me] to the conclusion that I needed to include a real data field with some sort of external scripting process - Script Block or API. Perhaps I’m not clever enough to come up with an approach using formulas only, but it looks like users are in a box when depending on formula fields to create dynamic data values that must mimic actual field values or suffer some rather nasty consequences.
Got a better idea for this user? Would love to learn how to do this a better way - I just can’t envision one.