CALCULATE TIME DIFFERENCE IN DAYS : HOURS : MINUTES

Another business scenario which is quite popular across a lot of companies is to calculate the time spent on an activity. Suppose say we created a case sometime before and I want to know at any moment the age of case in days hours and minutes. Unfortunately, there is no out of box feature provided by CRM using which this requirement can be achieved. Though we can use calculated fields to find difference in days or hours or minutes, there are some practical limitations. One is the value calculated (difference in hours or minutes) is always ballparked to nearest integer and this does not give us accurate or precise result. Second, we can calculate difference in hours, days, minutes in 3 different calculated fields but we cannot combine all the 3 values in a single field.

The above limitations broaden our thinking to search for some custom implementation to achieve this business requirement. The first option that comes to our mind is a plugin. Yes plugin is the simplest way to meet this business requirement to calculate difference in time and show it in days, hours, minutes. We can even perform complex operations in plugin to achieve much more complexity around this business requirement.

Still plugin is not a right solution to meet our business requirement. The reason is performance issue. For my current business requirement, once a case is created and SLA gets triggered, I am capturing the date time stamp into a custom field. Every time I open the case record, I need to calculate time difference between current date and the custom fields date and show the value in days hours & minutes.  It is inefficient to trigger plugin every time we open a case record as a plugin would occupy some server space to execute such a small calculation.

The best solution to achieve this business requirement is through Java script. We can write a js file and trigger it on form load to perform necessary calculations and populate the result into a text field.

For that I have written a java script and the trigger is form onload. The code is:

function Timedifference() {

    if (Xrm.Page.getAttribute("statecode").getValue() == 0 && Xrm.Page.getAttribute("new_starttime").getValue() != null) //check if status is active and Start Time field contains a value

    {

        var currentDatetime = new Date();

        var pastDatetime = Xrm.Page.getAttribute("new_starttime").getValue();

        calculatetimedifference(currentDatetime, pastDatetime);

    }

    else if (Xrm.Page.getAttribute("statecode").getValue() == 1) //if case status is resolved

    {

        var _id = Xrm.Page.data.entity.getId(); //get the GUID of case record

        var req = new XMLHttpRequest(); //Make WebAPI call to retrieve some data

        req.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v8.1/incidents(" + _id.replace('{','').replace('}','')+")?$select=new_resolveddate,new_starttime", false);

        req.setRequestHeader("OData-MaxVersion", "4.0");

        req.setRequestHeader("OData-Version", "4.0");

        req.setRequestHeader("Accept", "application/json");

        req.setRequestHeader("Content-Type", "application/json; charset=utf-8");

        req.setRequestHeader("Prefer", "odata.include-annotations=\"*\"");

        req.onreadystatechange = function () {

            if (this.readyState === 4) {

                req.onreadystatechange = null;

                if (this.status === 200) {

                    var result = JSON.parse(this.response);

                    var new_resolveddate = result["new_resolveddate"];

                    var new_starttime = result["new_starttime"];

                    calculatetimedifference(new_resolveddate, new_starttime);

                } else {

                    Xrm.Utility.alertDialog(this.statusText);

                }

            }

        };

        req.send();

    }

}

 

function calculatetimedifference(currentDatetime, pastDatetime)

{

    if (currentDatetime != null && pastDatetime != null)

    {

        difference_ms = Date.parse(currentDatetime) - Date.parse(pastDatetime);

        difference_ms = difference_ms / 1000;

        var seconds = Math.floor(difference_ms % 60);

        difference_ms = difference_ms / 60;

        var minutes = Math.floor(difference_ms % 60);

        difference_ms = difference_ms / 60;

        var hours = Math.floor(difference_ms % 24);

        var days = Math.floor(difference_ms / 24);

        var finaloutput = days + " days " + hours + " hours " + minutes + " minutes";

        Xrm.Page. getAttribute("new_timedifference").setValue(finaloutput);

    }

}

BUSINESS REQUIREMENT AND ALGORITHM USED

We have created a custom field called “Start Date” which will get populated with a date time stamp after a case record is created. Another custom field we have created is “Resolved Date” which will get populated when case is resolved. We have one more custom field “Time Difference” which is a single line text field.

Our business requirement is on load of a case record first determine if the case status is active or resolved. If case status is active, calculate the time difference between “Start Date” field and present date in terms of Days, hours, minutes and populate the end result into “Time Difference” text field. 

If the case status is resolved, calculate the time difference between “Start Time” field & “Resolved Date” field.

Inside the TimeDifference function, our java script algorithm is developed, and this function is triggered on case form Onload event. First it will check if the case status is active or resolved. If case is active, it will call another function calculatetimedifference() and send 2 input parameters: Current datetime & “Start Time” . All the major calculations are made inside the function and the result is written to “Time Difference” field.

If the case is resolved, we need to calculate time difference between “Start Time” & “Resolved Date” field. However, we can no longer use Xrm.Page.getAttribute(“”).getValue() to retrieve the attributes from the form because the case is read only. Alternate option is to make WebAPI calls to CRM server and retrieve the 2 field’s. After the 2 fields are retrieved, we again call calculatetimedifference() by sending 2 input parameters. The function will calculate the time difference and populate the result in “Time Difference” field on form. Though the case is read only, we can still use Xrm.Page.getAttribute(“”).setValue() to populate the result in “Time Difference” field. But you cannot save the record because it is read only. All you can do is just populate the calculated result into case form for the end user to see the value on form, but you cannot save the record as it is locked on the database side.