none
Do Calculation Action to minus values from two separate list.

    Question

  • Hi, 

    Two lists were created to calculate Leave days remaining for an employee requesting leave.

    List: Employee Leave Record

    Fields: Employee, Leave Type, Days, RequestedDays

    An employee has a record for each type of leave for example vacation, casual, maternity etc. for example: 

    Employee: Employee A

    Leave Type: Vacation

    Days: 15 

    Requested Days: 5 

    ---- 

    Employee: Employee A 

    Leave Type: Sick Leave

    Days: 10 

    Requested Days: 2

    ---

    List: Leave Request

    Fields: Department, Employee, Leave Type, Department Manager, Start Date, End Date, Workdays( Start Date - End Date as a calculated column). 

    ----- 

    Scenario: 

    An employee request <g class="gr_ gr_54 gr-alert gr_gramm gr_run_anim Grammar multiReplace" data-gr-id="54" id="54">leave</g> via the Leave Request List, the number of days requested is automatically calculated through a formula inserted in the column to output the results in Workdays column. Each employee has a record on the master list called Employee Leave Record with the columns mentioned above. 

    I created a workflow to calculate the number of days requested against the employee's days using the do calculation action in SharePoint Designer but it's not performing as it should for some reason. 

    Can someone please guide me to ensure the correct fields are being entered for the calculation to be done correctly and update the relevant fields. 

    Here's what I did: 

    Do calculation Function: 

    Data Source: Employee Leave Record 

    Field From Source: Days

    Field: Current Item: Employee

    Value: Current Item - Employee

    Minus

    CurrentItem: Workdays 

    Output to Variable: Cal1 

    Then Set RemainingDays to Variable: Cal 1. 

    ....... 

    Two separate lists so I'm not too sure if I am referencing the list correctly on the do calculation fields when it expands. 

    The aim is to minus the requested days from the bucket of days the employee has remaining based on the selected type of request. 

    Thanks in advance, 

    Regards,

    Thursday, March 16, 2017 2:56 PM

Answers

  • Hi aslr12,

    Does the information answer your question?

    If you think the answer is helpful, please mark it as answer.

    Best regards,

    Linda Zhang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • Marked as answer by aslr12 Monday, May 22, 2017 4:11 PM
    Wednesday, March 22, 2017 9:59 AM
    Moderator

All replies

  • Hi aslr12,

    If we retrieve field values based on a condition in SharePoint Designer workflow, we can only get the first value instead of the last value.

    As a workaround, we can use a InfoPath form to get the last value based on the Employee.

    1. Customize the form with InfoPath using InfoPath for “Employee Leave Record” list.

    2. Create a data connection to “Leave Request” list, and sort data by ID Descending.

    3. Create a hidden field to store the Days value from “Leave Request” list. (In this example, I create a field name “field2”).

    4. Add a rule to “Employee” field and add condition to check if it blank.

    5. “Set a field’s value” action to set “field2” value.

    After doing this, we can get the last value of the “Days” field based on the Employee in the workflow. In the workflow, create a new item in “Employee Leave Record” when a item is created in “Leave Request” list.

    Best regards,

    Linda Zhang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Saturday, March 18, 2017 9:30 AM
    Moderator
  • Hi aslr12,

    Does the information answer your question?

    If you think the answer is helpful, please mark it as answer.

    Best regards,

    Linda Zhang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • Marked as answer by aslr12 Monday, May 22, 2017 4:11 PM
    Wednesday, March 22, 2017 9:59 AM
    Moderator