Combining Data From Two Multiple-Select Fields Into One
Hello all,
I am stumped. I am trying to combine data from two multiple select fields, into one. For the sake of confidentiality, I have created an example of what I am working with, using part of the Greek alphabet instead of the actual data:
The goal is to combine these two columns into a third that looks and behaves exactly the same as the first two, simply with the data from both combined, into one. As easy as this feels like it should be, every single option I’ve seen either turns the output into text strings (which renders it useless for our tracking purposes), or else it requires a great deal of manually typing or copy-pasting the individual options of the multiple select, into the formula, one by one. It should be noted that while I have only used four Greek letters here, the actual number of entries we are working with is well over 600, and with far longer names, so such a process would be a grueling time-consumer.
Surely there must be some way to automate this?
Also note that there are a couple rows in which both columns already share one of the Greek letters - assuming that there IS a way to automate this, is there also a way to account for/deal with potential duplicates?
The easiest way to do so is to create an automation the simply copy pastes the data from both fields into a new multi select field. The trigger would be when a record is updated and Watch those 2 fields only. This however wont solve your duplicate problem. Also, keep in mind that none of your options can contain commas as it would be considered as 2 values instead of one.
If you want to remove the duplicates then it becomes a litte more complicated, you need to create a Linked Record field and paste both multi select options fields into it (using the same concept automation) then create a Rollup field in the same table with the expression ArrayUnique(Value) , this will remove the duplicates. You then can you another automation to copy paste this new rollup field to another multi select field
Hope this helps
Hello,
Thank you for your reply. Perhaps I am misunderstanding, however, but it seems like you’re suggesting that I manually copy each cell over to create one, for each row. These columns currently have 3,069 rows, and that is only going to increase from here. Manually copying and pasting is the time-consuming nightmare that we are trying to avoid. Is there really no other way to quickly combine the two columns into one?
Or am I simply misunderstanding?
EDIT: After re-reading, I think I understand somewhat better what you’re saying, but I’m not understanding how to go about doing that, apologies - would you be willing/able to detail that a little further?
Hello,
Thank you for your reply. Perhaps I am misunderstanding, however, but it seems like you’re suggesting that I manually copy each cell over to create one, for each row. These columns currently have 3,069 rows, and that is only going to increase from here. Manually copying and pasting is the time-consuming nightmare that we are trying to avoid. Is there really no other way to quickly combine the two columns into one?
Or am I simply misunderstanding?
EDIT: After re-reading, I think I understand somewhat better what you’re saying, but I’m not understanding how to go about doing that, apologies - would you be willing/able to detail that a little further?
I think @Mohamed_Swellam is suggesting that you make use of Airtable’s automation feature to have this process happen automatically.
That is definitely the goal - however, my teammate and I have spent the last week exhaustively looking through the documentation on automation, and other forums. Thus far, every single option we have found, converts the output into a text string, which as I said before, renders it useless to us.
I assure you, we have spent a great deal of time hunting for the specific steps or formula to make this work. We have not yet found it.
My apologies then for restating the obvious.
I’m less familiar with Automation than I am with scripting and extensions. I know it is possible to do what you are trying to do in a script. And it is possible (in Pro or higher subscriptions) to trigger a script from an automation.
I can pull some sample code for how to do it in a script a bit later if that’s helpful to you!
That would be greatly welcome and appreciated, if you do! Thank you in advance!
For what it’s worth, whether it is accomplished through a script, an automation, some other shortcut, or a genuine miracle, ANY solution towards the end goal will be warmly welcomed!
For what it’s worth, whether it is accomplished through a script, an automation, some other shortcut, or a genuine miracle, ANY solution towards the end goal will be warmly welcomed!
Seems like the automation does work…
Create a new field of type Multiple Select. I’ll call it “Sum of Multis”
Trigger: When record updated
Action: Update record
So now, whenever you update either Field1 or Field2, after a moment the automation will run and update the 'Sum of Multis" column to contain all the values.
One bug I haven’t had time to iron out is that when Field1 or Field2 are blank, you get a blank item selected in the “Sum of Multis” column. This can be avoided using conditional actions:
Seems like the automation does work…
Create a new field of type Multiple Select. I’ll call it “Sum of Multis”
Trigger: When record updated
Action: Update record
So now, whenever you update either Field1 or Field2, after a moment the automation will run and update the 'Sum of Multis" column to contain all the values.
One bug I haven’t had time to iron out is that when Field1 or Field2 are blank, you get a blank item selected in the “Sum of Multis” column. This can be avoided using conditional actions:
I should note that in my very brief testing, it seems like Airtable does automatically deduplicate the values:
This popped back into my mind today and I was curious @Lif_Alcon if the automation example I showed you ended up being helpful?
@Nathaniel_Grano Hi, thanks for your solution. I tested on my base and it works fine...for the first time. However, as I updated the trigger volume by adding new choices/option to that multiselect field. All the options in the combined field, including the newly added one got consolidated into a lengthy option. Initially: (Orange)(Apple) Added (Pineapple) to the trigger field After automation: (Orange,Apple,Pineapple) Multiselect field has always been something interesting as I get to know Airtable, however it does not work properly sometimes. If you can help me with this, would be really great. Thanks
@Nathaniel_Grano Hi, thanks for your solution. I tested on my base and it works fine...for the first time. However, as I updated the trigger volume by adding new choices/option to that multiselect field. All the options in the combined field, including the newly added one got consolidated into a lengthy option. Initially: (Orange)(Apple) Added (Pineapple) to the trigger field After automation: (Orange,Apple,Pineapple) Multiselect field has always been something interesting as I get to know Airtable, however it does not work properly sometimes. If you can help me with this, would be really great. Thanks
5) Add "Pineapple" to Field1 choices and edit the record to include "Pineapple" in Field1.
6) Automation produces correct value of "Apple","Banana","Grape","Pineapple"
What were the steps you followed that led to the wrong outcome?
I should note that in my very brief testing, it seems like Airtable does automatically deduplicate the values:
Hi @Nathaniel_Grano . Did what you said and it works fine but I also get a little blu with no info inside like in your picture above on record B after the "Beta". Why does that happen? How can you create an automation to remove those itens?
Hi @Nathaniel_Grano . Did what you said and it works fine but I also get a little blu with no info inside like in your picture above on record B after the "Beta". Why does that happen? How can you create an automation to remove those itens?
What's happening is that the automation merges the values from both trigger columns even if one of those columns is empty. To avoid those empty values, you could change the original automation to use several Conditional Actions instead of a single "Update Record" action. To do this, make the following changes:
Delete the "Update Record" action
Create a Conditional Action with the condition "If [field 1] is empty AND [field 2] is empty" (where field 1 and 2 are the trigger columns) and for the action choose Update Record to update the merged column with an empty value
Create second Conditional Action with the condition "If [field 1] is empty" (where [field 1] is the first of the two trigger columns) and for the action Update Record with only the value from [field 2]
Create a third Conditional Action with the condition "If [field 2] is empty" then Update Record with only the value from [field 1]
Finally, create a 4th Conditional Action with the default "Otherwise" condition, and then Update Record with the both the [field 1] and [field 2] values from the original example.
What's happening is that the automation merges the values from both trigger columns even if one of those columns is empty. To avoid those empty values, you could change the original automation to use several Conditional Actions instead of a single "Update Record" action. To do this, make the following changes:
Delete the "Update Record" action
Create a Conditional Action with the condition "If [field 1] is empty AND [field 2] is empty" (where field 1 and 2 are the trigger columns) and for the action choose Update Record to update the merged column with an empty value
Create second Conditional Action with the condition "If [field 1] is empty" (where [field 1] is the first of the two trigger columns) and for the action Update Record with only the value from [field 2]
Create a third Conditional Action with the condition "If [field 2] is empty" then Update Record with only the value from [field 1]
Finally, create a 4th Conditional Action with the default "Otherwise" condition, and then Update Record with the both the [field 1] and [field 2] values from the original example.
I will try that but I am afraid because I am combining 6 different fields. I am guessing it will be a very long automation to write...rsrsrs. Would you try a different approach? Is there a way to make an automation that removes those itens instead? Thanks for the help
I have the same issue; this would require too many rules. The only remedy right now is occasionally removing the blank option from the multi-select field...
I have the same issue; this would require too many rules. The only remedy right now is occasionally removing the blank option from the multi-select field...
If you are on a higher-tiered plan, you could certainly use a script action to combine the fields, ignoring blank ones.
Here's an alternative approach that doesn't require scripts that seems to work:
Clear out the current values in merged multi-select field
Add two formula fields named "Calculation" and "Trim" (you can do this with a single formula but it gets a little messy)
In the first formula field ("Calculation"): IF({Field 1},{Field 1} & ", ", "" ) & IF({Field 2},{Field 2} & ", ", "" ) & IF({Field 3},{Field 3} & ", ", "") extend this formula for as many fields as you are trying to merge
In the second formula field ("Trim"): IF(LEN({Calculation})>0, LEFT({Calculation},LEN({Calculation})-2), "")
Update/replace your automation to the following: When the Trim field is updated, update the Merge field with the value from the Trim field
Explanation: When multi-select values are copied into a text/formula column, you get a comma-separated list. Our "Calculation" formula combines all of the fields together, adding commas to stitch them together, but skipping fields that have no values. This means calculation will always either be empty or a list of values ending with ", ". The "Trim" formula removes the trailing ", " if Calculation is not empty. Our updated automation simply copies the trimmed, merged string back into the multiselect merge field, which does the deduplication work for us automatically.
Edited to correct the TRIM formula 2023-07-24
If you are on a higher-tiered plan, you could certainly use a script action to combine the fields, ignoring blank ones.
Here's an alternative approach that doesn't require scripts that seems to work:
Clear out the current values in merged multi-select field
Add two formula fields named "Calculation" and "Trim" (you can do this with a single formula but it gets a little messy)
In the first formula field ("Calculation"): IF({Field 1},{Field 1} & ", ", "" ) & IF({Field 2},{Field 2} & ", ", "" ) & IF({Field 3},{Field 3} & ", ", "") extend this formula for as many fields as you are trying to merge
In the second formula field ("Trim"): IF(LEN({Calculation})>0, LEFT({Calculation},LEN({Calculation})-2), "")
Update/replace your automation to the following: When the Trim field is updated, update the Merge field with the value from the Trim field
Explanation: When multi-select values are copied into a text/formula column, you get a comma-separated list. Our "Calculation" formula combines all of the fields together, adding commas to stitch them together, but skipping fields that have no values. This means calculation will always either be empty or a list of values ending with ", ". The "Trim" formula removes the trailing ", " if Calculation is not empty. Our updated automation simply copies the trimmed, merged string back into the multiselect merge field, which does the deduplication work for us automatically.
Edited to correct the TRIM formula 2023-07-24
It worked! This is the final formula I use! Tks a lot for the help! That was a classy move ....rsrsrs
If you are on a higher-tiered plan, you could certainly use a script action to combine the fields, ignoring blank ones.
Here's an alternative approach that doesn't require scripts that seems to work:
Clear out the current values in merged multi-select field
Add two formula fields named "Calculation" and "Trim" (you can do this with a single formula but it gets a little messy)
In the first formula field ("Calculation"): IF({Field 1},{Field 1} & ", ", "" ) & IF({Field 2},{Field 2} & ", ", "" ) & IF({Field 3},{Field 3} & ", ", "") extend this formula for as many fields as you are trying to merge
In the second formula field ("Trim"): IF(LEN({Calculation})>0, LEFT({Calculation},LEN({Calculation})-2), "")
Update/replace your automation to the following: When the Trim field is updated, update the Merge field with the value from the Trim field
Explanation: When multi-select values are copied into a text/formula column, you get a comma-separated list. Our "Calculation" formula combines all of the fields together, adding commas to stitch them together, but skipping fields that have no values. This means calculation will always either be empty or a list of values ending with ", ". The "Trim" formula removes the trailing ", " if Calculation is not empty. Our updated automation simply copies the trimmed, merged string back into the multiselect merge field, which does the deduplication work for us automatically.
and we want the same result to have these urls that are in that cell be divided as a multiple select. Can you advice? We dont use commas to separate them the urls go under each other
and we want the same result to have these urls that are in that cell be divided as a multiple select. Can you advice? We dont use commas to separate them the urls go under each other
Your formula is missing a right parenthesis after the digit 2 (which means that my original formula in this thread is also missing that parenthesis! I'm correcting the formula above)
Seems like the automation does work…
Create a new field of type Multiple Select. I’ll call it “Sum of Multis”
Trigger: When record updated
Action: Update record
So now, whenever you update either Field1 or Field2, after a moment the automation will run and update the 'Sum of Multis" column to contain all the values.
One bug I haven’t had time to iron out is that when Field1 or Field2 are blank, you get a blank item selected in the “Sum of Multis” column. This can be avoided using conditional actions:
Hi Nathaniel,
Thank you for including these detailed steps. I am trying to achieve something similar, but instead of combining two multi-select fields, I am trying to combine two multi-select user fields. What I'm finding is that the automation will display one field or the other field, but it will not combine the two. Any thoughts on why it might be breaking, or maybe it's not possible applying this towards combined User fields?
