Get Items filter query Contains

(0) Share

Report

Posted on by skindu

121

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.

Categories:

Building flows

I have the same question (0)

All responses (18)

Answers (2)

mcastine 57 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (1)

Report
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
substringof('bloggs', Title) will give you back both of the bloggs records
substringof('bloggs likes eating chocolate', Title) will give you zero records
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?

Was this reply helpful? Yes No
skindu 121 on at

Like (0)

Report

@mcastine

The condition just below the get items action?

You can place another screenshot a little up to see?

Thanks

Was this reply helpful? Yes No
skindu 121 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (1)

Report

This is one of those times you wish Power Automate had regex support!
Is the AA - 2 letters always "AA"

Was this reply helpful? Yes No
skindu 121 on at

Like (0)

Report

@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 🙂

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report

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

Was this reply helpful? Yes No
krootz 458 on at

Like (0)

Report

@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

Was this reply helpful? Yes No
skindu 121 on at

Like (0)

Report

@Paulie78 AA is 2 letter, but are variable

Was this reply helpful? Yes No
skindu 121 on at

Like (0)

Report
@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

Was this reply helpful? Yes No