Help

Re: Comparing two multi-select fields and showing the difference

Solved
Jump to Solution
978 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Kelly_Vorrasi1
6 - Interface Innovator
6 - Interface Innovator

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. 

missing_languauges.jpg

1 Solution

Accepted Solutions
Sho
10 - Mercury
10 - Mercury

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

 

See Solution in Thread

6 Replies 6
Alyssa_Buchthal
7 - App Architect
7 - App Architect

This 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

Sho
10 - Mercury
10 - Mercury

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

 

Kelly_Vorrasi1
6 - Interface Innovator
6 - Interface Innovator

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

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.