Is it possible using a formula or automation to compare two multi-select fields and show which items are missing from the 2nd?
I'd like to have a 3rd field that would show just what was missing from the completed list.
Solved
Comparing two multi-select fields and showing the difference
+9Best answer by Sho
If you just want to text, you can do it in the formula field. Multiselect color will be lost.
REGEX_REPLACE({Languages Requested},SUBSTITUTE({Art Complete},", ","[, ]*|")&"[, ]*","")
+13This is very easy to do with a scripting automation. Your trigger would likely be an update to the "Art Complete" field, and you'd loop through the languages requested, checking for each being present in the complete field, and if not passing the final array of those to the "Missing Art" field (or whatever you chose to call it).
Do you have access to scripting steps in automations?
+9This is very easy to do with a scripting automation. Your trigger would likely be an update to the "Art Complete" field, and you'd loop through the languages requested, checking for each being present in the complete field, and if not passing the final array of those to the "Missing Art" field (or whatever you chose to call it).
Do you have access to scripting steps in automations?
That would be great! I do have access to scripting in automations but I don't have experience writing scripts
If you just want to text, you can do it in the formula field. Multiselect color will be lost.
REGEX_REPLACE({Languages Requested},SUBSTITUTE({Art Complete},", ","[, ]*|")&"[, ]*","")
+9If you just want to text, you can do it in the formula field. Multiselect color will be lost.
REGEX_REPLACE({Languages Requested},SUBSTITUTE({Art Complete},", ","[, ]*|")&"[, ]*","")
That did it! Thank you so much! If it needs to be a multi select for other team members I can have an automation update a separate field
+9If you just want to text, you can do it in the formula field. Multiselect color will be lost.
REGEX_REPLACE({Languages Requested},SUBSTITUTE({Art Complete},", ","[, ]*|")&"[, ]*","")
Coming back because this worked so well for this set up, I'm wondering if it's possible to use a similar set up but instead of showing what's missing, can you show what matches? I've been trying to wrap my head around regex but its still beyond my reach.
So if A says "Pie, Cookies, Ice Cream" and B says "Sandwich, Cookies, Salad, Ice Cream" then C would show "Cookies, Ice Cream"
Coming back because this worked so well for this set up, I'm wondering if it's possible to use a similar set up but instead of showing what's missing, can you show what matches? I've been trying to wrap my head around regex but its still beyond my reach.
So if A says "Pie, Cookies, Ice Cream" and B says "Sandwich, Cookies, Salad, Ice Cream" then C would show "Cookies, Ice Cream"
That's hard to do with a formula field.
You could concatenate those two lists and detect if they were duplicate values, but you'd have a formula like this
REGEX_REPLACE(
"@>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
values&",",
REGEX_EXTRACT(values,"([^,]*,?$?){1}"),"<@"& REGEX_EXTRACT(values,"([^,]*,?$?){1}") & "@>",2),
REGEX_EXTRACT(values,"([^,]*,?$?){2}"),"<@"& REGEX_EXTRACT(values,"([^,]*,?$?){2}") & "@>",2),
REGEX_EXTRACT(values,"([^,]*,?$?){3}"),"<@"& REGEX_EXTRACT(values,"([^,]*,?$?){3}") & "@>",2),
REGEX_EXTRACT(values,"([^,]*,?$?){4}"),"<@"& REGEX_EXTRACT(values,"([^,]*,?$?){4}") & "@>",2),
REGEX_EXTRACT(values,"([^,]*,?$?){5}"),"<@"& REGEX_EXTRACT(values,"([^,]*,?$?){5}") & "@>",2)&"<@",
"@>[^<>@]*<@","")This formula can detect duplicates up to 5 values.
It can be increased by making the formula longer.
+1I'd love to see a formula that compares a Linked record field to a lookup field, and showing what's missing from either!
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.