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,

Sorry for the delayed reply. 

Based on my test, the substringof query is used to filter the value from a specific column and there may be error messages when using with ne, eq and so on.

If you need to filter some specific column values, you should use the ne query with And. So about " Is there a way to get the substringof to work with multiple fields as I posted?", could you upload some screenshots about your list and give some examples about your requirement so that we can see whether you can use some other simple queries instead of substringof?

Note: Remove any private information before uploading the screenshots.

Best Regards,

Cliff

Answer 3

Thanks for all your help.  I don't have the query with the substringof to give you a screenshot as I'm using it in production now with the column eq value query filter.  Below is a list of the different status values we use currently.  As you can see some have simular words that are meant to group them where others are grouped with dissimilar words.  Each group is broken out into different HTML tables on the report that is generated.   Using the substringof seems a bit more easy and flexable if the values changed rather than the specific column eq value  statement.

Not Started  (Group 1)

Data Collection  (Group 1)

Business Analysis  (Group 1)

BRM PG Review  (Group 1)

Pending Review  (Group 2)

Pending Leadership Approval  (Group 2)

Project - Pending Start  (Group 3)

Project - Active  (Group 3)

Project - Complete  (Group 3)

Rejected  (Group 4)

PG Withdrawn  (Group 4)

PSDC Withdrawn  (Group 4)

Again thanks for your help.

Answer 4

Dear Pat,

Thanks for your updates.

It seems that you are using the Group feature to filter the column values and use the list view to create the HTML table.  Could you mind uploading some screenshots showing the SharePoint list for our confirmation? If it is convenient, I'd also like you to post a screenshot of your current flow. You can remove some private information to protect your privacy. I'll investigate this further based on your screenshots.

Thanks for your effort.

Cliff

Answer 5

For the Sharepoint list, what info are you looking for?  Will a screenshot of the list settings page work?  If not please elaborate on what you are looking for. The flow is quite large and prior to this is a bunch of variables and a process to update the Sharepoint items to set a value prior to running this here.

This is one of the Get Items in the picture above.