Hello. I'm struggling to find a way to combine the contents of several Multiple Select field into a new field, while retaining the value and color for each entry. Here's a stripped down version of what I'm trying to do: I've got 4 separate multiple select field, each with their own set of options. The field called "Combined Types (Manual)" shows what I want the output to be. How can I aggregate all that data while retaining the color coding for each one? Thanks a lot.

Combining several Multiple Select fields into a new field

Within a Scripting Extension, or an Automation Script Action (Pro Account) - a script could read the 4 Type column values, and then combine them to the Combined Types field.

With an automation, this would happen upon a change being detected in one of the type columns.

Have you written a script before, or do you have access to automations scripts?

I'm on a Plus account, but I think that does give me access to the scripting extension. I will give that a try. Thanks!

Using Scripting, I've been able to combine several multiple select fields into one as a string. But I can't figure out how to convert that string to another multiple select field

If you can post your script, I'll find a moment to patch it for you.

Thanks a lot! I used the Script Extension from this thread to create the formula:

REGEX_REPLACE(SUBSTITUTE(SUBSTITUTE(IF({Type 1}, ARRAYJOIN({Type 1}) & ",") &IF({Type 2}, ARRAYJOIN({Type 2}) & ",") &IF({Type 3}, ARRAYJOIN({Type 3}) & ",") &IF({Type 4}, ARRAYJOIN({Type 4}) & ",") , ", ", ","), ",", ", "), ", *$", "")

So as you're using a Formula Field to simulate the merged result, you could write a script that parses that Formula into an Object that can then update the Multi-Select field.

In my example, I have these fields "Tag 1" and "Tag 2" that contains animals.

I have a merged formula field, that takes those two fields values and concatenates them;

And within the Scripting App, I use this script to parse that "Merged Formula" column, and paste it into "Tags Merge". Note that my Table is called "Themes" - you'll need to change it to your table name.

let themeTable = base.getTable("Themes");
let themeRecord = await input.recordAsync('Pick a record',themeTable);

if (!themeRecord) {
  console.warn("No theme found.")
  return
}

if (themeRecord) {
    output.text(`Processing Theme Data`);
}

let tags = themeRecord.getCellValue("Merged Formula");
let formattedTags = tags.split(", ").map( element => (
  {
  name : element}
));

let myRecordUpdate = { "Tags Merge" : formattedTags};

console.log(myRecordUpdate)
await themeTable.updateRecordAsync(themeRecord, myRecordUpdate)

Hopefully this helps.

To progress with an understanding of Scripting, make time to read up on JavaScript map(), reduce(), filter() methods - as they're key to understanding what's actually happening here.

So as you're using a Formula Field to simulate the merged result, you could write a script that parses that Formula into an Object that can then update the Multi-Select field.

In my example, I have these fields "Tag 1" and "Tag 2" that contains animals.

I have a merged formula field, that takes those two fields values and concatenates them;

And within the Scripting App, I use this script to parse that "Merged Formula" column, and paste it into "Tags Merge". Note that my Table is called "Themes" - you'll need to change it to your table name.

let themeTable = base.getTable("Themes");
let themeRecord = await input.recordAsync('Pick a record',themeTable);

if (!themeRecord) {
  console.warn("No theme found.")
  return
}

if (themeRecord) {
    output.text(`Processing Theme Data`);
}

let tags = themeRecord.getCellValue("Merged Formula");
let formattedTags = tags.split(", ").map( element => (
  {
  name : element}
));

let myRecordUpdate = { "Tags Merge" : formattedTags};

console.log(myRecordUpdate)
await themeTable.updateRecordAsync(themeRecord, myRecordUpdate)

Hopefully this helps.

To progress with an understanding of Scripting, make time to read up on JavaScript map(), reduce(), filter() methods - as they're key to understanding what's actually happening here.

Thanks a ton. I have been able to get this to work if I manually choose the records. But, as you originally pointed out, combining scripting with automation is only available for Pro accounts and we're only on Plus.

Thank you, Karlstens. Your answer really helped)
Here is an updated formula for a merging cell. Here we added a check for the case when one of the cells to be merged is empty.

IF({Tag 1},{Tag 1},"")& IF(AND({Tag 1},{Tag 2}),", ","")& IF({Tag 2},{Tag 2},"")

Hello @Phoenix_Aart and @Karlstens,

Do you guys think this is possible? Can you suggest me any other possible ways?

Thanks,

Leandro

Hello @Phoenix_Aart and @Karlstens,

Do you guys think this is possible? Can you suggest me any other possible ways?

Thanks,

Leandro

Whilst not understanding your base schema/structure - yes, what you've posted in your screenshot is achievable with either the Script App or an Automation script.

Thanks for your response Karlstens and sorry if I wasn't clear enough.

Basically I need to concatenate the data in the 3 columns in the order and lines as I have in the mockup. Is that possible? I tried modifying your code but didn't work.

Thanks,

L

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded