An array in Filter Query

[kenneth0596]

Hi, I want to filter items using an array variable from another list. The query would be something like this: client eq 'a' or client eq 'b' or client eq 'c' ... where list of clients comes from another sharepoint. Any ideas? 

[ScottShearer]

@kenneth0596 

You can't use an array in your filter query.  I suggest using a string variable.  Loop through the array and append

client eq '[current array value here]'  or   

in each loop.

In the last loop, don't add the or at the end.

Substitute the current value in the array from Dynamic properties for [current array value here].

In my example, please note that there is a space after or in the append to string action.

[Swiftx64]

Hey,

Thank you for sharing this, I am trying to follow along but having an issue.

Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Append_to_string_variable' at line '1 and column '2469' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'Append_to_string_variable' to be referenced by 'repeatItems' or 'items' functions.'.'."

What would cause that error?

https://powerusers.microsoft.com/t5/Building-Flows/Action-Apply-to-each-must-be-a-parent-foreach-sco...

[lokeshraje]

Thanks a lot for your solution. That worked for me.

[badri123]

Yes, you can use an array in your filter query.  you can write a Power FX query
Like - concat('ID eq ',join(split(triggerBody()['text'],','),' or ID eq '))

Result ---

Thanks!

[VictorIvanidze]

Hi @badri123,

you are talking about creating a string, not about creating an array. 

[badri123]

hello @VictorIvanidze,
You can implement both array and string. If you are working with string, you can use the split function. However, if you are not dealing with string, you can use array without the split function.

Please follow below example - 
String
concat('client  eq ',join(split(triggerBody()['text'],','),' or client  eq '))

Array

concat('client  eq ',join(variables('varArray'),' or client  eq '))

Thnkas

[johnnyshield]

I couldn't get this to work as described above.

The format that worked for my requirement - filtering IDs not in an array - is as follows:

(ID ne '289') and (ID ne '176') and (ID ne '192') and ...

i.e. each condition is enclosed in parentheses.

To build this up and because I can never remember how to correctly escape characters, I created compose steps for each weird character:

Then a final compose step brings them together, which is the same as @badri123's method above :

concat(outputs('lbr'),'ID ne ',outputs('quote'), join(variables('varArray'), concat(outputs('quote'),outputs('rbr'),' and ',outputs('lbr'),'ID ne ',outputs('quote'))),outputs('quote'),outputs('rbr'))

The compose output then goes straight into the Odata filter query