Changing single select to Multi-select

[Jordan_M]

Hello everyone,

In short, I am trying to use an automation to combine multiple single select linked fields into a single multi select linked record.

I have two bases: projects and team. Depending on the complexity and size of the project, there may be 5-15 resources assigned. We assign these resources through their own single select linked field so that we can utilize the timeline/utilization function. This makes doing things for the entire team complicated - such as creating a distribution list for the team members -  as we have to create a lookup field for each resource's email, another field to combine them, and another field to format it. 

I thought a simple automation could address this issue. It is pretty simple: when one of the 15 single select linked records is updated, it updates the multi select "team" field with all the assigned resources. I am running into two issues: 1)If I don't put a separator, then the multi select merges them all together (Instead of the field having individual links like [Resource One] [Resource Two] [Resource Three} it updates the field to be [Resource OneResource TwoResource Three]. 2) If I add a separator, like a comma, it becomes an issue if there are multiple blank fields. I assume this is because it is trying to update with [Resource One],,,[Resource Four],,,,[Resource Nine] and gives me the error "Field "XXXX" cannot accept the provided value: Could not find matching rows for string."

This seems like such an easy automation, and I am just missing something on the formatting, but I just cannot figure it out and am hoping someone here can help:

[pressGO_design]

Huge chance that I’m not understanding something here, so am asking for clarification …. you have 1 project record with 15 linked fields, each of which is 1 person assigned to that project?

[Lom_Labs · Accepted solution]

Hello, to do this I recommend using a formula field to format your data so that it can be used in your automation.  

I have created an extension that helps you with this: 

The example base from the video above can be found at this link.

You can install the extension into your base by adding a "Script Extension" extension to your base, and using the code below:

let settings = input.config({
    title: `Concatenate fields nicely formula generator`,
    description: `Simply select the table that contains the fields you want concatenated followed by the fields themselves.  This extension will then output a formula for you to use immediately!`,
    items: [
        input.config.table("table", { label: `Table` }),
    ],
});

let { table } = settings;

let buttonOptions = new Array
for (let field of table.fields){
    buttonOptions.push(field.name)
}
buttonOptions.sort()
buttonOptions.push({label: "Selection Complete", variant: 'primary'})

let fieldSelectionComplete = false;
let selectedFields = new Array
while (!fieldSelectionComplete){
    let selectedOption = await input.buttonsAsync("Select the field you want concatenated:", buttonOptions)
    if (selectedOption === "Selection Complete"){
        fieldSelectionComplete = true
    }
    else{
        selectedFields.push(selectedOption)
        buttonOptions.splice(buttonOptions.indexOf(selectedOption), 1)
    }
    output.text(`Currently selected fields: ${selectedFields}`)
}

if(fieldSelectionComplete){
    let formula = new String
    for (let sf of selectedFields){
        formula = formula + `IF({${sf}}, ARRAYJOIN({${sf}}) & ",") &`
    }  
    formula = formula.substring(0,formula.length-1);
    formula = `SUBSTITUTE(${formula}, ", ", ",")`
    formula = `SUBSTITUTE(${formula}, ",", ", ")`
    formula = `REGEX_REPLACE(${formula}, ", *\$", "")`
    output.markdown(`\n\n**Here's your formula!** \n\n\`\`\`${formula}\`\`\``)
}

I hope you find this useful

[Jordan_M]

Thank you for the replies!

To the first question, yes that is how we initially set up our tables ~3 years ago. I am sure there is a better way to structure it, but that is a knot that will take a lot of work to untangle. The main reason we stuck with 15 single select vs 1 multi select was due to leadership wanting the utilization / timeline view and at the time (Or may still be) it had to be a single select field for that feature to work. It also makes it nicer for interfaces not having those resources in a 2nd table.

Thank you very much for the script @Lom_Labs . I actually found a work around for this....its just two formula fields that replaces all the commas and formats the list correctly. The automation watches this formula for changes, and when it changes, it copy/pastes the text into the multi select link field which accomplishes what I had hoped - all the resources are combined into one multi link.

The formula for anyone that needs it in the future. This one combines all the resources and eliminates the double/triple/4+ commas:

SUBSTITUTE(

    SUBSTITUTE(

        SUBSTITUTE(

            ", " & {Resource 1} & ", " &

            {Resource 2} & ", " &

            {Resource 3} & ", " &

            {Resource 4} & ", " &

            {Resource 5} & ", " &

            {Resource 6} & ", " &

            {Resource 7} & ", " &

            {Resource 8} & ", " &

            {Resource 9} & ", " &

            {Resource 10} & ", " &

            {Resource 11} & ", " &

            {Resource 12} & ", " &

            {Resource 13} & ", " &

            {Resource 14} & ", " &

            {Resource 15} & ", ",

             ", , ",

             ", "

        ),

         ", , ",

         ", "

    ),

     ", , ",

     ", "

)

The output from this is almost right, but depending which resource fields are blank, there can be a comma at the beginning (Which is why the first line adds one). This 2nd formula cleans up that:

MID({2nd formula}, 3, LEN({2nd formula}) - 4)

The output from the 2nd formula can be copy/pasted straight into the multi select linked field with a simple automation of "When 2nd formula field changes, update multi select field with 2nd formula output".

Thanks again everyone,
Jordan