cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Loosersic
Helper I
Helper I

Can I update the sum of one SharePoint list in another list conditionally using power automate?

I have 2 lists.

List 1 where people enter their additional hours.

List 2 where I would like to maintain a balance of each person's hours.

This is my dream outcome (if possible) in screenshots (list 2 I have dummied up as I would imagine it)

I don't think Sharepoint will give me an item field with the balance on its own.

List 1 

 

1.png

 

 

 

 

 

 

 

 

 

 

 

 

 

List 2

2.png

 

 

 

 

 

 

 

 

 

 

 

 

 

I currently have Power Automate taking the first list data and entering it into a Spreadsheet, as it supports SumIf commands that give me what I want. Sharepoint doesn't seem to have SumIf.

I have followed one complex Power Automate tutorial but get stuck on one point and am getting lost.

Would love to know if this can be achieved using Sharepoint alone or whether Power Automate needs to be used. 

Or whether I just give up and stick with Excel.Thanks (in anticipation).

Scott

7 REPLIES 7
Hardesh15
Super User
Super User

@Loosersic 

 

Please follow these steps- (you need to figure out how you will apply filter in get items so that for each user you can get sum.

1.jpg2.jpg

Variable sum will have total of your list items.

 

Thanks

Hardesh

 

eliotcole
Super User
Super User

Hi, @Loosersic , I think @Hardesh15 gave a great flow, I thought I'd offer a different solution.

 

You have two lists, I'm assuming that the employees either all have *very* individual names, or (as I assumed) an employee number.

 

So here's the flow ... I'll explain in an edit, just to get it up there.

setup.jpg

 

Above we've set up variables for the relevant data that we need to work with, and ensured that the hours that were input in the SharePoint item are in an integer format with the newHoursVAR int() expression using the trigger "HowManyHours" data.

 

Below we then look at the single item that we filtered the employees list (your separate list that you keep the totals in) for, and in Set totalHourVAR we've taken the current total hours from that, ensured it is an integer, then in Set calculationVAR we added it to the newHoursVAR, then we just update the list items in each list with the new value.

 

smash it home.jpg

 

SharePoint you might be able to do something with a formula in a calculated column (I think I tried it once) but it's simpler to use one of these methods.

 

Thank you for taking the time to reply. I'm looking forward to trying your suggestions.

Regards Scott 

Wow, that looks really complicated but I'm really looking forward to trying your suggestions. 

My staff fill in the form and their 'claims' (display name, email etc...) get recorded in the 'created by' field in Sharepoint list 1. I can't use the same column in list 2 I think so I created one called 'Staff Member'. 

Your work flow looks like it will cater for new staff when they add an entry as well as updating repeat staff items. I'll let you know how it goes. When I have a few hours free. I find it really I interesting as a non-programmer. Once I learn one skill I can apply that to lots of other solutions. 

Regards Scott 

Actually, you'll probably be completely fine, and actually, you made a good point that I'd completely forgotten.

 

It sounds like you're (rightly) using the "Person" field and the "Created By" field. That is perfect, those are unique entities, they're who the staff actually are on your Microsoft system. So ignore all my nonsense about Staff numbers ;-).

 

Look, basically, it seems like you've got your head strapped on right, I reckon once you've got the logic in your head, you'll have it all good on the screen.

 

((( 🤫 I'm not a programmer either!! )))

I wasn't able to make either of the flows suggested work but did find a super simple way of summing Sharepoint columns in this Youtube video. https://www.youtube.com/watch?v=7sCF2pg8q5I

 

Hi Hardesh,

 

can you share the full flow including get items 

 

thanks

Nellai

 

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power automate tips 768x460 v2.png

Restore a Deleted Flow

Did you know that you could restore a deleted flow? Check out this helpful article.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Users online (2,488)