+4Hello. 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.
Best answer by Karlstens
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.
+24Within 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?
+4Within 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!
+4I'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
+24Using 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.
+4If 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}) & ",") , ", ", ","), ",", ", "), ", *$", "")
+24So 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.
+4So 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.
+1Thank 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.
+4Hello @Phoenix_Aart and @Karlstens,
Do you guys think this is possible? Can you suggest me any other possible ways?
Thanks,
Leandro
+24Hello @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.
+4Whilst 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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.