SUM multiple lookup columns

[Anonymous]

Dear PowerUsers,

I am very new to SharePoint, Power Automate and so on. We want to implement our transport management system in our SharePoint environment. Now I got the following problem:

I have 2 tables

The table "Lieferaufträge" contains the following information which are important for the "Sendung"

Multipe "Lieferaufträge" (delivery orders) are in 1 "Sendung" (shipment). 

"Lieferaufträge" and "Lieferaufträge:Gewicht" (Gewicht = Weight) are both lookup columns. 

I guess there is a way to sum up the weight of a shipment with multiple delivery orders using Power Automate flows, but as I said I am very new.

Thank you very much 🙂 

[Caspar_Rubin · Accepted solution]

Hey, 

I'm sure there are better ways to do this but you could do it like this:

Full Flow:

Explanation:

First you get the items from the List Sendungen.
You could also use the trigger When an item is created or something like that.
For my example, I use a manual trigger and a Get items action.

After you get the items from the Sendung list, add a Initialize variable action, name it Sum, type is Float and Value is 0. This is the "container" in which we will sum all weights. 

Then add an Apply to each action, with the Dynamic content value from the Get items action.

After that add another Apply to each with the Dynamic content Lieferaufträge:Gewicht from the Get items action.

Inside the second Apply to each, add two Compose actions. 

In the first one, simply add the Dynamic content Sum (the variable)

In the second one, add the following Expression: 

float(item()?['Value'])

After that, add an Set variable action, select the Sum and in its value, use the following Expression:

add(outputs('Compose_-_Total_SUM_at_this_moment'),outputs('Compose_-_Weigth_of_this_item'))

!Make sure, you name the 2 Compose actions before the Set variable action exactly the same as mine or edit the Expression above...

Check

Let's check the flow:

I added a Compose action at the End with the Sum inside it to check if it gets added correctly:

My Sendungen List:

Result:

334.2 + 12.5 + 62.34 = 409.04

EDIT:

You could then do something like this:

Add a new column to your Sendung List called Total Gewicht.

And use an Update item action inside the first Apply to each loop to update each row in Sendung with its total weight:

Leave the Lieferaufträge Id and all other fields empty so they don't get overwritten...

Result:

Let us know if this works for you, cheers,

caspar

[Anonymous]

Hey,

Thank you very much - it worked!

Your explanation is perect, even I understand the flow 🙂

cheers

Timon

[Anonymous]

Hi,

one thing that I have realised is that, when I create a new "Sendung" with Lieferaufträge in it, it always adds the old weigth.

cheers

Timon

[Caspar_Rubin]

Hey,

to avoid that, simply add a Set variable action after your Update items action inside the Apply to each loop at the end and reset the variable Sum to 0 for the next item.

cheers