ODATA Question "contains" or similar?

[jed76]

Hi

I'm pulling an email thread from a group mailbox and want to then update a row in a list IF a particular column (in my case JOB NUMBER) is somewhere within the email subject. So far I have only been able to use EQUALS which works perfectly but the subject of the email must EXACTLY match the column. So, if someone replies to an email and the usual "RE-" is added, the flow doesn't work. This is for a SP list.

Is there a better ODATA formula to use?

Secondly, I am then also updating a row in a Dataverse table and I do seem to have been able to use the "contains" function?

Many thanks. 

[Expiscornovus]

Hi @jed76,

With emails I would suggest to store the Conversation Id value of the email in your list in a separate column. The Conversation Id should be the same for the original e-mail and the reply. That should be easier to match and you can keep using your equals operator for this.

Alternatively, if you want to match on email subject try and use the endswith instead of the equals? That should also work in the "RE-" scenario.

[jed76]

That sounds good. However, my issue is that what if the first email sent is not precise? For example, I have a job number 12345 and the person sending the first email about it puts int the subject "Quote Number: 12345"). I want the match to occur because "12345" is within their subject but there are characters present too. Hope this makes sense!

[Expiscornovus]

Hi @jed76,

If you use the Conversation Id for matching the rename of the subject shouldn't matter, as long as they have replied to the original e-mail.

For the endswith approach that rename would be more difficult. So, I would suggest to try the Conversation Id approach first.

[jed76]

Many thanks but forgive me, I don't think you quite understand. I appreciate what you are saying, but the first email which is sent MUST match the job number already in the list and if it does, it pulls the email body and any attachments to the record matching the job number. 

So, I have a job number 12345AB which is present in a record. I then start an email with the subject line "Here is your quote for job number 12345B". I need the flow to trigger because the job number CONTAINS the number in the record? It seems to work with a dataverse table, but I have another list for adding email attachments to in a SP list. I only seem to be able to use the function EQUALS here so it only pulls the email if there is an exact match. Many thanks!

[Expiscornovus]

Hi @jed76,

You are correct, I misunderstood. My bad 😅

What is the specific format of the job number (always 6 digits and two letters)? Might be able to track it with an RegEx pattern.

[jed76]

Many thanks. I am just trying another method. GET ALL ITEMS and then use a filter array which I can use the CONTAIN function with? It seems you can't use CONTAINS with GET ITEMS from a SP List?

[Expiscornovus]

Hi @jed76,

In that case try the substringof function instead of the contains. 

[jed76]

Doesn’t seem to want to play ball either with a SP list or Dataverse table. I’ve tried contains and substring of and I just get it to work the correct way around. It seems that it only really works when you are testing to see if the email is contained with the column whereas I need the column to be contained within the email (which in my case is a group conversation thread topic, which seems to make it even more difficult!).