Answered

ODATA filter using And operator

(1) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi all,

I'm trying to build a flow where I get items from a sharepoint list and filter them by two or more columns, but every time I try to use the And operator in the ODATA filter, I get a bad request error. Is this just a broken implementation of filtering or am I actually doing something wrong?

Thanks

Categories:

Building flows

I have the same question (0)

All responses (12)

Answers (1)

Verified answer

v-xida-msft Microsoft Employee on at

Like (5)

Report
Copy link

Link copied!

Hi @IanBrooks,

Could you please share a screenshot of your flow’s configuration?

Do you use the And operator in Filter Query field of “Get items” action?

Further, could you please show a bit more about the error message?

I suppose that you want to filter items based on multiple columns of SharePoint list, I think there is something wrong with your formula. Please take a try to change "And" Operator into "and" operator:

Best regards,

Kris

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (2)

Report
Copy link

Link copied!

Hi Kris,

You've answered my question, the And operator isn't supported at present, which is a real shame as it is restricting the usability of the filter. Hopefully it will come in a future update!

Thanks

Ian

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (2)

Report
Copy link

Link copied!

Kris
Thanks for sharing the link. It is a very valuable info!

@Anonymous
I guess for some reason you have discarded using Filter Array action block, right?
BR

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@efialttesThat's correct, I didn't really have a lot of choice as it simply doesn't fit the bill for our needs at present

Was this reply helpful? Yes No
kbirstein1 130 on at

Like (3)

Report
Copy link

Link copied!

This is supported now. I have the following odata filter which works:

Frequency ne null and ApprovalStatus eq 'Approved' and TaskStatus ne 'Complete' and SelectMethod ne 'By Attribute'"

Was this reply helpful? Yes No
Uvais 10 on at

Like (2)

Report
Copy link

Link copied!

Is it really working now.? I tried below condition and got error message

Company eq 'Test' and Zone eq 'SW'

Is there other way to get a filtered excel row with multiple conditions.

Was this reply helpful? Yes No
rjhale 212 on at

Like (0)

Report
Copy link

Link copied!

Did you ever get this worked out? I'm also trying to filter on multiple "eq" conditions, but I'm getting an error indicating that mutltiple conditions is not supported. I'm using the List Rows Present in Table action within the Excel (Business) connector.

Was this reply helpful? Yes No
X-off 17 on at

Like (2)

Report
Copy link

Link copied!

Thanks @efialttes

Using the filter array action block is a great workaround for the "and" operator not working !

You made my ~~day~~ week !

Christophe

Was this reply helpful? Yes No
akharns 433 on at

Like (3)

Report
Copy link

Link copied!

Did we ever get an answer? It looks to me like "and" is not working in the ODATA query for filter on getting rows from Excel.

ODATA expression: SubmissionMonth eq '@{triggerBody()?['SubmissionMonth']}' and Entity eq '@{triggerBody()?['Vendor']}'
400 Bad Request
Error message: Invalid filter clause: unsupported operation. Only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' is currently supported.

1 people found this reply helpful.

Was this reply helpful? Yes No
tsirweyn_ 2 on at

Like (0)

Report
Copy link

Link copied!

You could maybe try adding a calculated column directly to your sharepoint list: if (and ( ColumnA="criteria1" , ColumnB="criteria2" ), "yes" , "no"))

Was this reply helpful? Yes No