Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 14, 2022 11:01 AM
When creating a formula field with an IF statement where I want to check the value being returned before deciding to use that value or display something else I run into an issue where it seems terribly inefficient to have to basically have to call all the functions twice.
IF({INSERT CONDITION HERE WITH A LOT OF FUNTIONS} > 0, {INSERT CONDITION HERE WITH A LOT OF FUNTIONS}, “NO”)
Is there any way to avoid having to basically calculate a value for something twice when using it in an If statement? I’d hate to have to make a field just to hold calculated values that are only useful so that the IF statement doesn’t need to calculate the same thing twice.
Our fields are already a bit bloated as it stands.
Any creative solutions?
Solved! Go to Solution.
Nov 15, 2022 09:53 PM
Nope. You’ve listed the two options.
Formula fields are essential a single expression that can have nested expressions. However, you cannot save a value to a variable in a single formula field. In order to save a value to a variable, you need to put it in a different field.
Thus, you have to strike a balance between really long formulas or a proliferation of helper fields. Usually I go with the really long formula and follow a strict style guide to make the formula readable, especially if there are lots of fields in the table. On the other hand, if one of the intermediate values might be used elsewhere in the base, I will go ahead and create the helper field.
Nov 15, 2022 08:48 PM
One common approach in this scenario is to move INSERT CONDITION HERE WITH A LOT OF FUNCTIONS
into its own formula field. Then your primary formula field can just reference that helper column each time. You can, of course, hide the helper column from your views so it isn’t in the way.
Nov 15, 2022 09:53 PM
Nope. You’ve listed the two options.
Formula fields are essential a single expression that can have nested expressions. However, you cannot save a value to a variable in a single formula field. In order to save a value to a variable, you need to put it in a different field.
Thus, you have to strike a balance between really long formulas or a proliferation of helper fields. Usually I go with the really long formula and follow a strict style guide to make the formula readable, especially if there are lots of fields in the table. On the other hand, if one of the intermediate values might be used elsewhere in the base, I will go ahead and create the helper field.
Nov 16, 2022 08:17 AM
I appreciate both your responses. I just wanted to make sure I wasn’t missing something. It hurts a little each time that I create these elaborate functions that have so much redundancy, but It makes sense to break it out in a field by itself.
I think I need to be better at formatting my formulas since it seems to hold that formatting even after submit and also field naming convention so that people know that these weird extra fields are probably being used by formulas or automations or other and don’t get deleted.
Nov 16, 2022 09:53 AM
A while ago, I spent a lot of time deciding how to format my formulas. I came up with a method that works pretty well for me. You can see it in the majority of formulas that I have posted over the past year. Mostly it involves starting a new line for each parameter of a function and nesting indents.
Some people use a gear emoji at the start of field names to indicate “system” fields that should not be deleted or altered. Using Airtable’s tools to trace field dependencies also really helps. Some of the field dependency features are enterprise only, but I’ve been noticing access to some of them in pro workspaces as well.
Nov 16, 2022 10:07 AM
To add on to @kuovonne’s excellent responses, I highly recommend getting into consistent habits about naming fields and about using the field description to convey information about architecture and dependencies. I spent several weeks on a client project this year that involved backwards engineering a gigantic base with a huge number of fields.