SharePoint Lists - Using a list as a lookup to populate field on another lsit

[Rob_CTL]

Hi,

I am trying to covert a SP2013 Workflow to Flow and it's proving a bit of a challenge.  So the concept is that an email is sent to a shared mailbox.  Flow then picks up this email and captures the sender, subject and body and this injected this into a SharePoint list called "Tickets".  Then my trusty SP2013 workflow would then look at the sender field and using "Category lookup" it would lookup the senders email address on another list called "Categories", this list has a number of email addresses and each is associated to a category.  The workflow would match the email address and category associated and populate the category field on the Ticket list.

Ticket list structure:

Categories List structure

The issue I have is that when I try to add a condition, Flow wraps it up in a "Apply to each" control which means it goes through each email address rather than just looking for a match and doing the update.

Flow:

value = body('Get_the_Categories_List_Items')?['value']

Email Ad... = items('Apply_to_each_-_Set_Categories')?['Title']

Outputs = outputs('Set_Email_subject_to_lowercase')

Is there any way to get Flow to simply look for an email address match then get the category?

Thanks in advance

Rob

[SCTdan · Accepted solution]

I'd suggesting using an oData filter in your sharepoint lookup.  

In your 'Get the categories list item' action, click show advanced options.  You'll want to see if the incoming email sender matches the email in the categories list.  That'll be something like EmailAddress eq 'email'  , where email is the value you want to lookup, and EmailAddress is the sharepoint column.  Single quote around your search query.  Make sure that's the proper column name, it may be Email0x200Address or something.  You can see the correct name in the URL if you're in the column settings.  

That should return you only one item, assuming the address is in the list and there aren't duplicates.

After this I like to throw in a condition to handle if it doesn't return anything.  You can use the empty() expression to check if it returned anything or not.  Then, to reference the item, use an array index instead so it doesn't go into a loop.  body('get_the_categories_list_item')[0]?['Category'] will give you the value for Category from the first result.  

Let me know if that makes sense, I can find an example to post.

[chsanche]

Hi @Rob_CTL 

The reason why Flow is wrapping the condition inside an Apply to each is because of the actions that you are using above, if you retrieve a list from SharePoint, then Flow will recognize this and insert an Apply to each. You are using Get Items and this retrieves a list.

Best Regards

--

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions help users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Rob_CTL]

Thanks @SCTdan nice solution.