Unanswered

An array in Filter Query

(0) Share

Report

Posted on by 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?

Categories:

General topics

I have the same question (0)

All responses (8)

Answers (0)

ScottShearer 25,270 Most Valuable Professional on at

Like (3)

Report

@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.

Was this reply helpful? Yes No
Swiftx64 2 on at

Like (0)

Report

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-scope-of-action/m-p/1266735/thread-id/144590

Was this reply helpful? Yes No
lokeshraje 3 on at

Like (0)

Report

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

Was this reply helpful? Yes No
badri123 10 on at

Like (3)

Report

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!

Was this reply helpful? Yes No
VictorIvanidze 13,073 on at

Like (0)

Report

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

Was this reply helpful? Yes No
badri123 10 on at

Like (2)

Report

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

Was this reply helpful? Yes No
CU07080701-0 on at

Like (1)

Report
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

Was this reply helpful? Yes No
GuillaumeBD 23 on at

Like (0)

Report

Hello, I tried your formula but I can't make it work. Here's the info below:

The flow:

The result of join step:

Your formula I changed for my needs in Get Items-Copy:
Error Message:

Any ideas why it's not working?

Thank you

Was this reply helpful? Yes No