Get Items filter query Contains

[skindu]

Hi,

I have a sharepoint list with several columns, one of which is the invoice code.

I pretend that when a new email arrives that in the subject contains the invoice code (at the beginning, at the end or in the middle of the subject) search the sharepoint list and if you find (that is, the number of records found greater than or equal to 1) execute the remaining operations.

For the research I did with substringof function it should work. In the example below it only works if the email subject has exactly the invoice code and nothing else.

[mcastine]

Did you try doing a condition to compare the Subject? I would use that to compare the Subject with the Invoice variable. I do this with incoming mail and updates a column (Category) so the mail is easy to sort. 

[Paulie78]

I think you have misunderstood the function of substringof odata query. My understanding....

Your title column contains the invoice number.

The subject line from your email also contains the invoice number amongst other things.

substringof will return rows that contain all of your search criteria plus anything before or after it.

Imagine...

Title column in SharePoint contains three rows

"Joe Bloggs"

"Jane Bloggs"

"Jimmy Page"

Example Filters

1. substringof('bloggs', Title) will give you back both of the bloggs records
2. substringof('bloggs likes eating chocolate', Title) will give you zero records
3. substringof('J', Title) will return all records.

You are executing your query as per example two. You're searching for more than there is in the column.

Your best approach would be to try and extract the invoice number from the email subject in a more accurate way. Do the invoice numbers follow a particular format?

Does what I am saying make sense?

[skindu]

@mcastine

The condition just below the get items action?

You can place another screenshot a little up to see?

Thanks 

[skindu]

Hi @Paulie78

Now, after your explain I understand substringof. 

I think I really have to use the odata filter because the list has about 2500 records, if I don't do the filter here I may have problems performing, the power automate itself gives a warning message if option is blank. 

The invoice code pattern is always 10 characters and the pattern is: ##111AA111

## - 2 digits or letters
111 - 3 digits
AA - 2 letters
111 - 3 digits

Thanks 

[Paulie78]

This is one of those times you wish Power Automate had regex support!

Is the AA - 2 letters always "AA" 

[skindu]

@Paulie78 
Any suggestions or came up with a different approach? With a list of thousands of records, will making a search cycle go through the records hurt performance and take a long time or not?

PS: sorry my bad english 🙂

[Paulie78]

Yes, I have an idea, but as I asked in previous message. Does the invoice number always contain AA in the middle? If so that could be used as a reference point to capture the invoice number. 

So is AA always present?

##111AA111

[krootz]

@skindu if you provide a sample of what's in the subject of the incoming email and sample data in your Title, I'm sure we can help better

[skindu]

@Paulie78  AA is 2 letter, but are variable

[skindu]

@krootz 

Some examples:

• 16220ZZ001 Overdue Invoice Payment
• 16220ZX001 Bill payment
• T220ZZ001 Bill payment
• RE: PT220ZZ001 Bill payment
• "FWD: 16220ZZ001 Overdue Invoice Payment"
• FR220ZZ059 Payment confirmation
• ES220ZW001 Payment confirmation
• RE: ES220ZW017 Payment confirmation
• RE: RE: ES220ZW001 Payment confirmation
• Bill payment ES220ZX015

[Paulie78 · Accepted solution]

I have thought of a way of doing it. But the one in the list above that does not make it work is T220ZZ001 - is that correct or is it a mistake? it does not match the same format as the others

[skindu]

@Paulie78 
There is no pattern, any of the letters can vary from A to Z and the numbers from 0 to 9

There is no possibility to use wildcard like * Field * ? 😉

[Paulie78]

This is my solution ( https://ibb.co/74LDwFc 😞

Result ( https://ibb.co/Z24PK9d ) :

Explanation:

1. The email subject is split into an array of words by the space character.
2. The array is filtered to find only words that end in a number.
3. The first element of that array is then put into a compose ready for use in an odata filter query.

The code for the filter is:

@or
(
	contains(substring(item(), sub(length(item()), 1), 1), '0'),
	contains(substring(item(), sub(length(item()), 1), 1), '1'),
	contains(substring(item(), sub(length(item()), 1), 1), '2'),
	contains(substring(item(), sub(length(item()), 1), 1), '3'),
	contains(substring(item(), sub(length(item()), 1), 1), '4'),
	contains(substring(item(), sub(length(item()), 1), 1), '5'),
	contains(substring(item(), sub(length(item()), 1), 1), '6'),
	contains(substring(item(), sub(length(item()), 1), 1), '7'),
	contains(substring(item(), sub(length(item()), 1), 1), '8'),
	contains(substring(item(), sub(length(item()), 1), 1), '9')
)

I tested with your samples and it worked every time. So hopefully it should do what you need.

If I answered your question Accept it as a solution ✔️ 

If I helped you out, please give me some Kudos 👍

Thanks 😙

[Paulie78 · Accepted solution]

Noticed that one of the comments on my screenshot is wrong, code in the last step should be:

first(body('Filter_Words'))

[skindu]

@Paulie78 

Amazing... Top Top Top!

I believe this will help many of the people here on this forum

Thanks

[yashm]

Hey @Paulie78 , we meet again! 

I'm trying to have a similar flow as the above. In my case the subject contains invoice numbers as well. However, when I was used the above filter code for the subject an error showed up because the subject of my email was 'Test10 Z1C000542'

So the filter's output was 'Test10' and it didn't take in the invoice code.

The thing in my case is my invoice number is always 9 digits (starts with two alphabets and remaining numbers), for eg. NW1003019, PW2040146 and UU2100016

What should I modify the code to so that it always returns the invoice number and not anything else?

Thanks!

[Paulie78]

Post a new question in the forum and I will answer for you.

[yashm]

Done! 

https://powerusers.microsoft.com/t5/Building-Flows/Email-Subject-Filter-Query-for-Invoice-Code/m-p/8...