Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Ok so I have been working on this issue for a few days now.</p> <p>I found that it was possible to complete this task with the help of the following tool <a href="http://dynamicsxrmtools.codeplex.com/" rel="nofollow noreferrer">XRM Dynamics Tools</a> to help generate my OData query code.</p> <p>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.</p> <p>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.</p> <p>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:</p> <p><img src="https://i.stack.imgur.com/s8KTI.png" alt="enter image description here"></p> <p>My code follows:</p> <pre><code>/// &lt;reference path="XrmPageTemplate.js" /&gt; /// &lt;reference path="JQuery.js" /&gt; /// &lt;reference path="SDK.REST.js" /&gt; /// &lt;reference path="json2.js" /&gt; 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 += "&amp;$expand=appointment_activity_parties"; // Expand sub tables query += "&amp;$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'" + start + "' and "; // Where query += "ActualEnd ge datetime'" + end + "' 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'" + start + "' and "; // Where query += "ActualEnd ge datetime'" + end + "' 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 &gt; 0) { // loop through the appointment results for (i = 0; i &lt; data.d.results.length; i++) { // if we have results if (data.d.results[i].appointment_activity_parties.results.length &gt; 0) { // loop through the appointment_activity_parties for (j = 0; j &lt; 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" &amp;&amp; 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 &lt; new date if (lastVisitDate &lt; 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 &gt; 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 &gt; 0) { // loop through the appointment results for (i = 0; i &lt; data.d.results.length; i++) { // if we have results if (data.d.results[i].appointment_activity_parties.results.length &gt; 0) { // loop through the appointment_activity_parties for (j = 0; j &lt; 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" &amp;&amp; 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); } </code></pre> <p>Hope this helps anyone with similar issues.</p>
    singulars
    1. This table or related slice is empty.
    1. POCalculate the Last Completed Appointment for a particular Contact CRM 2011 Online JavaScript
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USComic Coder
    1. USComic Coder
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCalculate the Last Completed Appointment for a particular Contact CRM 2011 Online JavaScript
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload