How to set dynamic variables in formulas (not a value of a variable)?

Question

How can I implement such a formula in Airtable?
// all fields in an Airtable table
const allFields = [
  {
    key: 'type',
    value: 'TypeOne',
  }
  {
    key: 'name',
    value: 'Some name',
  },
  {
    key: 'description',
    value: '',
  },
]

// set in a field of the table
const requiredFields = [
  {
    type: 'TypeOne',
    requiredFields: ['name', 'description'],
  },
  {
    type: 'TypeTwo',
    requiredFields: ['name'],
  },
]

let invalid = 0

for (let i in requiredFields) {
  if (!allFields[requiredFields[i]]) invalid += 1
}

if (invalid) return '⚠️ Some fields are not filled out!'

For now, I have to do something like:
IF(type = 'TypeOne', IF(OR(name = 0, description = 0), '⚠️ Some fields are not filled out!'))
& IF(type = 'TypeTwo', IF(OR(name = 0), '⚠️ Some fields are not filled out!'))

But I would like to set just an array of required fields but I don’t know how to dynamically set a variable like at this point allFields[requiredFields[i]] (not a value of some predefined variable but a variable itself).

Justin_Barrett · Accepted Answer

Airtable formulas don’t currently support variables. The formula structure you showed is pretty much the way it’s done based on the example you provided. It can be optimized a bit further, though. Here’s what I came up with:

IF(
    AND(Type, NOT(
        SWITCH(
            Type,
            "TypeOne", AND(Name, Description),
            "TypeTwo", Name
        )
    )), "⚠️ Some fields are not filled out!"
)

Here’s how that breaks down, starting on the inside.

The SWITCH() function is optimized for creating specific output based on what’s found in a single field. In this case, I’m checking the {Type} field. If it contains “TypeOne”, it will return the result of checking the contents of both {Name} and {Description} (if you put just a field name in a test like that, it will return True if the field is non-empty, and false if empty). For the “TypeTwo” option, putting the field reference alone works because of what’s happening next.

Once the SWITCH() function is processed, it goes to the NOT() function, which inverts that result. In other words, it’s effectively processing this:

NOT(AND(Name, Description))

if the type is “TypeOne,” and

NOT(Name)

if the type is “TypeTwo”. In short, if we don’t have data in the required field(s) for the chosen type, then NOT() will return True; otherwise it will return False.

Outside of that, we’re using AND() to test if a) there’s actually a selection in the {Type} field, and b) we don’t have all the data we need for that type. That is passed back to the outermost function, IF(), and the warning message will print if both tests in that AND() function are True.

Here’s that formula in action:

Thanks, Justin! It’s sad that Airtable doesn’t yet support variables in formulas but your code is indeed a more optimized way to solve this for now. Thank you!

Sergey_Filimono · Answer

Airtable formulas don’t currently support variables. The formula structure you showed is pretty much the way it’s done based on the example you provided. It can be optimized a bit further, though. Here’s what I came up with:

IF(
    AND(Type, NOT(
        SWITCH(
            Type,
            "TypeOne", AND(Name, Description),
            "TypeTwo", Name
        )
    )), "⚠️ Some fields are not filled out!"
)

Here’s how that breaks down, starting on the inside.

The SWITCH() function is optimized for creating specific output based on what’s found in a single field. In this case, I’m checking the {Type} field. If it contains “TypeOne”, it will return the result of checking the contents of both {Name} and {Description} (if you put just a field name in a test like that, it will return True if the field is non-empty, and false if empty). For the “TypeTwo” option, putting the field reference alone works because of what’s happening next.

Once the SWITCH() function is processed, it goes to the NOT() function, which inverts that result. In other words, it’s effectively processing this:

NOT(AND(Name, Description))

if the type is “TypeOne,” and

NOT(Name)

if the type is “TypeTwo”. In short, if we don’t have data in the required field(s) for the chosen type, then NOT() will return True; otherwise it will return False.

Outside of that, we’re using AND() to test if a) there’s actually a selection in the {Type} field, and b) we don’t have all the data we need for that type. That is passed back to the outermost function, IF(), and the warning message will print if both tests in that AND() function are True.

Here’s that formula in action:

Thanks, Justin! It’s sad that Airtable doesn’t yet support variables in formulas but your code is indeed a more optimized way to solve this for now. Thank you!

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded