Getting List Items that have three column fields in common, and updating those groups

[Krickner]

Please forgive the mess that is my flow.  I quit in the middle of the build when I realized it was beyond my capabilities. 

I have a SharePoint list that houses customer order details.  It's important to note that I must group records by PONumber, PartNumber, and DueDate, as that accounts for every data circumstance.  Meaning, that our customers may put multiple part numbers on a single PO, but with different due dates.  To segregate the manufacturing steps, I have to group items this way.

What I'm trying to accomplish:
I need to create a flow that fires off every Saturday and gets my list items, groups all items that have matching "PONumber", "PartNumber", and "DueDate".  With those items grouped, I need the flow to check the "StepStatus" field in each grouped item, and if the entire group has a "StepStatus" of "Complete" I need to update the "OrderStatus" to "Closed" on each group item.

I will paste my best shot at this, and it was a long shot.  Any guidance would be very much appreciated.

The append to array variable:
{
"ItemID": @{items('Apply_to_each')?['ID']},
"PONumber": @{variables('CurrentPONumber')},
"PartNumber": @{variables('CurrentPartNumber')},
"DueDate": @{variables('CurrentDueDate')}
}

The append to array variable 2:
{
"ItemID": @{items('Apply_to_each')?['ID']},
"PONumber": @{variables('CurrentPONumber')},
"PartNumber": @{variables('CurrentPartNumber')},
"DueDate": @{variables('CurrentDueDate')},
"IsNewGroup": true
}

Append to array variable 3
{
"PONumber": @{items('Apply_to_each_3')?['PONumber']},
"PartNumber": @{items('Apply_to_each_3')?['PartNumber']},
"DueDate": @{items('Apply_to_each_3')?['DueDate']},
"Items": [
@{items('Apply_to_each_3')}
]
}

[creativeopinion · Accepted solution]

@Krickner You definitely don't need to use Arrays. I think you can accomplish this with a more streamlined flow.

Concepts covered can be reviewed in a few YT Tutorials I have up:

• Are you using the Microsoft Power Automate Filter Action wrong?
• 3 Mistakes YOU 🫵 are Making with the Apply to Each Action in your Microsoft Power Automate Flow
• How to Send a SINGLE EMAIL ✉️ with multiple SharePoint list items

Get Items from List

Depending on how many items you have in your SP List, I would recommend limiting the Top Count to a smaller number than the total number of items in your list. This will increase the speed of your flow runs. Instead of returning all items in your list—it'll limit the Get Items action to the number entered here.

Get PO Number Groups

Because you want to "group" your items by PO number, you'll need to create an array of unique PO numbers. Tip: Remember to rename your actions as you go to keep things organized!

Add a Select action to your flow. Press the icon to switch from key value to map mode.

Insert the PO number dynamic content. For my SP list I'm storing the order number in the Title column.

Run a test. The Select action will return all the order numbers from your SP list. However, there are duplicate values that you will need to remove.

Remove Duplicate Values from Array

To create an array of unique values you'll need to use an expression and the union() function. You can reference this YT Short to see a demo of this.

Add a Compose action to your flow and insert an expression. Use the union() function. The union() function takes two parameters—two arrays you'd like to combine. The neat thing about this function is if there are duplicate items—it'll remove them!

Select the dynamic content tab and insert the output from the Select action above.

Add a comma and insert the same output. This expression will combine the output from the select action and get rid of any duplicates. 

Run a test. Review the output from the Compose action. You should be left with a unique list of order numbers.

Use Scope Actions to Group Actions (optional)

This is optional. I like to use Scope actions to group my actions together. It keeps things organized and also makes it easy to collapse multiple actions with a single click.

Apply to Each Order

Add an Apply to Each action to your flow. You'll use this to loop through each order. Ensure that you are inserting the outputs from the Compose action—not the select action. Remember that the compose action contains unique orders.

Add a Compose action to store the current item being looped through. This is optional—however I find that this can help with troubleshooting.

Get All Items for Order

Next, add a Filter Array action. Use this action to get all the items for the current order.

In the From field, insert the value dynamic content from the Get Items action. In the first value field—insert the PO number dynamic content from the Get Items action (my order number is stored in the Title column). 

Leave the operator as is equal to. In the second value field insert the output from the Compose action above.

Whenever I use a Filter Array action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow.

Insert a Compose action. Add an Expression. Use the length() function.

Select the Dynamic content tab and insert the value dynamic content from the Get Items action into the length() function.

Run a test. Review the outputs. Compare the outputs against your SP list (keep in mind if you've limited the count... the items returned may not be accurate)

Filter the Filtered Array

Add another Filter Array action. In the From field, insert the Body from the Filter Array action above. You are going to filter out items with a status of complete. You'll then compare the length of the second filter array to the first to see if all items should be marked closed.

Refer to this section of a YT Tutorial I uploaded on how to get dynamic content from a Filter Array action. Take a look at the outputs from the first Filter Array action. You'll need to compose an expression to return the dynamic content from a Filter Array action. The dynamic content key is the text in red between the double quotes.

Use the item() expression, add a question mark and square brackets between single quotes.

item()?['']

In between the single quotes, enter in the dynamic content key. Since the Step Status column is a choice field you need to add a forward slash between StepStatus and Value. Note: You'll need to verify the output of YOUR Filter Array action as the key may be different. Please note that the keys are case sensitive.

Add a Compose action to store the count of items from the Filter Array action. Use the length() function as you did previously.

Run a test. Review the outputs. 

This is an example where all items in the order are marked complete as the output from the first Filter Array action matches the output from the second Filter Array action. 

This is an example where the outputs do not match.

Again, this is optional but I've grouped the last four actions into a Scope action.

Condition

Add a Condition action. You'll only want to continue on with the flow if the two Compose action outputs match. Otherwise nothing needs to happen.

This is why it's important to name your actions properly—otherwise you won't know which output belongs to which action.

In the first value field insert the output from one of the two compose actions that are storing the count from the Filter Array actions. In the second value field, insert the other compose action that is storing the count from the other Filter Array action. 

This Condition action will compare the outputs to see if they match.

Place the rest of your actions into the YES branch. If you do want something to happen when the numbers don't match, add those actions to the NO branch.

Apply to Each Order Item

Add an Apply to Each action. You can actually insert either of the body dynamic content from either of the Filter Array action—since the flow will only continue if all items need to be marked as Closed (aka they meet your criteria).

Add a Compose action. This is optional as you can insert the expression directly into the Update Item action. However, I prefer to use Compose actions.

Insert the item() function. 

The dynamic content key for the ID is ID.

Run a test if you'd like to confirm the ID's of the items that will be marked as closed. 

Update Item

Add an Update Item action to your flow. In the ID field, insert the output from the Compose action storing the ID—or insert the expression directly into this field. Select the Closed order status.

Concurrency Control

Since you aren't using any variables in the flow you can turn on the concurrency control which can reduce the time it takes your flow to run. You'll have to turn it on for both Apply to Each actions. This will run multiple instances of the Apply to Each action simultaneously.  

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

[Krickner]

@creativeopinion everything up to the condition is working perfectly.  What's odd is I can see that the All Count is equal to the Complete count, so I'm uncertain why the condition isn't showing any values.  I want to give a sincere thank you for helping me with this.  I've posted the results I'm getting below:

[Krickner]

@creativeopinion , please disregard.  I made an error in the setup of the condition.  It's working like a charm!!! I cannot thank you enough.  Not only have you solved a problem for me, I've learned quite a bit based on the way you process situations such as this.  I wish you the very best.

[Krickner]

@creativeopinion enjoy the coffee. It's the least I can do ; )

[creativeopinion]

@Krickner thanks so much! Appreciate it and I'm glad I could help. More importantly..glad I taught you something! 😄