Skip to main content
Solved

Formula field efficiency

  • November 14, 2022
  • 5 replies
  • 32 views
F
Forum|alt.badge.img+9

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?

Best answer by kuovonne

Nope. You’ve listed the two options.

  • Have the formula calculate the value twice (once for the condition, and again for the output)
  • Use a helper field that calculates the output

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.

5 replies

N
Forum|alt.badge.img+13

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.

F
kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • Answer
  • November 16, 2022

Nope. You’ve listed the two options.

  • Have the formula calculate the value twice (once for the condition, and again for the output)
  • Use a helper field that calculates the output

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.

M
F
F
Forum|alt.badge.img+9
  • Faculty_Schedul
  • Author
  • Known Participant
  • November 16, 2022

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.

kuovonne
kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • November 16, 2022

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.


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.

N
N
Forum|alt.badge.img+13

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.


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.

  • I second @kuovonne’s suggestion of using a prefix of some kind (a gear emoji, or just a short acronym of somekind) to make it easier to identify auxiliary fields and so they are grouped together alphabetically various lists
  • fill in a description of the field explaining precisely what it’s used for
  • consider putting access restrictions on the field if you have other “normal users” in your base who should not be fiddling with these fields
    • note that restricting field editing permissions doesn’t actually stop users from editing the field’s configuration. This isn’t a security recommendation but rather another strong UX cue to user’s that they should think twice before messing with this field.
Terms & ConditionsAccessibility statement