Help

Combining several Multiple Select fields into a new field

Topic Labels: Automations Formulas
Solved
Jump to Solution
1952 8
cancel
Showing results for 
Search instead for 
Did you mean: 
jaybushman
6 - Interface Innovator
6 - Interface Innovator

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: 

jaybushman_0-1679813054400.png

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. 

 

1 Solution

Accepted Solutions
Karlstens
11 - Venus
11 - Venus

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.

Karlstens_0-1680064950689.png

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

Karlstens_1-1680064990286.png

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.

 

See Solution in Thread

8 Replies 8

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

 

jaybushman_1-1680022736563.png

 

 

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

 

 

 

 

 

Karlstens
11 - Venus
11 - Venus

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.

Karlstens_0-1680064950689.png

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

Karlstens_1-1680064990286.png

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. 

Phoenix_Aart
4 - Data Explorer
4 - Data Explorer

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},"")
Phoenix_Aart_0-1690660674751.png