How to use Not Startswith in Odata filter

[vallamreddyvenu]

Hi,

I am trying to use GetFiles action of sharepoint in microsoft flow. I need to add a filter in the odata fileter like "Not Startswith 'Template'". I am not sure on how to use not startswith in the Odata filter. Can somebody, please help me with thsi. 

Thanks,

Venu

[RezaDorrani]

Hi @vallamreddyvenu 

I dont believe odata query with rest api can do this

You may have to create another field or get all data first and then do the filter in flow 

Regards,

Reza Dorrani

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[sagirkazi]

This works for me in the Flow for "List rows"

Filter rows - "statuscode eq 1 and not startswith(name, '<')"

[SJNBham]

Hi @vallamreddyvenu and @sagirkazi and @RezaDorrani,

We're struggling with this as well. We keep getting the error "Fetching items failed. Possible invalid string in filter query." We haven't been able to find a great Microsoft-published reference that clearly outlines the allowed syntax for OData filter queries in Power Automate. We're actually unsure which API the SharePoint Get Items action even uses:

not(startswith(My_x0020_Text_x0020_Column,'myText')) - doesn't work

not startswith(My_x0020_Text_x0020_Column,'myText') - doesn't work

startswith(My_x0020_Text_x0020_Column,'myText') - works

After a bunch of searching, we finally happened upon the following which seems to indicate OData filtering is limited in the SharePoint Get Items action:

https://learn.microsoft.com/en-us/sharepoint/dev/business-apps/power-automate/guidance/working-with-...

Maybe there's a way to use the Send SharePoint HTTP Request to allow access to more OData options and return select items from a list using the "not startswith" option.

[sagirkazi]

Did you try adding another condition and having startswith as the second condition and not first? Something like

statecode eq 1 and not startswith(My_x0020_Text_x0020_Column,'myText')

For me it was Dataverse List Rows and it worked. Did not try for SharePoint Get Items action. 

[SJNBham]

We tried using the Send SharePoint HTTP Request action with the same results. When trying to use the not(startswith) filtering option it fails. It's not listed as being supported in the online documentation we could find, so I guess we move on and use filter array actions. 

[Golly]

It's not ideal, but I solved this issue by using the Filter Array Action.  For instance:

@And(if(empty(item()?['DisplayName']), false, not(startsWith(item()?['DisplayName'], 'zz'))), not(empty(item()?['Email'])))

[Jesse_Cooper]

I have also found that Not doesn't work with SharePoint OData filter.
I've tried some other options like ((startswith(text, 'searchtext')<>0) but that doesn't work either.
Do you think Microsoft might add not or notstartswith to the SharePoint OData filter options?
It seems really restrictive to the filter to not be able to filter out, and only to be able to filter in data.

[mvn]

will this work?

$filter=not(startswith(fieldName,’text to search’))

[Golly]

It appears you're asking if that string will work as an ODATA filter query in the SharePoint Get Items action.  According to this list of supported operators, it will not.