locked
Get Value Of All Items Associated With Order/Quote/Custom Entity (i.e. Products) RRS feed

  • Question

  • Hi Guys, 

    First, thanks to all who contribute to this, since having started my career in CRM Dev in October, everyone here has been invaluable so cheers. 

    Now, i am after some more help. 

    I need to emulate what the Quote & Order entities do natively in CRM in collating the values for all associated products. In either of these entities, you can add products to either a sales order or a quote and, OnSave, CRM will collate all the values from this list of products and publish that total cost (or other value) to a field on the order/quote form. 

    This would be hugely useful to be able to do in other places. I am currently using SOAP queries to retrieve data from a lookup to another CRM Record in other areas of our system but i that is where there is a lookup attribute on the form i am using (and therefore a 1:N relationship). What i am unsure is how to retrieve data from a group of items that are associated with record i am using via a N:1 relationship. 

    The code i am using for my current SOAP query is:
    var ITprodLookup = new Array();  
    ITprodLookup = crmForm.all.citja_itproductid.DataValue;  
     
    if(ITprodLookup != null)  
    {  
        var ITProdId = ITprodLookup[0].id.replace(/^{+|}+$/g, "");  
     
        var serverUrl = "/mscrmservices/2007/crmservice.asmx";  
        var xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");  
        xmlhttp.open("POST",serverUrl,false);  
        xmlhttp.setRequestHeader("Content-Type","text/xml; charset=utf-8");  
        xmlhttp.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/Retrieve");  
     
        var message =   
        [  
            "<?xml version='1.0' encoding='utf-8'?>",  
            "<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'>",  
            GenerateAuthenticationHeader(),  
            "<soap:Body>",  
            "   <Retrieve xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>",  
            "       <entityName>citja_itproduct</entityName>",  
            "       <id>", ITProdId, "</id>",  
            "       <columnSet xmlns:q1='http://schemas.microsoft.com/crm/2006/Query' xsi:type='q1:ColumnSet'>",  
            "           <q1:Attributes>",    
            "               <q1:Attribute>citja_descrip</q1:Attribute>",
            "               <q1:Attribute>citja_pclaptophddtype</q1:Attribute>",   
            "               <q1:Attribute>citja_laptoppcram</q1:Attribute>",   
            "               <q1:Attribute>citja_pclaptophddsize</q1:Attribute>",       
            "           </q1:Attributes>",  
            "       </columnSet>",  
            "   </Retrieve>",  
            "</soap:Body>",  
            "</soap:Envelope>" 
        ].join("");  
     
        xmlhttp.send(message);  
        //Capture the result.  
        var resultXml = xmlhttp.responseXML;  
     
        //Check for errors.  
        var errorCount = resultXml.selectNodes('//error').length;  
        if (errorCount != 0)  
        {  
         var msg = resultXml.selectSingleNode('//description').nodeTypedValue;  
         alert(msg);  
        }  
    else
    {
      var node = resultXml.selectSingleNode("//q1:citja_descrip");
      if (node != null) crmForm.all.citja_description.DataValue = node.nodeTypedValue;
    var node2 = resultXml.selectSingleNode("//q1:citja_pclaptophddtype");
      if (node2 != null) crmForm.all.citja_hddtypepc.DataValue = node2.nodeTypedValue;
    var node3 = resultXml.selectSingleNode("//q1:citja_laptoppcram");
      if (node3 != null) crmForm.all.citja_rampc.DataValue = node3.nodeTypedValue;
    var node4 = resultXml.selectSingleNode("//q1:citja_pclaptophddsize");
      if (node4 != null) crmForm.all.citja_hddsizepc.DataValue = node4.nodeTypedValue;
    }
    }
    
    I insert this as an example purely to give an example of what i have been working with, i realize this is probably far from the answer!

    Any help would be greatly appreciated and apologies if i have not explained myself clearly enough. 

    Kind Regards

    Joel Abbott
    Wednesday, March 10, 2010 10:00 AM

Answers

  • Maybe too late for the original poster, but for reference, you may want to look at using the Fetch request, as this can return data from more than one entity, which is is more flexibile than a retrieve. However a Fetch is not able to do a grouped Sum, for example, so you'd still need some extra code to sum the child values.

     

     


    Microsoft CRM MVP - http://mscrmuk.blogspot.com http://www.excitation.co.uk
    Monday, April 19, 2010 8:32 PM
    Moderator

All replies

  • Hi Guys, 

    First, thanks to all who contribute to this, since having started my career in CRM Dev in October, everyone here has been invaluable so cheers. 

    Now, i am after some more help. 

    I need to emulate what the Quote & Order entities do natively in CRM in collating the values for all associated products. In either of these entities, you can add products to either a sales order or a quote and, OnSave, CRM will collate all the values from this list of products and publish that total cost (or other value) to a field on the order/quote form. 

    This would be hugely useful to be able to do in other places. I am currently using SOAP queries to retrieve data from a lookup to another CRM Record in other areas of our system but i that is where there is a lookup attribute on the form i am using (and therefore a 1:N relationship). What i am unsure is how to retrieve data from a group of items that are associated with record i am using via a N:1 relationship. 

    The code i am using for my current SOAP query is:
    var ITprodLookup = new Array();  
    ITprodLookup = crmForm.all.citja_itproductid.DataValue;  
     
    if(ITprodLookup != null)  
    {  
        var ITProdId = ITprodLookup[0].id.replace(/^{+|}+$/g, "");  
     
        var serverUrl = "/mscrmservices/2007/crmservice.asmx";  
        var xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");  
        xmlhttp.open("POST",serverUrl,false);  
        xmlhttp.setRequestHeader("Content-Type","text/xml; charset=utf-8");  
        xmlhttp.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/Retrieve");  
     
        var message =   
        [  
            "<?xml version='1.0' encoding='utf-8'?>",  
            "<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'>",  
            GenerateAuthenticationHeader(),  
            "<soap:Body>",  
            "   <Retrieve xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>",  
            "       <entityName>citja_itproduct</entityName>",  
            "       <id>", ITProdId, "</id>",  
            "       <columnSet xmlns:q1='http://schemas.microsoft.com/crm/2006/Query' xsi:type='q1:ColumnSet'>",  
            "           <q1:Attributes>",    
            "               <q1:Attribute>citja_descrip</q1:Attribute>",
            "               <q1:Attribute>citja_pclaptophddtype</q1:Attribute>",   
            "               <q1:Attribute>citja_laptoppcram</q1:Attribute>",   
            "               <q1:Attribute>citja_pclaptophddsize</q1:Attribute>",       
            "           </q1:Attributes>",  
            "       </columnSet>",  
            "   </Retrieve>",  
            "</soap:Body>",  
            "</soap:Envelope>" 
        ].join("");  
     
        xmlhttp.send(message);  
        //Capture the result.  
        var resultXml = xmlhttp.responseXML;  
     
        //Check for errors.  
        var errorCount = resultXml.selectNodes('//error').length;  
        if (errorCount != 0)  
        {  
         var msg = resultXml.selectSingleNode('//description').nodeTypedValue;  
         alert(msg);  
        }  
    else
    {
      var node = resultXml.selectSingleNode("//q1:citja_descrip");
      if (node != null) crmForm.all.citja_description.DataValue = node.nodeTypedValue;
    var node2 = resultXml.selectSingleNode("//q1:citja_pclaptophddtype");
      if (node2 != null) crmForm.all.citja_hddtypepc.DataValue = node2.nodeTypedValue;
    var node3 = resultXml.selectSingleNode("//q1:citja_laptoppcram");
      if (node3 != null) crmForm.all.citja_rampc.DataValue = node3.nodeTypedValue;
    var node4 = resultXml.selectSingleNode("//q1:citja_pclaptophddsize");
      if (node4 != null) crmForm.all.citja_hddsizepc.DataValue = node4.nodeTypedValue;
    }
    }
    I insert this as an example purely to give an example of what i have been working with, i realize this is probably far from the answer!

    Any help would be greatly appreciated and apologies if i have not explained myself clearly enough. 

    Kind Regards

    Joel Abbott

    Monday, March 8, 2010 5:12 PM
  • Maybe too late for the original poster, but for reference, you may want to look at using the Fetch request, as this can return data from more than one entity, which is is more flexibile than a retrieve. However a Fetch is not able to do a grouped Sum, for example, so you'd still need some extra code to sum the child values.

     

     


    Microsoft CRM MVP - http://mscrmuk.blogspot.com http://www.excitation.co.uk
    Monday, April 19, 2010 8:32 PM
    Moderator