Update Parent list with total from child list

[Amapola188]

Good Afternoon

I have two SharePoint lists that represent Purchase Orders. Parent list are the Purchase Orders and individual PO lines are in a child list.

I want Power Automate to add up the Total column in the child list and put that value into the parent list. (My ultimate aim is then to implement another flow that manage additional approvals based on that amount.)

I don't know where to go from here (see below). I think I need to get the parent list information but do I use Get Item or Get Items?

It adds an Apply to Each every time I do something. I was going to add Get Item before the variable but it then adds Apply to Each and it's freaking me out.

This is what my flow looks like so far:

[MarvinBangert]

Hey @Amapola188 

could you also please tell us more about your SharePoint Lists? Like are they connected by a Lookup-Field or do you have any columns, that have a unique ID or something else to match List A with List B?

I guess the relationship between child and parent is 1:N, correct? Like 1 parent item (in "Purchase Orders") can have multiple (N) child items (in "PO lines")?

On which List runs your flow (which list is in your trigger)?

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel

[Amapola188]

@MarvinBangert 

Hi Marvin

Yes to all of those 🙂. Multiple PO lines to each Purchase Order. And I pull in the ID of the Purchase Order list so that connects the two lists (Column name PO, see below).

My trigger sits on the child list, because I'm adding the amounts in that list. Trigger is "item created or modified".

(Reality I would only need to run flow if the Amount is updated but there doesn't seem to be a filter in the trigger.)

So I guess the logic is this:

Item gets updated - pull other items with the parent ID - SUM the Amounts and put that amount into the parent item (and from what I see, I will have to pull in parent item data as well).

From what I've tried so far and the error messages I get, I assume that Get Item gives me one value and Get Items gives me an array, otherwise I don't really understand the difference between the two. 

What I don't understand is why it put those Apply to Each for both options. Plus my trigger is a specific item so really should return one item, not an array.

Thank you!

Christine

[MarvinBangert]

Hey @Amapola188 

thanks for your reply, I tested it on my side and I guess this would be the solution you are describing correctly:

- When an item is created or modified runs on your child list

- Initialize Variable: Amount (I used Float, because in my test I also have decimal numbers, you can also use integer, if it works for you.

- Get items from Child list with a Filter Query:

The dynamic content comes from trigger, please make sure you use the internal column name within your child list.

PO eq @{triggerOutputs()?['body/PO/Id']}

This will give you only the items with the same Parent ID.

- Apply to each (should appear automatically, when you enter the dynamic value within the variable action) runs on @{outputs('Get_items')?['body/value']} and runs through every item if your "Get items" output.

 - Increment variable by the amount (dynamic value from "Get items")

- Outside the Apply to each, add an "Get item" (single item) to get the parent item from the parent list. Use the Lookup with the ID from your trigger to get the correct item:

Finally update the parent item with the amount out of the variable and use set the required field using the dynamic value from your "Get item" and/or Trigger:

If you do not keep every item within your child list, you should maybe consider to build a flow, that doesn't get items from the child list, but using "get item" and get the total from the parent list, set the start value of your "Amount" variable as the amount from your trigger and without an "apply to each" increment the value with the "Total" value from your parent list (hope this makes sense, otherwise please let me know, so I can build the flow for you).

Does this help you? Otherwise please give me some more information.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel

[Amapola188]

@MarvinBangert Vielen Dank! (I had a look at your blog. Quite disconcerting reading about Power Apps in German. For some reason, I never worked on German-speaking computers 🙂)

I've tried starting from scratch, following your instructions on this. I can tell from your screen-shot that when you Get Items from the child list, it doesn't wrap that immediately into Apply to Each. Mine does. Really freaking me out. I don't understand ... This is where it's losing and I can't pick up again.

[Amapola188]

@MarvinBangert Thanks for all your help!

I've done a few things and it's working so far:

1. I'm using Expressions rather than the Dynamic content and that got rid of Power Automate wrapping everything into Apply to Each. (Also, I'm pretty much at the start of my list so I've also recreated the linking column, calling it ParentID. There was a lot of PO in my lists so that should make it easier to navigate. Still learning how important column names are in SharePoint!)
2. My Total field in SharePoint is calculated and that must be a string because I got an error message that I can't feed a string into a Float variable. So following this video by Paulie M I've revamped the addition bit of the flow.

Trial and error will get me there eventually! 

[MarvinBangert]

Hey @Amapola188 

does your "PO" field in the child list maybe allows multiple values? Otherwise I couldn't understand why it adds an apply to each. But you should also be able to go on with this, just put everything inside the apply to each. So when you select one or more PO within the child list item, your flow runs through every one of these and updates your parent list:

Also do not forget to clear set your variable to 0 in the beginning.

Does this help you? Otherwise please give me some more information.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel

[Amapola188]

@MarvinBangert Yes, the former PO (now renamed to ParentID) column can have multiple child values.

I've recreated your flow above but get an error "The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@triggerOutputs()?['body/ParentID/Id']' is of type 'Integer'. The result must be a valid array." (Note my Apply to Each is your Apply to Each 2)

I may leave it at that since I have the workflow working ...

Thanks for your help and have a great weekend.

Christine