Concatenate all your fields nicely and easily with this formula generator!

[Lom_Labs]

When trying to concatenate fields together, we always face the problem of either having too many commas (if some fields are blank), or not enough commas (if we are trying to concatenate a lookup field).  Not to mention the fact that some values show up as "Banana, Orange" while others show up as "Banana,Orange" without the space after the comma!

To solve this problem, I have created a script extension that will help you to generate a formula that will concatenate all your selected fields!

Once you have selected the table that contains the fields you want concatenated, you will be asked to select the fields you want concatenated.  After you have selected all the fields you want, you can click "Selection Complete" and the formula will be generated for you.  You can then copy and paste the formula into a formula field!


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

To use this, add the "Script Extension" extension to your base, and paste in 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}\`\`\``)
}