Using Single Select Column data to make a Multiple Select Column

[UWC_GSP]

I have several single select columns with "yes/no" selection answers. I want to make a sort of if/than formula to combine the information from those yes/no columns. 

For example, in the photo below, there are columns a, b, c ,d, e. We will call those each "foods" for the purpose of the example. If someone says "Yes" to foods a, d, e but they say No to foods b and c, can I make a column that combines the yes data? I would like a formula field to show that the person has chosen foods a, d, e, just like how a multi select field looks. 

I have thought about using If statements, but I'm not sure if that will allow multiple answers into 1 column. I'm not even 100% sure a formula can give me the information I need. 

Does anyone have any input on this? 

[preshetin]

Hi @UWC_GSP  - you can solve it with a script. I'm attaching it below. What is does is it loops through the records and generates a value for combined cell based on Yes/No values.

Check out the result on running this script in the attached screenshot:

Feel free to use this script (note to update table name and field names)

// Select the table
let table = base.getTable('yes-no answers');

// Query all records
let query = await table.selectRecordsAsync({fields: ['a', 'b', 'c', 'd', 'e', 'answers']});

// Iterate over each record
for (let record of query.records) {
    let yesNoColumns = ['a', 'b', 'c', 'd', 'e'];
    let answers = [];

    // Check each Yes/No column
    yesNoColumns.forEach(column => {
        if (record.getCellValueAsString(column) == "Yes") {
            answers.push(column);
        }
    });

    const answersCell = record.getCellValue('answers');

    // Update the 'answers' field
    await table.updateRecordAsync(record.id, {
        'answers': answers.map(el => ({name: el}))
    });
}

I'll be happy to help if you need further assistance!

[UWC_GSP]

Wow this is incredibly useful. I do have very limited experience with script, so when I initially tried to run this script with the real table info plugged in, it wouldn't run and kept giving me "SyntaxError: Unexpected string" so I have attached another photo with a bit more detail. I can't imaging language is incorrect, as we are both using the airtable scripting app. 

My example above was definitely a good indicator of what I need, but I actually have 15 columns I'm wishing to use this script for and when I attempted it on my own, it would not run. 

The table name is: Applicant Data 

The 15 columns are: AC, CSC, CR, DIL, EA, IS, LPC, MAS, MA, MO, PC, RCN, T, U, WK

Any additional advice would be so appreciated, I'm not a coder but I am trying my best to make the data work for me! 

[preshetin · Accepted solution]

Hey @UWC_GSP  - thanks for the feedback. Working with scripts can be challenging. Even one typo may lead to an error. So be careful when pasting the code.

Can you share the screenshot of that error? The error message usually contains the line number of the script where that error occurred. So better provide the screenshot with that error. 

Make sure to create the field named 'answers'. The type of this field should be Multiple select and it should have all 15 options (see attached image).

I adjusted initial code a bit and also added your table name and columns. Try to paste this:

let table = base.getTable('Applicant Data');

const yesNoColumns = ['AC', 'CSC', 'CR', 'DIL', 'EA', 'IS', 'LPC', 'MAS', 'MA', 'MO', 'PC', 'RCN', 'T', 'U', 'WK'];

const answersColumnName = 'answers';

let query = await table.selectRecordsAsync({fields: [...yesNoColumns, answersColumnName]});

for (let record of query.records) {    
    let answers = [];

    yesNoColumns.forEach(column => {
        if (record.getCellValueAsString(column) == "Yes") {
            answers.push(column);
        }
    });

    await table.updateRecordAsync(record.id, {
        [answersColumnName]: answers.map(el => ({name: el}))
    });
}

[Alexey_Gusev]

Hi,
You can easily combine many fields, like

IF(A='Yes','A ')&
IF(B='Yes','B ')&
IF(C='Yes','C ')&
.....
IF(E='Yes','E')

In example I set spaces as separator. You can wrap whole formula in TRIM() to get rid of possible trailing space in the end when E='No'
Note that result will be usual, non-colored text, or you can use emojii (Win +  .  ) , in formula like
IF( {🍓} = 'Yes', '🍓')   same as in field names. 
If you need result to look exactly like multi-select, you should use script.

[Alexey_Gusev]

By the way, how many records do you have? Looping single update might stop the script after reaching some limit (15-20?) of updates/sec. But it depends on execution speed.

[UWC_GSP]

This worked! Thank you so much!