Answered

Filter Query with multiple conditions

Like (0) Share

Report

Posted on 1 Mar 2024 22:51:39 by Jukie

I am new to Power Automate, but based on another community post, I successfully created a flow to automatically copy items created or modified in one Sharepoint List to another in real time.

- When an item is created or modified

- Get Items (Filter: SyncedID eq Body/IDx)

- Condition to create the item in the second list if true or update the item in the second list if false

Works great.

If possible, under Get Items, I want to continue to add another condition that filters out any records where BOTH of the following are true: Column A is not "placeholder" and Column B is not null.

The filter would need to return all records where:

- Synced ID eq Body/ID, Column A is "placeholder" and Column B is not null

- Synced ID eq Body/ID, Column A is not "placeholder" and Column B is null

Is that possible under Filter Query or do I need to research Filter Array? Thanks!

All responses (10)

Answers (1)

aamirghanchi 38 on 27 May 2024 at 22:57:41

Like (0)

Report

Re: Filter Query with multiple conditions

Power automate does not support multiple conditions in the Filter array function.
Tristan_D 388 on 29 Mar 2024 at 16:49:09

Like (0)

Report

Re: Filter Query with multiple conditions

Well done Julie (@Jukie )!

Happy to hear that you managed to find a solution.

T
Verified answer

Jukie 82 on 28 Mar 2024 at 14:30:07

Like (1)

Report

Re: Filter Query with multiple conditions

Hi @Tristan_D ,

Thanks again for all your assistance! Work took me away from this project for a bit, but in the meantime, one of my colleagues was able to find an alternate solution before I could get back to you. Instead of using the Filter Array, we ended up with multiple conditional statements. It is a little more complicated, but works like a charm.

When an item is created or modified:
Condition 1: field 2 = Placeholder
If Condition 1 is true, then Get Items (Filter Query: SyncedID = ID)
Condition 2: length(outputs('Get_items')?['body/value']) = 0; if 0 then create, if not then update

If Condition 1 is not true, then look at Condition 3.
Condition 3: field 9 is null
If Condition 3 is true, then Get Items (Filter Query: SyncedID = ID)
Condition 4: length(outputs('Get_items')?['body/value']) = 0; if 0 then create, if not then update

If Condition 3 is not true, do nothing.

Have a great rest of your day!
Julie
Tristan_D 388 on 10 Mar 2024 at 16:00:39

Like (0)

Report

Re: Filter Query with multiple conditions

Hi @Jukie !

I am not sure at 100% what might happen here, could you switch the filter array back to the classic view in order to see if it is an issue generated by the advanced code ?

Have you got one of the flow run that we could check (especially what is returned in the filter array ouput) ?

Thanks.

T
Jukie 82 on 08 Mar 2024 at 16:18:00

Like (0)

Report

Re: Filter Query with multiple conditions

Hi @Tristan_D ,

Your expression looks good to me, but it is still telling me it is invalid. I have the feeling that I must be doing something else wrong, so let me back up. Just as a reference, I am syncing my two SharePoint lists based on these instructions: https://www.youtube.com/watch?v=looyVm_8OKI

CoPilot gave me the following expression which does not return invalid if I have Value in the From field, but it also doesn't work when I test it.
@or(and(not(equals(item()?['field_2'], 'Placeholder')),empty(item()?['field_9'])), and(equals(item()?['field_2'], 'Placeholder'),not(empty(item()?['field_9']))))
(field_2 and field_9 are the actual names of the SharePoint List columns. An issue from when I first set the list up.)

Is there something else that I would need to add under the conditions? Seems like it is skipping the filter array.
Under Update item, ID = outputs('Get_Items')?['body/value'][0]['ID']
Under Create item and Update item, the two fields I am filtering = triggerOutputs()?['body/field_2/Value'] and triggerOutputs()?['body/field_9']

The Syncing works perfectly without the filter array. Do you see anything obvious? Again, thanks so much for your time and help!
Tristan_D 388 on 08 Mar 2024 at 08:11:15

Like (0)

Report
Re: Filter Query with multiple conditions

Hi @Jukie !

No worries 😊 !

Could you try this expression instead ?
@(not(equals(item()?['ColumnA'], 'placeholder')) and (empty(item()?['ColumnB'])) or (equals(item()?['ColumnA'], 'placeholder')) and not(empty(item()?['ColumnB'])))
I think there is an issue with the brackets.

Let me know if it works 😉.

T
Jukie 82 on 07 Mar 2024 at 23:11:37

Like (0)

Report
Re: Filter Query with multiple conditions

Hi @Tristan_D ,

Apologies for the delayed reply. I am clearly doing something wrong!

The parameters above are almost correct, so I adjusted for it. Either of these two conditions must be true for the item to be included in the new Sharepoint List.
Column A is not "placeholder" AND Column B is null
Column A is "placeholder" AND Column B is not null
In the From of the Filter Array, I have tried both (1)outputs('Get_items') and (2)outputs('Get_items')?['body/value']

In the Filter Query (using Advanced Mode), I have copied in:
@(not(equals(item()?['ColumnA'], 'placeholder') and (empty(item()?['ColumnB']))) or (equals(item()?['ColumnA'], 'placeholder') and not(empty(item()?['ColumnB'])))

Unfortunately, it always turns up as an invalid expression. Do you see where I am messing up? Thanks!!!
Jukie 82 on 05 Mar 2024 at 23:39:48

Like (0)

Report
Re: Filter Query with multiple conditions

Hi Tristan,

Apologies for the delayed reply. I am clearly doing something wrong!

The parameters above are almost correct, so I adjusted for it. Either of these two conditions must be true for the item to be included in the new Sharepoint List.
Column A is not "placeholder" AND Column B is null
Column A is "placeholder" AND Column B is not null
In the From of the Filter Array, I have tried both (1)outputs('Get_items') and (2)outputs('Get_items')?['body/value']

In the Filter Query (using Advanced Mode), I have copied in:
@(not(equals(item()?['ColumnA'], 'placeholder') and (empty(item()?['ColumnB']))) or (equals(item()?['ColumnA'], 'placeholder') and not(empty(item()?['ColumnB'])))

Unfortunately, it always turns up as an invalid expression. Do you see where I am messing up? Thanks!!!
Tristan_D 388 on 01 Mar 2024 at 23:21:37

Like (0)

Report
Re: Filter Query with multiple conditions

Hi @Jukie !

You can use the Filter Array action instead actually :
After "Get Items", add a "Filter Array" action.
In the "Filter Array" action, you can use the expression builder and the result should be something like :
@and(not(equals(item()?['ColumnA'], 'placeholder')), not(empty(item()?['ColumnB']))) or (equals(item()?['ColumnA'], 'placeholder') and empty(item()?['ColumnB']))

Then, you can use the output of the "Filter Array" later on in your flow 😉.

Does that work for you ?

T
Tristan_D 388 on 01 Mar 2024 at 23:07:06

Like (0)

Report
Re: Filter Query with multiple conditions

Hi @Jukie !

Sadly, it's not possible to construct complex conditions directly within the Filter Query using logical operators like AND or OR to handle multiple conditions simultaneously.

You can however use the Filter Array action instead.

After the "Get Items" action, add a "Filter Array" action from the Data Operations category.
In the "Filter Array" action, specify the following conditions:
Column A is not "placeholder" AND Column B is not null
OR
Column A is "placeholder" AND Column B is null
it would look like :
@and(not(equals(item()?['ColumnA'], 'placeholder')), not(empty(item()?['ColumnB']))) or (equals(item()?['ColumnA'], 'placeholder') and empty(item()?['ColumnB']))

Finally, you can connect the output of the "Filter Array" action to the subsequent steps in your flow.

Would that work for you ?

T