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

  • Question

  • Hi Guys,

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

    1. Number of completed appointments, this year
    2. Number of completed appointments, last year
    3. 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