Jan 31, 2022 10:16 AM
Hello,
I have a Jotform survey flowing into my base per each response. In the survey, I have multiple “Check all the apply questions” that subsequently flow into multiple-select fields in Airtable. I am trying to connect a data visualization program to Airtable to visualize these responses, but the connect combines the row into a single string (i.e. “Option 1, Option 3”), which makes it difficult to visualize. Is there a way to have a field that puts a 1 if a selection is made, or a zero if not. I am trying to avoid making separate tables with available options, since my base already has a lot of tables (this is a big project).
What I initially have:
What I’m trying to achieve:
Solved! Go to Solution.
Jan 31, 2022 10:52 AM
I figured it out, for anyone wondering this in the future:
Step 1: Make new Formula column.
Step 2: Use
IF(
FIND(
"Option 1", {Answer}
),
1,
0
)
This will output a 1 if the string is found and output a 0 if not.
There may be a more efficient way of doing this, but this works for the time being. If you have any better suggestions, let me know!
Jan 31, 2022 10:52 AM
I figured it out, for anyone wondering this in the future:
Step 1: Make new Formula column.
Step 2: Use
IF(
FIND(
"Option 1", {Answer}
),
1,
0
)
This will output a 1 if the string is found and output a 0 if not.
There may be a more efficient way of doing this, but this works for the time being. If you have any better suggestions, let me know!
Jan 31, 2022 01:30 PM
Welcome to the community, @Devan_Kreitzer! :grinning_face_with_big_eyes:
This can be simplified just a touch by dropping the surrounding IF()
function and just comparing the results of the FIND()
function against 0. The comparison returns True
or False
, and when output directly to a field, those appear as 1 and 0, respectively. In that light, your example could also be written like this:
FIND("Option 1", {Answer}) > 0
If the output of the FIND()
function is any positive number—meaning that the text was found—that formula will output a 1; otherwise it will output a 0.
Feb 02, 2022 10:55 AM
This is why they call you the “Community Leader”, haha!
Thanks so much for this great suggestion, anywhere to cutdown on redundant code is completely necessary! Making this change will also make it easier when calculating the amount of free form “Other” responses.
Just off of the head with no actual testing: would the Formula column work with the || operator? Possibly setting up a Formula that says, “IF(not Option 1 || Option 2 || Option 3,1,0)”. Any ideas?
Feb 02, 2022 12:04 PM
In case anyone else is wondering, ||
is the OR operator in some languages, including JavaScript. In Airtable’s formula system, there are AND()
and OR()
functions. Instead of putting them between items, you pass the pieces to test as arguments to those functions. More about those functions can be seen in this video that I made a while ago:
In your case, though, you want to see if none of those options are contained in the selection. In that context, you can’t just put the option names. You’d need to again use the FIND()
function to see whether or not each exists, like this (also dropping the surrounding IF()
function like before, as the NOT()
function also returns 1 or 0 depending on the result of what’s passed to it):
NOT(
OR(
FIND("Option 1", Answer) > 0,
FIND("Option 2", Answer) > 0,
FIND("Option 3", Answer) > 0,
)
)
If none of those three options exists in the {Answer}
field, the OR()
function will return False
. The wrapping NOT()
function inverts that to True
, which outputs as 1 to the field.
To reiterate, any of Airtable’s functions that return True
or False
will output 1 or 0 respectively when set as the only output from the formula. In that light, it’s rarely necessary to have a formula explicitly output 1 or 0 depending on the result of some condition because the condition itself does that automatically. The same goes for simple comparisons like {Player Height (in.)} > 72
. That also returns either True
or False
, which become 1 or 0 when there’s no wrapping function.
Feb 02, 2022 12:36 PM
Great video and extremely helpful solution. Thanks so much!