Compare Email subject line to SP List Column with delimited text within.

[ChelanThomas]

I have created a flow to search the subject line of an email for Key words/phrases from a SP list when a new email arrives.  I am able to get the flow to work flawlessly as long as the column only contains one word/phrase per row; however, this is not efficient, I would have 100's of rows doing it this way. 

Example of Subject Line:

or  

SP list example

I need to run the Subject line through a flow that will check to see if it contains either of the words in the Must_Contain column and return the value of the Report column.

I have tried every way under the sun, email subject to array as a whole and bu individual words, SP List as a string and an array and so many others.  It seems simple, "if email subject line contains either the phrase before the , or the word after then return the Report name"  but I cannot figure it out.  Like I said, if the row contain only one element it will work. 

Thank you. 

[ol_poweruser]

Hi,

I have a question about your question! 🙂
Where is your condition located? In the initial trigger or another action further down in the flow?

[ChelanThomas]

It is not in the trigger, but almost directly after.  Prior to running through the remainder of the flow I need to know what report it matches to. 

[ol_poweruser]

Hello again!

Back with another question... or two.

• I understand that the screenshot that you displayed is just a sample but could you use the text of your two columns (Report and Acronyms) as the conditions of your OR statement? That may be easier than running a function on the Must_Contain column but I also may still misunderstand.
  
  
• What is your condition statement that works with only one value in the Must_Contain column? When it breaks down are you using a split statement to break your column contents down? Will there ever be more than two items in this column?

[ChelanThomas]

I cannot use the "Acronym column" as not all rows will have an acronym.

I cannot use the "Report Name" column because 80% of the time the subject line will not contain the entire report name. 

There may be more than 2 items but never more than 4 in the Must_Contain column. 

here is a screen shot of my working CONDITION

[ol_poweruser]

Okay, this helps a lot! I think I misunderstood.

Are you using variables or dynamic values for your condition statement when it breaks?

It might be this weekend before I can try it but I wanted to share the logic that I'm thinking of right now in case it was helpful:

Trigger: When a new email arrives

Actions: 

• Get a SharePoint list
• Loop thru the contents of the SharePoint list
  • Split the contents of Must_Contain
  • Loop thru output checking the condition if Email subject contains Must_Contain
  • Get SharePoint row ID if there is a match 
  • Exit both loops
• Match row ID to obtain Report

You mentioned that you already tried arrays.  So I don't want to reinvent the wheel if you've already tried this.  Is the loop to check each of the SharePoint rows where your condition previously broke?

How many rows are in your SharePoint list? I am also thinking that you may be able to use a switch statement to reduce the need for looping depending on the number of options. 

[ol_poweruser]

Here it is in Power Automate:

Here are the details for the condition:

Depending on how large your SharePoint list is, it may be very resource intensive. 
I tested it again a recreation of your SharePoint list with both "Canadian Indicator Framework" and "2nd" in the subject line of my test email.

 I hope this helps!