Combining several Multiple Select fields into a new field

[jaybushman]

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. 

[Karlstens]

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?

[jaybushman]

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!

[jaybushman]

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

[Karlstens]

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

[jaybushman]

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}) & ",") , ", ", ","), ",", ", "), ", *$", "")

[Karlstens · Accepted solution]

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.

[jaybushman]

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. 

[Phoenix_Aart]

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},"")

[LeandroHBayer]

Hello @Phoenix_Aart and @Karlstens,

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

Thanks,

Leandro