Hi, @evalindag
I've used the following method to get this done, running at midnight every night on a schedule.
This flow has 'parallel branches', but that's not a requirement, I just do it for neatness and to save time on longer flow runs.
Filter Items, Select Unique Responsible People, Filter By Person
First up, here's the flow:
I'll continue to edit in my explanation of this into this answer (so if you need more, please wait) ... I just have to not allow the login to expire whilst I'm writing the answer! 😅
Get items
This approach requires that your Get items action on the list has a Filter set:
To set that Filter follow these steps:
- Set the left side to 'Status' using a 'Custom value' if needs be.
- Set the condition to 'Equals' to ensure it matches exactly the right side.
- In the the right side type "New" to ensure that it will only match that.
Don't type the quotes when you enter these values.
To double check this, you can tap the double arrows you'll see that it reads like this:
If not, make sure that it does.
Initialize responsiblePersonVAR
This is a String variable which is optional, I used it so that the flow is more understandable when looked at.
You can remove it, and either replace the Set responsiblePersonVAR step with a Compose action or just call items('Apply_to_each') when ever you need the value.
Select rp
This is the magic bit, and uses a little bit of special sauce to get *only* the field that you want to check for matches.
 |
| From |
xpath(xml(json(concat('{ "r": { "i": ', string(outputs('Get_items')?['body/value']), ' } }'))), '//ResponsiblePeople_x0023_Claims')
|
| Map |
xpath(item(), 'string(/)')
|
|
Those From & Map expressions are fanned out and explained in the below spoiler:
Spoiler (Highlight to read)
This hopefully allows you to see the logic of the expression a little more:
| From |
xpath(
xml(
json(
concat(
'{ "r": { "i": ',
string(
outputs('Get_items')?['body/value']
),
' } }'
)
)
),
'//ResponsiblePeople_x0023_Claims'
)
|
| Map |
xpath(
item(),
'string(/)'
)
|
|
From the inside-out, you may be able to see:
- string() - This converts the Get items action into a string of text.
- concat() - This adds each of the three items detailed inside it into one long string of text.
You'll note that the first and last items here are essentially creating a JSON object with the Get items value in the middle.
- json() - This converts the string of text into usable JSON which can be worked with.
- xml() - This converts the internal JSON into usable XML data.
- xpath() - This is selects only items in the 'ResponsiblePeople_x0023_Claims' field of the XML data.
The xpath() function is an extremely powerful manipulation one, which executes commands on XML data that Power Automate flow actions or expression functions otherwise couldn't do.
This hopefully allows you to see the logic of the expression a little more:
From
xpath(
xml(
json(
concat(
'{ "r": { "i": ',
string(
outputs('Get_items')?['body/value']
),
' } }'
)
)
),
'//ResponsiblePeople_x0023_Claims'
)
Map
xpath(
item(),
'string(/)'
)
From the inside-out, you may be able to see:
string() - This converts the Get items action into a string of text.
concat() - This adds each of the three items detailed inside it into one long string of text.You'll note that the first and last items here are essentially creating a JSON object with the Get items value in the middle.
json() - This converts the string of text into usable JSON which can be worked with.
xml() - This converts the internal JSON into usable XML data.
xpath() - This is selects only items in the 'ResponsiblePeople_x0023_Claims' field of the XML data.
The xpath() function is an extremely powerful manipulation one, which executes commands on XML data that Power Automate flow actions or expression functions otherwise couldn't do.
So now you have a list of probably repeated values ... what next?
Initialize onlyUniquesArrVAR
This is an Array variable which runs a union() function inside its input:
 |
union(
body('Select_rp'),
body('Select_rp')
)
|
That union() function is basically comparing the array of Responsible People items to iteself, and where there are repeats, it removes them.
Condition to cancel if no-one's assigned
This is an entirely optional step, so I won't detail it at all. Essentially it is just a condition against the amount of items inside the onlyUniquesArrVAR variable. If there are zero, it will run a Terminate action which will halt the flow.
To count the items inside the array, just use the length() expression in this spoiler:
Spoiler (Highlight to read)
length(
variables('onlyUniquesArrVAR')
)
|
length(
variables('onlyUniquesArrVAR')
)
Apply to each
This essentially loops through each of the unique Responsible People found in the onlyUniquesArrVAR array:
Here you can see where I have set the responsiblePersonVAR variable simply with the current Responsible person.
After that, there is a slightly complicated Filter array, only in that you can't actually select the 'ResponsiblePeople#Claims' column from the available options. 😅 So just paste in the below text in that left box and select the responsiblePersonVAR variable on the right with 'contains' in the middle.
This will then check each item in the original Get items action. Each one has a mini-array that contains everyone that is a Responsible person for that item. The contains part in the middle checks each of them for the value that you have defined in right hand side box!
That last part is just my processing logic, you will likely have an email there, or something.
If you wish to do more with the responsible person, then you just have to use this expression to get their email address:
last(
split(
variables('responsiblePersonVAR'),
'|'
)
)
|
As an example, I used that inside a Get user profile (V2) action to then have much more usable data on the user, like their name, email adress, and more:
This is good because it also works with any external/guest users that you may have.
