Answered

Syntax for ODATA filter substringof to not contain certain text

(0) Share

Report

Posted on by WSR2006

I'd like to use an ODATA filter to exclude records that contain certain words in their title.

I can see that 'substringof' can be used to include records that contain certain words. So this will filter records that contain certain text: substringof('text to check for',Title)

What would the syntax be if I want to exclude all records that contain that text?

I've tried a few different things using 'ne' but with no success.

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Verified answer

v-litu-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @WSR2006,

It seems like there is no "not contains" in the ODATA syntax:

https://www.odata.org/documentation/odata-version-2-0/uri-conventions/

As a workaround, you could use the Filter query under the Get items action:

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
WSR2006 13 on at

Like (2)

Report
Copy link

Link copied!

Thanks - yes that works as a workaround. Would be good still if they can add a 'not contains' function to the ODATA syntax though.

Was this reply helpful? Yes No
TheBrittinator 16 on at

Like (0)

Report
Copy link

Link copied!

I had a similar issue and I wanted to share an alternative solution using the ODATA filter query that worked for me.

I use a sharepoint list with Power BI, so my list cannot be empty. My workaround was to leave a 'DO NOT DELETE' list item as a quick fix. That means I needed to adjust my scheduled flow to delete everything EXCEPT that item. In the ODATA Filter Query I typed the following:

Status eq 'New'

For your use case it would be:

ColumnName eq 'Value' (this is case sensitive)

And now my scheduled flow will only age off items that are of the status New. I changed the status of my DONOTDELETE item to 'By design' and now it will not be deleted.

Was this reply helpful? Yes No
papsoc 4 on at

Like (0)

Report
Copy link

Link copied!

That should make the trick:
It will bring all records, excluding the ones having abcdefg in their Fieldname.
?$filter=not substringof('abcdefg', Fieldname)

Was this reply helpful? Yes No