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

Thanks for your help Cliff.