cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ctedesco3307
Helper III
Helper III

Update Sharepoint list column based on sum of another sharepoint list

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

ctedesco3307_0-1661617253483.png

 

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

 

ctedesco3307_1-1661617253488.png

 

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 

1 ACCEPTED SOLUTION

Accepted Solutions
David-
Solution Sage
Solution Sage

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.

David_0-1661623222069.png

 

View solution in original post

5 REPLIES 5
David-
Solution Sage
Solution Sage

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.

David_0-1661623222069.png

 

ctedesco3307
Helper III
Helper III

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

ctedesco3307
Helper III
Helper III

@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 

ctedesco3307_0-1661789036411.png

ctedesco3307_1-1661789090220.pngctedesco3307_2-1661789125290.png

 

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
Helper III
Helper III

@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. 🙂 

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (5,773)