Combining Multiple Single Select Linked Fields into One Multi Select
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:
Accepted Solutions
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
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
