Filter using ODATA query : column does NOT contains

[atreyu]

Hello everyone !

I am tugging on my hair so bad rn.
I have turned the Internet upside down and nothing came out of it.

Can we filter a "Get items" action from SharePoint connector to " Column does NOT contain String" ?

What I have :

I have a list  called "Choices_Workload" of several choices for answers, which can be changed anytime.

I will always have 1 "generic" answer in this list set to Title = "🚫 - No Answer". This item can change its ID, so it would be preferable to filter by the title and not the ID. It might change (emoji or no emoji etc) but the "No Answer" will remain.

I want to get all the items from this list, Except for the "🚫 - No Answer". 

I have achieved to filter my list with 

substringof('No Answer',Title)

This only gets me the generic Answer which is "No Answer", which is the standard and right behavior, so ok.

Now, I want to do just the contrary of this : I want to get every other items EXCEPT for the "No Answer" item.

I tried to filter by:

• not(substringof('No Answer',Title))
• substringof('No Answer',Title) eq false
• substringof('No Answer',Title) neq true

But nothing works.

I would love to avoir filtering the items after the "Get items" action, as it is poor optimization.
But if you tell me it is the only solution, then it is what it is.

Thanks a lot for your insight !

[creativeopinion]

@atreyu I believe you will have to use a Filter Array action to filter out those items.

Return Count

Whenever I use a Filter Array action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow.

Insert a Compose action. Add an Expression. Use the length() function.

Select the Dynamic content tab and insert the value dynamic content from the Get Items action into the length() function.

Run a test.

To learn more about how to use the Filter Array action, please refer to this recent YT Tutorial I uploaded.

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

[atreyu]

Hi @creativeopinion !
Thanks a lot for your time, I already know how to filter the datas using the Filter Array action.

Here, it is not what I am trying to achieve. I want to already filter my results in the Get List Items action. It would allow me to optimize my flow and my data, and to get rid of all the warnings on the data returned.
If you have any idea on how to write the ODATA query to get data where the title does not contain "No Answer", that would be a blast !

[creativeopinion]

@atreyu Unfortunately you cannot use an OData Filter Query to filter items that do not contain a string of text. Which is why you need to use a Filter Array action.  

[mvn]

not contains doesn't work with odata filter query but can use 'not' like this:

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