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

Topic Labels: Formulas
Solved
3468 2
cancel
Showing results for
Did you mean:  5 - Automation Enthusiast

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).

1 Solution

Accepted Solutions  18 - Pluto

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: 2 Replies 2  18 - Pluto

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:   5 - Automation Enthusiast

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! 