Splitting Multi-select field into different fields

[Devan_Kreitzer]

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:

[Devan_Kreitzer · Accepted solution]

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!

[Justin_Barrett]

Welcome to the community, @Devan_Kreitzer! :grinning_face_with_big_eyes:

Devan_Kreitzer:

If you have any better suggestions, let me know!

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.

[Devan_Kreitzer]

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?

[Justin_Barrett]

Devan_Kreitzer:

Just off of the head with no actual testing: would the Formula column work with the || operator?

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.

[Devan_Kreitzer]

Great video and extremely helpful solution. Thanks so much!