OData Filter substringof, sharepoint Title as first parameter

[Anonymous]

Hello,

I have a flow, triggered by an arrival of an email to an inbox, that uses a GetItems from a Sharepoint list where I'd like to filter to only show certain list items. I'd like to filter the Sharepoint list to only show items where the Title is contained in the initial Emails' Subject.

E.g. if the email subject  = 'RE: Access Request', all Sharepoint list items containing 'Access Request' in the Title should be returned.

I'm trying to do it with the following formula in the OData filter, but am getting the following error.

If I flip the values around and have the Subject first and Title second, there is no error, but no values are returned. This makes sense though, as no sharepoint list items have a substring in the Title of "RE: Access Request". 

Does anyone have any suggestions for this error?

[Expiscornovus]

Hi @Anonymous,

Substringof function is a bit of an odd one. While other functions (like startswith) would start with the column first, for the substringof function it is the other way around:

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-rest-requests#odata-query-operators-supported-in-the-sharepoint-rest-service

startswith( {Col to query},'{string to check}' )
substringof( '{string to check}', {Col to query} )

Can you try:

substringof('Re: Access Request', Title)

[Anonymous]

@Expiscornovus 

I've tried it in that order, which doesn't return an error, but is not what I'm looking for. I want to query the Title column for a substring that exists in the Subject. E.g. if the Subject = 'RE: Access Request', return any Title that contains any part of that. I think what you're suggesting is querying the Title column for the entire substirng of 'RE: Access Request'. Does that make sense?

[Expiscornovus]

Hi @Anonymous,

Just to clarify your requirement. You mention that you want to match any part of the subject value.

Let's say the subject of the e-mail is:

RE: Hello World from Power Automate.

In that case you want to retrieve all list items which have 'RE:' or 'Hello' or 'World' or 'from' or 'Power' or 'Automate' in the title of the list item? 

[Anonymous]

Really all list items with a Title that matches everything after RE:. Is that possible?

[Expiscornovus]

Hi @Anonymous,

For me it is still not clear what you mean with match everything. But to get us started I have assumed you are looking for or operator. I have gone for the dynamic OData query builder approach.

Below is an example and a setup.

Let's say an e-mail is received with the subject RE: Hello World from Power Automate. I will try and find every individual word (splitting on the space character).

This example won't be able to handle commas and other type of characters, keep that in mind.

With that subject I expect that only item number 6 won't be found in the example list below.

To achieve this result I will dynamically build an OData Query filter:

substringof('Hello',Title) or substringof('World',Title) or substringof('from',Title) or substringof('Power',Title) or substringof('Automate',Title)

1. First I use an empty (temp) Array variable.

2. In an apply to each loop I check if the subjects starts with RE: and if that is the case I slice the subject value. After that the result of that is split on a space character

split(if(startswith(triggerOutputs()?['body/subject'], 'RE: '), slice(triggerOutputs()?['body/subject'], 4), triggerOutputs()?['body/subject']), ' ')

3. Within the apply to each I append the item with a concat function to our temp array. I use encoded characters to be able to build the substringof('value',Title) string.

concat('substringof%28%27',item(),'%27%2CTitle%29')

4. Within the Filter Query I join the values together with an ' or '. I all words need to be in the title you could change this to ' and '. I use a decodeUriComponent to decode the encoded characters back to something readable.

decodeUriComponent(join(variables('ODataQueryArray'), ' or '))

[Anonymous]

Thank you so much for that explanation, but when I said I need every word I meant the entire phrase. E.g. if the email subject is "RE: "Hello big world!" I want to return all titles with the exact phrase "Hello big world!"

Would correct filter just be something like below?

split(triggerOutputs()?['body/subject'], 'RE: ')

[Expiscornovus]

Ok, that simplifies things a lot.

In that case you can use this directly in the Get Items action filter (and ignore steps 1 to 3 from my previous suggestion approach).

substringof('@{if(startswith(triggerOutputs()?['body/subject'], 'RE: '), slice(triggerOutputs()?['body/subject'], 4), triggerOutputs()?['body/subject'])}',Title)

[Sahara22]

Hello @Expiscornovus , I know this is an old post but awesome job explaining everything!  I have a similar issue that I'm trying filter query by name in a sharepoint list.  basically I have two sharepoint lists and I need to find out If list 1 has the same name as list # 2. But sometimes names are attached to text, like @123 so that's why I need to use substring.  then if the name isn't found that "new" name is then created in list # 3. How I have it written now is : substringof('@{variables('varNameToSearch')}',field_1)   --> VarNametoSearch is : 

substring(items('Apply_to_each')?['field_5'],0,outputs('Compose')) . Thanks for your help!