Unanswered

How to use Not Startswith in Odata filter

(0) Share

Report

Posted on by Vallamreddy VenuGopal

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

Categories:

General topics

I have the same question (0)

All responses (9)

Answers (0)

RezaDorrani 12,143 on at

Like (0)

Report

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

Was this reply helpful? Yes No
sagirkazi 23 on at

Like (0)

Report

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

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

Was this reply helpful? Yes No
SJNBham 51 on at

Like (0)

Report
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-get-items-and-get-files#filter-queries

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.

Was this reply helpful? Yes No
sagirkazi 23 on at

Like (0)

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

Was this reply helpful? Yes No
SJNBham 51 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Golly 127 on at

Like (0)

Report
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'])))

Was this reply helpful? Yes No
Jesse_Cooper 3 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
mvn 32 on at

Like (0)

Report
will this work?

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

Was this reply helpful? Yes No
Golly 127 on at

Like (0)

Report

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.

Was this reply helpful? Yes No