Update Sharepoint list column based on sum of another sharepoint list

[ctedesco3307]

I have a SharePoint list that has projects and project totals PROJTOT

I have another SharePoint list that has roles and hours for those projects PROJDETAIL

When the users add rows to the SharePoint list PROJDETAIL I want to trigger a power automate flow to update the contract total

In PROJTOTS to sum all the Role_Totals for that project in PROJDETAIL

MasterProjID field in PROJDETAIL  is a lookup to the ID in PROJTOT –

I see lot of similar posts on this, and I've tried them but none working. thx in advance 

[David- · Accepted solution]

I am doing something similar to generate a weekly report of purchase requests submitted, which includes the total amount.

1. In your Get items action, use a filter query to filter it by your project id field.
2. Initialize a variable of Float type with whatever name you want to give it.
3. Add an Apply to each action and set the data to the value of your get items.
4. In the apply to each add an Increment variable action with the value from the role total (at least I think that is the field you want to total).

That will generate your total.

[ctedesco3307]

@David-  Hi David - thanks for the reply.  Worked like a charm!!! Much appreciated!! 

[ctedesco3307]

@David- one quick question - I updated the flow to work with a different calculated column in my list, Role Amt. - the column is set to currency data type - same as the Role Total. but when I switch to Role Amt I get this error : I switch back to Role total, which is also set as currency, it works fine. Any workarounds for this? Variable is still set to float.   thx 

[David-]

Unfortunately I have not encountered this problem because I do not use the currency type in SharePoint lists for my solutions. I work for a large multinational, so we deal in various currencies, so everything uses the number type. In my forms such as for purchase requests there is a drop-down to choose the currency type. I suspect you will need use an expression in the Increment variable action to convert the currency value in your SharePoint list to a float value. I looked in the forums and found people asking the same question, but I have not found one with a solution.

[ctedesco3307]

@David- - Thanks for the response. I actually figured out a solution - I did a workaround populating the text column with an update action then running this against the text column. I think the problem was that the column I ended up changing to was a calculated column, more then a currency.  Fun fact .. I also tried to change the datatype in the increment variable to float - which got me past the error - but instead of summing the unique roleAmt column values for each row  that had that ID, it applied the same RowAmt value of the row I was adding and posting that to the totals list. So short story - the error went away but the calculations were wrong. If I tinkered it for a bit more I prob could have gotten it to work.... but  I moved on with the workaround. 🙂