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

[Loosersic]

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 

List 2

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

[Hardesh15]

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

Variable sum will have total of your list items.

Thanks

Hardesh

[eliotcole]

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.

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.

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.

[Loosersic]

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

Regards Scott 

[Loosersic]

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 

[eliotcole]

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!! )))

[Loosersic]

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

[ferconellai]

Hi Hardesh,

can you share the full flow including get items 

thanks

Nellai