Answered by:
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