Struggling with how to do this. I am making a PTO system that has two lists. One that works as a simple leave request system, and one that is used to keep track of how much PTO each employee has accrued for the year and how much they have still available to take.
Most of it is done. The part I still need to do is create a workflow that sums all of the approved hours that each employee has in the leave request list, then puts that total in the Available PTO list so it can be subtracted from the accrued PTO column to give a value of remaining PTO. The lists look something like this:
Employee Hours Requested Approval Status
John 8 Approved
Chris 8 Rejected
John 40 Approved
John 8 Pending
Chris 16 Approved
Emily 4 Approved
Emily 8 Approved
I need the workflow to determine that John has 48 approved hours, Chris has 16 approved hours, and Emily has 12, then update the other list with these totals so that employees can see this list filtered to themselves on the PTO landing page :
Employee PTO Accrued to Date Approved Leave PTO Hours Remaining
John 115 48 67
Chris 35 16 19
Emily 12 12 0
I've tried some combinations of Query Lists and For each loops but to no avail yet. Any help would be tremendously appreciated.
Solved! Go to Solution.
Try something like this.
Thanks a lot. I will try this tonight and report back on the success.
Hey RMC - did this resolve your issue? I'm running into something similar, but I need to total everything up on the same list rather than a separate list.
What is the reason to total on the same list and how will you save the value? Against every line item?
If you just want to see the total, then group them by name and use Totals to see the sum of the column.
I'm running into an issue with step five of the suggested can someone provide more details about these steps. I for instance don't see where or how to sum each entry up.
To sum up the entries for each individual you need a secondary loop. If you've already gotten your collection of employee names and removed the duplicate values then you will do a for each to walk through that employee collection that doesn't have any duplicate names.
Inside that first for each loop you'll query the list where status == approved and employee == tempEmployee (or whatever you named your output in your for each loop). That will give you another collection of approved hours for a particular employee. This is where you have a secondary loop that walks through that approved hours collection you got back from the second query. Inside of that secondary loop just use a math action to add up the hours in that collection, and when the loop completes you'll have the total for that particular employee and you can update some other list with it, or send a notification or whatever you need to do with the totals.
I have a order status in the system now, so on the form selection it’s using a lookup of fruit types from List A
I have four lines in List B all same fruit quantity is 5300, 2650, 10600, 5300. I am getting a calculation of 15900 as my total in List A is this because it’s pulling out duplicate values? Or not seeing all of the lines? Thoughts?
Looks like its in your step 4/5. Create a new variable called TempQuantity and use it in part c. of step 4. Then in step 5 have your math operation be FinalQuantity = FinalQuantity + TempQuantity.
What's happening to you right now is that each time the for each runs its overwriting your FinalQuantity with the next value in your RunningQuantity collection. So at the end you just get the last 2 added together (10600 + 5300).
Please can you assist me as i'm having an issue with the totals that get generated to update the second list. My solution seems to be continuing to sum up all the values instead of only summing up the hours that belong to each unique employee name. I don't know if this is caused by the fact that i'm using an index (number) variable set to a default value of 0, to iterate through the collection of hours. Please assist asi can't seem to figure out how to rectify this.