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!
Power automate does not support multiple conditions in the Filter array function.
Well done Julie (@Jukie )!
Happy to hear that you managed to find a solution.
T
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
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
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!
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
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.
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!!!
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.
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!!!
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
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.
@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
WarrenBelz
146,743
Most Valuable Professional
RandyHayes
76,287
Super User 2024 Season 1
Pstork1
66,079
Most Valuable Professional