Compare Choice Options from 2 Sharepoint Lists

[fabiofa]

Hello Power Automate Community,

I'm looking for some help with this.

I have 2 Sharepoint lists, both having a multiple-choice field.

My flow triggers when a row in the first Sharepoint list is updated, it should then compare the multiple-choice value for that row with all rows in the second SharePoint list and perform some action when there is a match.

Below are screenshots of the data.

List 1:

List 2:

End result should be:

test 3 of list 2 is empty so does not match anything, nothing happens

test 4 of list 2 matches test 6 since they both contain "Choice 10", should do something (ex. send an e-mail)

What I have right now is the following:

For the updated row I take each choice and append it to an array which I then concatenate into one string. I am now not totally sure how to compare this string with each row of the other SharePoint list. If I were to compare the two, it would be 2 for each loops, one to go through each element in the second list, and another one for each choice.

Is there a simpler/ more optimal way to do this?

[Mister_Shaik · Accepted solution]

Hi @fabiofa ,

That's quite interesting 🙂

So what you're trying to do is, when an item is edited in list A, then the flow should trigger and get items where the multi choice column value in list B has a matching value with multi choice column value of triggered item in list A.

To solve this in a simple way, you can use a filer query on your "get items from list B" action. As the filter expression "eq" works similar to "in" or "contains", it'll fetch the matching items from list B.

Here's how I'd do it, I've created a column called "Country" which is multi choice in two lists "ParentList", "RelatedList". Now I have a trigger on "ParentList",

1. After flow is triggered, using a Select action, write a filter query for each multi choice value and get them as an array. (I'm basically writing filter query for each choice and adding it to array)
   
2. Now we use a join expression to concat all the queries from the "Select" action with a ' or ' and use them inside the "get items" filter field

join(body('Select'), ' or ')​​

Now the items returned are the ones that have a match in multi choice columns between list A and list B, you can use them to update your items or send out an email.

Here are some screenshots from a test I've run,

Hope this helps 🙂

Kind Regards,

Shaik Sha
________________________________________________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[fabiofa]

I was able to make the flow work with a bunch of "for each loops", but your solution is much more elegant and efficient.

I was not using filter queries because I kept getting an error message about column names not existing, but I figured it out. For anyone also having this issue, make sure you check in the list settings what the real name of your column is, as the name you see on the frontend is not assured to be the same.

Thank you, Shaik for the solution!

[gmbeee]

@Mister_Shaik 

This is great. I have a similar problem…

What if I wanted to return only the item that have India and Canada together? So in your example only Test Item A is returned.

Is there a way?

Thanks

[gmbeee]

Anyone got any ideas of how I can achieve the above. Thanks