Unable to use NOT EQUAL in Power Automate Get Items ODATA Filter for Sharepoint List

Question

I have tried many iterations of the ODATA filter to retrieve all records EXCEPT for just a couple of values from the status column.  My list is a very simple list where we use a column named 'Status' which is a choice type.  We have 12 values in the choice but only 3 of which I am trying to exclude from the items returned.

This will work fine in the ODATA Filter -> substringof('Complete',Status) and will return all rows that have 'Complete' in the Status column.

However if I use substringof('Complete',Status) eq false it will generate the error below.  If I include anything such as eq ne it will generate the same error.

The query is not valid.

clientRequestId: 

serviceRequestId: 

How can I achieve this without creating a huge filter with all the values that should be included?

Answer 1

Dear Pat,

Thanks for your detailed updates and screenshots.

I do a test again and I'm afraid that you may use long query instead of substringof. Sorry for the inconvenience, substringof may only work with the format substringof('field value', filed name). 

I understand that the query you mentioned here is a good idea. Currently I kindly suggest you submit the feedback on the Power Automate UserVoice platform to let our product developers know your requirements and ideas for product improvements. I hope that the query you mentioned here will be soon supported in Power Automate. If our product developers have some updates on the feedback you submitted, they will post updates there directly.

Thanks for your understanding and have a nice day!

Cliff

Answer 2

Dear Pat,

You need to use ne instead of eq false.  For example, you have the following list with the items below. You want to filter all items excluding pending, column1 and column2 in Status.

You can use the following filter query to see the result.

Status ne 'complete2' and Status ne 'complete1' and Status ne 'pending' 

Best Regards,

Cliff

Answer 3

Dear Pat,

Welcome to share any updates when you have time if you need further help on this issue.

Best Regards,

Cliff

Answer 4

Thanks Cliff.  I had tried that at one point, but I was using the OR clause.  Replaced with AND and works great.  The main reason I was using the substringof function is that we have simular values that I could reduce the complexity of the filter query.  Is there a way to get the substringof to work with multiple fields as I posted?

Answer 5

Dear Pat,

Thanks for letting us know the situation in your environment.

I'll do a test again and update here as soon as possible.

Best Regards,

Cliff