Adding all values that correspond with an employee in one list, then updating a single item with that total in another list corresponding with that same employee

[#1]

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:

PTO Requests

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 :

Available PTO

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.

Thanks!

[mark_viglione · Accepted solution]

Try something like this. 

• Use a query list, filter on status == approved; store the Employee Column in a collection
• Remove the duplicates from the collection - leaving you with a collection representing each unique employee entry - this can be done with the collection operation
• Use a for each to walk through your unique collection storing each individual employee in a tempEmployee variable
• Query the list filtering where status == approved and Employee == tempEmployee - store the hours in a new collection - this will be all approved hours for a specific employee
• use another for each to walk through approved hours collection and sum each entry up - now you have the total approved hours for each employee
• outside the sum hours for each, but still inside the employee for each, update the available pto list with that total

[#3]

Thanks a lot. I will try this tonight and report back on the success.

[gbaker88]

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.

-Greg

[kapilkjoshi]

Hi Greg,

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.

[irebout]

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.

• Query the list filtering where status == approved and Employee == tempEmployee - store the hours in a new collection - this will be all approved hours for a specific employee
• use another for each to walk through approved hours collection and sum each entry up - now you have the total approved hours for each employee
• outside the sum hours for each, but still inside the employee for each, update the available pto list with that total

[mark_viglione]

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.

[irebout]

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

1. Query List B for Fruit & Approved store this in a collection “Fruit”
2. Remove the Duplicates from the Fruit Collection and Store them in new collection “UniqueFruit”
3. Create For Each
   1. Target Collection “UniqueFruit”
   2. Store Result in new variable
      1. Create Variable “TempFruit” Type of Variable is Single Line of Text
   3. Query List B
      1. Filter off of Status = Approved
      2. Filter off of Fruit = TempFruit (Variable)
      3. Field = Quantity
      4. Store in “RunningQuantity” (collection)
4. Create For Each (Inside of first Create For Each)
   1. Target Collection “RunningQuantity”
   2. Create “FinalQuantity” variable (number)
   3. Store Result in “FinalQuantity”
5. Create Math Operation inside second “For Each”
   1. Workflow Date “FinalQuantity” plus List Lookup Current Item “Quantity” Store Result in “FinalQuantity”
6. Outside of the second “For Each” but inside the first “For Each” Update List Item
   1. Update List A
   2. Where Fruit equals Value (of list B) Fruit
   3. Field selection is Quantity equals Workflow Data “FinalQuantity”

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?

[mark_viglione]

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.

1. Create For Each (Inside of first Create For Each)
   1. Target Collection “RunningQuantity”
   2. Create “FinalQuantity” variable (number)
   3. Create "TempQuantity" variable (number)
   4. Store Result in “TempQuantity”
2. Create Math Operation inside second “For Each”
   1. Workflow Date “FinalQuantity” plus “TempQuantity” Store Result in “FinalQuantity”

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

[zafares]

Hi Mark,

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.