locked
How to Count Service Activities , Completed , Canceled , scheduled ? RRS feed

  • Question

  • Hi all ,

    I want to add 3 fields in Cases so that it can display me :

    1. The number of service activities that are completed

    2. The number of service activities that are canceled

    3. The number of service activities that are schedule

    for example

     crmForm.all.new_completed.DataValue = {how do i count the completed appointments ?}

    Monday, August 9, 2010 5:28 AM

Answers

  • If you are having challenges with this, you might also consider using workflows and increment the value of the fields when a Service Activity status changes, a Service Activity is created, etc.


    Regards, Donna

    • Marked as answer by Jim Glass Jr Tuesday, August 17, 2010 4:37 PM
    Thursday, August 12, 2010 5:06 PM
  • Hi

    I have changed the FetchXML try this out.

    Please copy this code again

    // SERVICE Activities 
    fetchXml = Fetch("<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'><entity name='serviceappointment'><attribute name='subject'/><attribute name='statecode'/><attribute name='scheduledstart'/><attribute name='scheduledend'/><attribute name='createdby'/><attribute name='regardingobjectid'/><attribute name='activityid'/><order attribute='statecode' descending='false'/><link-entity name='activityparty' from='activityid' to='activityid' alias='aa'><link-entity name='incident' from='incidentid' to='partyid' alias='ab'><filter type='and'><condition attribute='incidentid' operator='eq' uitype='incident' value='" + crmForm.ObjectId + "'/></filter></link-entity></link-entity></entity></fetch>");
    
    var actHistNodes = fetchXml.selectNodes("//statecode");
    if (actHistNodes.length > 0) 
    {
    	var iOpen = 0; 
    	var iScheduled = 0;
    	var iCompleted = 0; 
    	var iCanceled = 0;
    	
    	for( i = 0; i < actHistNodes.length; i++) { 
    		switch(actHistNodes[i].attributes[0].text) { 
    			case "Open" : iOpen++; break; 
    			case "Scheduled" : iScheduled++; break; 
    			case "Completed" : iCompleted++; break; 
    			case "Canceled" : iCanceled++; break; 
    		} 
    	}
    	
    	crmForm.all.new_completed.DataValue = iCompleted;
    	crmForm.all.new_Scheduled.DataValue = iScheduled;
    	crmForm.all.new_Canceled.DataValue = iCanceled;
    } 
    
    
    //FETCH FUNCTION
    function Fetch( xmlRequestString )
    {
     var Xml = "<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">"
     Xml += GenerateAuthenticationHeader()
     Xml += "<soap:Body>";
     Xml += "<Fetch xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">";
     Xml += "<fetchXml>";
     Xml += FetchEncode(xmlRequestString); // HtmlEncode function
     Xml += "</fetchXml>";
     Xml += "</Fetch>";
     Xml += "</soap:Body>";
     Xml += "</soap:Envelope>";
    
     var XmlHttp = CreateXmlHttpObject(); // CreateXmlHttp function
     XmlHttp.open("POST", "/mscrmservices/2007/crmservice.asmx", false ); //Sync Request
     XmlHttp.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
     XmlHttp.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/Fetch");
     XmlHttp.send(Xml);
    
     var XmlDoc = new ActiveXObject("Msxml2.DOMDocument");
     XmlDoc.async = false;
     XmlDoc.resolveExternals = false;
     XmlDoc.loadXML(XmlHttp.responseXML.text);
    
     return XmlDoc;
    }
    
    
    // CreateXmlHttp function
    function CreateXmlHttpObject() 
    {
     var oXmlHttp = null;
     if (window.XMLHttpRequest) {
     oXmlHttp = new XMLHttpRequest();
     }
     else {
     var arrProgIds = ["Msxml2.XMLHTTP", "Microsoft.XMLHTTP"];
     for (var iCount = 0; iCount < arrProgIds.length; iCount++) {
     try {
     oXmlHttp = new ActiveXObject(arrProgIds[iCount]);
     break;
     }
     catch (e) { }
     }
     }
    
     return oXmlHttp;
    }
    
    //HtmlEncode
    function FetchEncode( strInput ) 
    {
     var c;
     var HtmlEncode = '';
    
     if(strInput == null)
     {
     return null;
     }
     if (strInput == '')
     {
     return '';
     }
    
     for(var cnt = 0; cnt < strInput.length; cnt++)
     {
     c = strInput.charCodeAt(cnt);
    
     if (( ( c > 96 ) && ( c < 123 ) ) ||
     ( ( c > 64 ) && ( c < 91 ) ) ||
     ( c == 32 ) ||
     ( ( c > 47 ) && ( c < 58 ) ) ||
     ( c == 46 ) ||
     ( c == 44 ) ||
     ( c == 45 ) ||
     ( c == 95 ))
     {
     HtmlEncode = HtmlEncode + String.fromCharCode(c);
     }
     else 
     {
     HtmlEncode = HtmlEncode + '&#' + c + ';';
     }
     }
     
     return HtmlEncode;
    }

    There was one extra '}' closing braces that I have removed

    Amar

    • Edited by Amarsen Vangoor Wednesday, August 11, 2010 10:54 AM None
    • Proposed as answer by Faisal Fiaz Wednesday, August 11, 2010 4:05 PM
    • Marked as answer by Jim Glass Jr Tuesday, August 17, 2010 4:38 PM
    Wednesday, August 11, 2010 10:17 AM

All replies

  • Hi,

    You can achieve this by using Plug-in or Custom Workflow activity (Server Side) and if you want to do it by using Javascript then you have to call Client Side MS CRM Webservice call.

    Please refer below link - how to work with client side web services with MS CRM.
    http://www.stunnware.com/crm2/topic.aspx?id=jswebservice2

    And here is the tool which generates the Javascript Code (very helpful):
    http://www.stunnware.com/crm2/topic.aspx?id=JSWebService

    When you change the status of the Service Actity, call the Plugin / Custom Workflow activity, and Get all Service activites associated with Case.

    First retrieve all cases which are Completed => You will get the count, update it in the Field.

    Next retrieve all cases which are Cancelled => You will get the count, update it in the Field..

    First retrieve all cases which are Scheduled / Active => You will get the count, update it in the Field..

    Hope this helps.

     


    Thanks, Ranjitsingh R | http://mscrm-developer.blogspot.com/ | MS CRM Consultant
    • Proposed as answer by Ranjitsingh R Monday, August 9, 2010 5:45 AM
    Monday, August 9, 2010 5:45 AM
  • Thanks for your reply but I dont get it :o

     

    I just want something simple , a code to add on Form Load or on field change if its possible...

     

     

    Monday, August 9, 2010 11:48 AM
  • Dear Amar ,

    First of all I would like to thank you for your prompt reply and the trouble of creating that javascript code ..

    I have put the code on onload in the Case Entity and created the fields

     crmForm.all.new_completed

    crmForm.all.new_Scheduled 

    crmForm.all.new_Canceled

    Unfortunately it doesnt give values to the fields and Also I dont get any errors fromt the code.

     

     

    Tuesday, August 10, 2010 7:21 AM
  • Hi,

    Can you confirm to me that you only want the counts of Service Appointments on a selected case?

    Let me test the code again and get back to you.

    I NOTICED A MINOR BUG IN THE FETCH XML (replaced the double quote around subject with single quote) please can you replace the FETCH with the following line

    "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'><entity name='serviceappointment'><attribute name='subject'/><attribute name='statecode'/><attribute name='scheduledstart'/><attribute name='scheduledend'/><attribute name='createdby'/><attribute name='regardingobjectid'/><attribute name='activityid'/><order attribute='statecode' descending='false'/><link-entity name='activityparty' from='activityid' to='activityid' alias='aa'><link-entity name='incident' from='incidentid' to='partyid' alias='ab'><filter type='and'><condition attribute='incidentid' operator='eq' uitype='incident' value='"

     

    + crmForm.ObjectId + "'/></filter></link-entity></link-entity></entity></fetch>"

    Amar

    Tuesday, August 10, 2010 10:57 AM
  • Hi,

    I have now tested the code and it is working my side... please do the above or replace the following fetchXml line with the below line...

    fetchXml = Fetch("<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'><entity name='serviceappointment'><attribute name='subject'/><attribute name='statecode'/><attribute name='scheduledstart'/><attribute name='scheduledend'/><attribute name='createdby'/><attribute name='regardingobjectid'/><attribute name='activityid'/><order attribute='statecode' descending='false'/><link-entity name='activityparty' from='activityid' to='activityid' alias='aa'><link-entity name='incident' from='incidentid' to='partyid' alias='ab'><filter type='and'><condition attribute='incidentid' operator='eq' uitype='incident' value='" + crmForm.ObjectId + "'/></filter></link-entity></link-entity></entity></fetch>");

    Hope this solves your problem.

    Thanks,

    Amar

    Tuesday, August 10, 2010 11:20 AM
  • hi Aran,

     

    Unfortunately is not working , I copy/paste and replace the fetchXml code and still not working... :(

    (We are using CRM 4.0 btw.)

     

    I confirm that we want the counts of Service Appointments on a selected case

    Wednesday, August 11, 2010 6:26 AM
  • I have found this code on the below url which is working , but I wish I could modify this code so to transfer the values to the fields

    crmForm.all.new_completed

    crmForm.all.new_Scheduled 

    crmForm.all.new_Canceled

     

    http://jianwang.blogspot.com/2008/02/show-how-many-activitieshistory.html

     

     

    Wednesday, August 11, 2010 6:45 AM
  • Hi

    I have changed the FetchXML try this out.

    Please copy this code again

    // SERVICE Activities 
    fetchXml = Fetch("<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'><entity name='serviceappointment'><attribute name='subject'/><attribute name='statecode'/><attribute name='scheduledstart'/><attribute name='scheduledend'/><attribute name='createdby'/><attribute name='regardingobjectid'/><attribute name='activityid'/><order attribute='statecode' descending='false'/><link-entity name='activityparty' from='activityid' to='activityid' alias='aa'><link-entity name='incident' from='incidentid' to='partyid' alias='ab'><filter type='and'><condition attribute='incidentid' operator='eq' uitype='incident' value='" + crmForm.ObjectId + "'/></filter></link-entity></link-entity></entity></fetch>");
    
    var actHistNodes = fetchXml.selectNodes("//statecode");
    if (actHistNodes.length > 0) 
    {
    	var iOpen = 0; 
    	var iScheduled = 0;
    	var iCompleted = 0; 
    	var iCanceled = 0;
    	
    	for( i = 0; i < actHistNodes.length; i++) { 
    		switch(actHistNodes[i].attributes[0].text) { 
    			case "Open" : iOpen++; break; 
    			case "Scheduled" : iScheduled++; break; 
    			case "Completed" : iCompleted++; break; 
    			case "Canceled" : iCanceled++; break; 
    		} 
    	}
    	
    	crmForm.all.new_completed.DataValue = iCompleted;
    	crmForm.all.new_Scheduled.DataValue = iScheduled;
    	crmForm.all.new_Canceled.DataValue = iCanceled;
    } 
    
    
    //FETCH FUNCTION
    function Fetch( xmlRequestString )
    {
     var Xml = "<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">"
     Xml += GenerateAuthenticationHeader()
     Xml += "<soap:Body>";
     Xml += "<Fetch xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">";
     Xml += "<fetchXml>";
     Xml += FetchEncode(xmlRequestString); // HtmlEncode function
     Xml += "</fetchXml>";
     Xml += "</Fetch>";
     Xml += "</soap:Body>";
     Xml += "</soap:Envelope>";
    
     var XmlHttp = CreateXmlHttpObject(); // CreateXmlHttp function
     XmlHttp.open("POST", "/mscrmservices/2007/crmservice.asmx", false ); //Sync Request
     XmlHttp.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
     XmlHttp.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/Fetch");
     XmlHttp.send(Xml);
    
     var XmlDoc = new ActiveXObject("Msxml2.DOMDocument");
     XmlDoc.async = false;
     XmlDoc.resolveExternals = false;
     XmlDoc.loadXML(XmlHttp.responseXML.text);
    
     return XmlDoc;
    }
    
    
    // CreateXmlHttp function
    function CreateXmlHttpObject() 
    {
     var oXmlHttp = null;
     if (window.XMLHttpRequest) {
     oXmlHttp = new XMLHttpRequest();
     }
     else {
     var arrProgIds = ["Msxml2.XMLHTTP", "Microsoft.XMLHTTP"];
     for (var iCount = 0; iCount < arrProgIds.length; iCount++) {
     try {
     oXmlHttp = new ActiveXObject(arrProgIds[iCount]);
     break;
     }
     catch (e) { }
     }
     }
    
     return oXmlHttp;
    }
    
    //HtmlEncode
    function FetchEncode( strInput ) 
    {
     var c;
     var HtmlEncode = '';
    
     if(strInput == null)
     {
     return null;
     }
     if (strInput == '')
     {
     return '';
     }
    
     for(var cnt = 0; cnt < strInput.length; cnt++)
     {
     c = strInput.charCodeAt(cnt);
    
     if (( ( c > 96 ) && ( c < 123 ) ) ||
     ( ( c > 64 ) && ( c < 91 ) ) ||
     ( c == 32 ) ||
     ( ( c > 47 ) && ( c < 58 ) ) ||
     ( c == 46 ) ||
     ( c == 44 ) ||
     ( c == 45 ) ||
     ( c == 95 ))
     {
     HtmlEncode = HtmlEncode + String.fromCharCode(c);
     }
     else 
     {
     HtmlEncode = HtmlEncode + '&#' + c + ';';
     }
     }
     
     return HtmlEncode;
    }

    There was one extra '}' closing braces that I have removed

    Amar

    • Edited by Amarsen Vangoor Wednesday, August 11, 2010 10:54 AM None
    • Proposed as answer by Faisal Fiaz Wednesday, August 11, 2010 4:05 PM
    • Marked as answer by Jim Glass Jr Tuesday, August 17, 2010 4:38 PM
    Wednesday, August 11, 2010 10:17 AM
  • If you are having challenges with this, you might also consider using workflows and increment the value of the fields when a Service Activity status changes, a Service Activity is created, etc.


    Regards, Donna

    • Marked as answer by Jim Glass Jr Tuesday, August 17, 2010 4:37 PM
    Thursday, August 12, 2010 5:06 PM
  • Hi,

    Were you able to resolve your issue?  Did one of these solutions work for you?

    Thank you

     


    Regards, Donna

    Tuesday, August 17, 2010 2:42 PM
  • I Was on vacations and didnt find a chance to reply...

    I am trying to make Amarsen Reddy code to work but Unfortunately I cant ... :o

    I think I am making something wrong....

     Can you please let me know  what type this fields should be ?

    crmForm.all.new_completed

    crmForm.all.new_Scheduled 

    crmForm.all.new_Canceled

    Atm I have them as nvarchar type - text , but still cant get any values  or I get some errors that the fields doesnt support this kind of values e,t,c

     

    Tuesday, August 31, 2010 8:21 AM