Remove From My Forums

Count the number of completed appointments a contact has had in CRM Online 2013

Question

Hi Guys,

I have been asked to display the following fields on the Contact Form:

Number of completed appointments, this year
Number of completed appointments, last year
Actual End date of the contacts last completed activity

I have also been asked to display the actual end date of the last completed appointment for the Account form. I handled that using JavaScript and OData to query the Appointment Set in the Account Forms OnLoad event. The following code snippet shows how I achieved this:

function HarrionAB_AccountForm_OnLoad() {

    var accountId = Xrm.Page.data.entity.getId().replace("{", "").replace("}", "");
    if (accountId != "") {
        RetrieveRecords(accountId);
    }
}

function RetrieveRecords(id) {

    // create the odata query
    var query = "/AppointmentSet?$select=ActualEnd&$top=1&$orderby=ActualEnd desc&$filter=RegardingObjectId/Id eq guid'" + id + "' and StateCode/Value eq 1 and ActivityTypeCode eq 'appointment'";
    ExecuteQuery(query);
}

//
// ExecuteQuery executes the specified OData Query asyncronously
//
// NOTE: Requires JSON and jQuery libraries. Review this Microsoft MSDN article before 
//       using this script http://msdn.microsoft.com/en-us/library/gg328025.aspx
//
function ExecuteQuery(ODataQuery) {

    var serverUrl = Xrm.Page.context.getServerUrl();

    // Adjust URL for differences between on premise and online 
    if (serverUrl.match(/\/$/)) {
        serverUrl = serverUrl.substring(0, serverUrl.length - 1);
    }

    var ODataURL = serverUrl + "/XRMServices/2011/OrganizationData.svc" + ODataQuery;

    $.ajax({
        type: "GET",
        contentType: "application/json; charset=utf-8",
        datatype: "json",
        url: ODataURL,
        beforeSend: function (XMLHttpRequest) {
            XMLHttpRequest.setRequestHeader("Accept", "application/json");
        },
        success: function (data, textStatus, XmlHttpRequest) {
            //
            // Handle result from successful execution
            //
            // e.g. if data.d.results has some records returned
            debugger;
            if (data.d.results.length > 0) {
                Xrm.Page.getAttribute("new_lastvisit").setValue(new Date(parseInt(data.d.results[0].ActualEnd.substr(6))));


                var lookup = new Array();
                lookup[0] = new Object();
                lookup[0].id = data.d.results[0].ActivityId;
                lookup[0].name =data.d.results[0].Subject;
                lookup[0].entityType = "Appointment";
                Xrm.Page.getAttribute("new_lastvisitappointment").setValue(lookup);
                Xrm.Page.getAttribute("new_lastvisitlink").setValue([{ id: recorid, name: recordname, entityType: entityname}]);

            }
        },
        error: function (XmlHttpRequest, textStatus, errorObject) {
            //
            // Handle result from unsuccessful execution
            //
            alert("OData Execution Error Occurred");
        }
    });
}

//
// Error Handler
//
function ErrorHandler(XMLHttpRequest, textStatus, errorObject)
{ alert("Error Occurred : " + textStatus + ": " + JSON.parse(XMLHttpRequest.responseText).error.message.value); }

Now I have tried to do the same thing with the Contacts but found that RegardingObjectID does not link to Contacts only Accounts. So now I have been looking at trying to expand the required attendees and optional attendees and the Organiser fields. I am struggling and any input to steer me in the right direction would be appreciated.

Monday, November 18, 2013 3:41 PM

Answers

Ok so I have been working on this issue for a few days now.

I found that it was possible to complete this task with the help of the following tool XRM Dynamics Tools to help generate my OData query code.

Essentially the problem I had was understanding how contacts were linked to the Appointment. Once I understood that all participants for an appointment would be contained in the "appointment_activity_parties" field of an Appointment I was able to see how to create a suitable query to handle this problem.

By choosing the activityid, actualEnd fields and using the expand functionality on the appointment_activity_parties, selecting the PartyId from this expanded field enabled me to check that the party type was a contact, that the contactId of the Party matched with the current contact we were viewing. This way I could count the total number of matches and record the most recent date of a completed appointment for that contact.

In the end I also broke the problem down into 2 queries. One for each year: current and previous. I added three new fields to the Contact form. Two that hold the integers for VisitsLastYear and VisitsThisYear and a lookup to hold a link to the Appointment as can be seen in the following screenshot:

My code follows:

// Project:     Harrion AB Customisation
// Form:        Contact Form
// Created:     31/10/2013
// Author:      Iain Blackwood
// Modified:    20/11/2013
// Java Script Library to handle form and field validation
/// <reference path="XrmPageTemplate.js" />
/// <reference path="JQuery.js" />
/// <reference path="SDK.REST.js" />
/// <reference path="json2.js" />
function HarrionAB_ContactForm_OnLoad() {

    // get the contact id from the page
    var contactId = Xrm.Page.data.entity.getId().replace("{", "").replace("}", "")
    // if we got a value
    if (contactId != "") {
        var currentYear = new Date().getFullYear();
        var query = "/AppointmentSet?";                                                                         // Appointments table
            query += "$select=ActualEnd,ActivityId,appointment_activity_parties/PartyId";                       // Select
            query += "&$expand=appointment_activity_parties";                                                   // Expand sub tables
            query += "&$filter=ActivityTypeCode eq 'appointment' and StateCode/Value eq 1 and ";                // Where

        CountVisitsThisYear(query, currentYear);
        CountVisitsLastYear(query, currentYear - 1);
    }
}

function CountVisitsThisYear(query, currentYear) {
    
    var start = currentYear.toString() + "-01-01T00:00:00";
    var end = currentYear.toString() + "-12-31T00:00:00";

    query += "ActualStart ge datetime'" + start + "' or ActualStart le datetime'" + end + "' and ";     // Where  
    query += "ActualEnd ge datetime'" + start + "' or ActualEnd le datetime'" + end + "'";              // Where

    // call function to execute the odata query
    ExecuteVisitsThisYearQuery(query);
}

function CountVisitsLastYear(query, lastYear) {

    var start = lastYear.toString() + "-01-01T00:00:00";
    var end = lastYear.toString() + "-12-31T00:00:00";
    query += "ActualStart ge datetime'" + start + "' or ActualStart le datetime'" + end + "' and ";     // Where  
    query += "ActualEnd ge datetime'" + start + "' or ActualEnd le datetime'" + end + "'";              // Where

    // call function to execute the odata query
    ExecuteVisitsLastYearQuery(query);
}

//
// ExecuteQuery executes the specified OData Query asyncronously
//
// NOTE: Requires JSON and jQuery libraries. Review this Microsoft MSDN article before 
//       using this script http://msdn.microsoft.com/en-us/library/gg328025.aspx
//
function ExecuteVisitsThisYearQuery(ODataQuery) {

    // get the server url
    var serverUrl = Xrm.Page.context.getServerUrl();

    // Adjust URL for differences between on premise and online 
    if (serverUrl.match(/\/$/)) {
        serverUrl = serverUrl.substring(0, serverUrl.length - 1);
    }

    var ODataURL = serverUrl + "/XRMServices/2011/OrganizationData.svc" + ODataQuery;

    $.ajax({
        type: "GET",
        contentType: "application/json; charset=utf-8",
        datatype: "json",
        url: ODataURL,
        beforeSend: function (XMLHttpRequest) {
            XMLHttpRequest.setRequestHeader("Accept", "application/json");
        },
        success: function (data, textStatus, XmlHttpRequest) {
            //
            // Handle result from successful execution
            //
            // e.g. data.d.results
            var contactId = Xrm.Page.data.entity.getId().replace("{", "").replace("}", "");
            var lastVisitDate;
            var activityId;

            var count = 0;
            // if we have results
            if (data.d.results.length > 0) {
                // loop through the appointment results
                for (i = 0; i < data.d.results.length; i++) {
                    // if we have results
                    if (data.d.results[i].appointment_activity_parties.results.length > 0) {
                        // loop through the appointment_activity_parties
                        for (j = 0; j < data.d.results[i].appointment_activity_parties.results.length; j++) {
                            // if the party id type is contact and the contact ids match
                            if (data.d.results[i].appointment_activity_parties.results[j].PartyId.LogicalName == "contact" && contactId.toLowerCase() == data.d.results[i].appointment_activity_parties.results[j].PartyId.Id.toLowerCase()) {
                                // if we have not got a date yet
                                if (lastVisitDate == null) {
                                    // set the date as this is the first date we found
                                    lastVisitDate = data.d.results[i].ActualEnd;
                                    activityId = data.d.results[i].ActivityId;
                                } else {
                                    // if the current date is < new date
                                    if (lastVisitDate < data.d.results[i].ActualEnd) {
                                        // reset the date as we have found a later one
                                        lastVisitDate = data.d.results[i].ActualEnd;
                                        activityId = data.d.results[i].ActivityId;
                                    }
                                }
                                ++count;
                            }
                        }
                    }
                }
            }

            Xrm.Page.getAttribute("new_visitsthisyear").setValue(count);
            // if we found a completed appointment
            if (count > 0) {
                SetLookup("new_lastvisitcompleted", activityId, ParseJsonDate(lastVisitDate).toString('dd/MM/yyyy'), "Appointment");
            }
        },
        error: function (XmlHttpRequest, textStatus, errorObject) {
            //
            // Handle result from unsuccessful execution
            //
            alert("OData Execution Error Occurred");
        }
    });
}

//
// ExecuteQuery executes the specified OData Query asyncronously
//
// NOTE: Requires JSON and jQuery libraries. Review this Microsoft MSDN article before 
//       using this script http://msdn.microsoft.com/en-us/library/gg328025.aspx
//
function ExecuteVisitsLastYearQuery(ODataQuery) {

    // get the server url
    var serverUrl = Xrm.Page.context.getServerUrl();

    // Adjust URL for differences between on premise and online 
    if (serverUrl.match(/\/$/)) {
        serverUrl = serverUrl.substring(0, serverUrl.length - 1);
    }

    var ODataURL = serverUrl + "/XRMServices/2011/OrganizationData.svc" + ODataQuery;

    $.ajax({
        type: "GET",
        contentType: "application/json; charset=utf-8",
        datatype: "json",
        url: ODataURL,
        beforeSend: function (XMLHttpRequest) {
            XMLHttpRequest.setRequestHeader("Accept", "application/json");
        },
        success: function (data, textStatus, XmlHttpRequest) {
            //
            // Handle result from successful execution
            //
            // e.g. data.d.results
            var contactId = Xrm.Page.data.entity.getId().replace("{", "").replace("}", "");

            var count = 0;
            // if we have results
            if (data.d.results.length > 0) {
                // loop through the appointment results
                for (i = 0; i < data.d.results.length; i++) {
                    // if we have results
                    if (data.d.results[i].appointment_activity_parties.results.length > 0) {
                        // loop through the appointment_activity_parties
                        for (j = 0; j < data.d.results[i].appointment_activity_parties.results.length; j++) {
                            // if the party id type is contact and the contact ids match
                            if (data.d.results[i].appointment_activity_parties.results[j].PartyId.LogicalName == "contact" && contactId.toLowerCase() == data.d.results[i].appointment_activity_parties.results[j].PartyId.Id.toLowerCase()) {
                                ++count;
                            }
                        }
                    }
                }
            }

            Xrm.Page.getAttribute("new_visitslastyear").setValue(count);
        },
        error: function (XmlHttpRequest, textStatus, errorObject) {
            //
            // Handle result from unsuccessful execution
            //
            alert("OData Execution Error Occurred");
        }
    });
}


// function to parse JSON date into JavaScript Date
function ParseJsonDate(jsonDate) {
    var offset = new Date().getTimezoneOffset() * 60000;
    var parts = /\/Date\((-?\d+)([+-]\d{2})?(\d{2})?.*/.exec(jsonDate);

    if (parts[2] == undefined)
        parts[2] = 0;

    if (parts[3] == undefined)
        parts[3] = 0;

    return new Date(+parts[1] + offset + parts[2] * 3600000 + parts[3] * 60000);
};

//function to create a lookup control
function SetLookup(fieldName, idValue, textValue, typeValue) {
    var value = new Array();
    value[0] = new Object();
    value[0].id = idValue;
    value[0].name = textValue;
    value[0].typename = typeValue;

    Xrm.Page.getAttribute(fieldName).setValue(value);
}


//
// Error Handler
//
function ErrorHandler(XMLHttpRequest, textStatus, errorObject)
{ alert("Error Occurred : " + textStatus + ": " + JSON.parse(XMLHttpRequest.responseText).error.message.value); }

Hope this helps anyone with similar issues

Marked as answer by Iain Blackwood Thursday, November 21, 2013 12:16 PM
Edited by Iain Blackwood Thursday, November 21, 2013 12:19 PM Reformated code block

Wednesday, November 20, 2013 5:26 PM

Answered by:

Count the number of completed appointments a contact has had in CRM Online 2013

Question

Answers