Applies to: SharePoint Online and On-Premises

Description

In Crow Canyon IT helpdesk application, there is a Time Tracking list where staff can add entries to capture the time spent on a Ticket for different activities done to resolve that Ticket. Total time spent on the Ticket is the sum of time values entered in these Time Tracking items that are related to that Ticket. If Ticket has an estimated time column, then it is possible to calculate and show the actual time spent as a percentage of the estimated time. This can then be used for reporting purposes and identify where estimated time is significantly in variance with actual time based on say staff, category etc.

This article describes how to calculate total time spent based on Time Tracking entries of the Ticket and update this value in a column in the Ticket. And then create a calculated column for total time as the percentage of estimated time.

Summary of Steps

1. List Schema changes
   • Time Tracking list
   • Tickets list
2. Configure Workflow to calculate and put the the value of Total time in Tickets item
3. Create calculated column for percentage

Detail Steps

1. List Schema

Time Tracking list

No changes required as this is part of standard application and has the required columns and the Tickets list has a column to show associated “Time Tracking” items.

Tickets list

Create below columns in Tickets list, note that you can specify different names as required:

• Total Work (Number)
  • Internal Name: TotalWork
  • Display Name: Total Work
• Estimated Hours (Number)
  • Internal Name: EstimatedHours
  • Display Name: Estimated Hours

After creating these columns, add them to NITRO forms. Go to Tickets list -> List Settings -> Crow Canyon NITRO forms -> New Form, Edit Form and Display Form –> Add these two columns to NITRO forms and publish the forms.

2. Configure Workflow to update Total work in Tickets list

Configure a workflow to update the “Total Work” value in Tickets list from the “Time Tracking” list entries.

Configure workflow as shown below on Item create and Item modified events on Time Tracking list.

Conditions:

Duration Hours equal afterchange:[[Any]]

Duration Minutes equal afterchange:[[Any]]

Action 1: Query List

Create Query list action to get all the related Time Tracking entries for the ticket.

Query:

<Where>

<Eq>

<FieldRef Name='TicketID' LookupId='TRUE' />

<Value Type='Lookup'>##TicketID##</Value>

</Eq>

</Where>

Note: In the above query, please use internal name of “Related Ticket ID” column in Time Tracking list in place of “TicketId” (highlighted in red).

Action 2: Update Item

Create an update item action to update the “Total Work” value in the Ticket.

Column Mapping:

Total Work: GetRelatedTimeTrackings##$sum([Total Work|TotalWork])

 

Create a calculated column in Tickets list

This column will have the total time as percentage of the estimated time.

Formula: =IF(ISERROR(Total Work/[Estimated Hours]),"0",(Total Work/[Estimated Hours]))