locked
Simple FetchXML query RRS feed

  • Question

  • I'm trying to pull in the Product's price when an Existing Product is added to a Quote.  So I'm trying to define this function in the onLoad event of the Quote Product, and then call it in the onChange event of the Product lookup field.  However, it's blowing up when the form is loaded.  Any clues?

    I'm using FetchXMLBuilder to create the Fetch statement, but something isn't working...

    GetProductPrice = function() {
    
    
    
        using System.Xml; 
    
    
    
        CrmService service = new CrmService();
    
        service.Credentials = System.Net.CredentialCache.DefaultCredentials;
    
    
    
        string fetchXml = @"
    
        <fetch mapping=""logical"">
    
    	    <entity name=""productpricelevel"">
    
    		    <attribute name=""amount""/>
    
    		    <filter type=""and"">
    
    			    <condition attribute=""productid"" operator=""eq"" value= crmForm.all.productid.DataValue[0].id />
    
    		    </filter>
    
    	    </entity>
    
        </fetch>
    
        ";
    
        
    
        string result = service.Fetch(fetchXml);
    
       
    
        string price; 
    
    
    
        XmlDocument doc = new XmlDocument();
    
        doc.LoadXml(result); 
    
    
    
        foreach(XmlNode node in doc.DocumentElement.ChildNodes)
    
        {
    
             price = node.SelectSingleNode("amount").InnerText;
    
        } 
    
        
    
        crmForm.all.priceperunit.DataValue = parseFloat(price);
    
    
    
    }
    
    
    • Edited by NateOne Wednesday, October 21, 2009 12:27 PM correction
    Wednesday, October 21, 2009 12:25 PM

Answers

  • Hassan,

    Unfortunately that also seems to give an error; not sure why.   But in the meantime I found this link

    http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/4f469d60-74aa-43e3-acff-39214ff4e49f 

    And customized it to work for me and it works!

    I was hoping for something less complicated, but I guess this is as simple as it can be for now.

    Many thanks for your help!

    Nate

    GetProductPrice = function() {
        var authenticationHeader = GenerateAuthenticationHeader();
        //Prepare the SOAP message.
        var xml = "<?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'>" +
        authenticationHeader +
        "<soap:Body>" +
        "<Retrieve xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>" +
        "<entityName>product</entityName>" +
        "<id>" + crmForm.all.productid.DataValue[0].id + "</id>" +
        "<columnSet xmlns:q1='http://schemas.microsoft.com/crm/2006/Query' xsi:type='q1:ColumnSet'>" +
        "<q1:Attributes>" +
        "<q1:Attribute>price</q1:Attribute>" +
        "</q1:Attributes>" +
        "</columnSet>" +
        "</Retrieve>" +
        "</soap:Body>" +
        "</soap:Envelope>";
        //Prepare the xmlHttpObject and send the request.
        var xHReq = new ActiveXObject("Msxml2.XMLHTTP");
        xHReq.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
        xHReq.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/Retrieve");
        xHReq.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
        xHReq.setRequestHeader("Content-Length", xml.length);
        xHReq.send(xml);
        //Capture the result.
        var resultXml = xHReq.responseXML;
        var price = resultXml.selectSingleNode("//q1:price").nodeTypedValue;
        crmForm.all.priceperunit.DataValue = parseFloat(price);
    }
    Wednesday, October 21, 2009 2:34 PM

All replies

  • I beg a pardon but on what language this wonderful code is written?
    Truth is opened the prepared mind My blog - http://a33ik.blogspot.com
    Wednesday, October 21, 2009 12:31 PM
    Moderator
  • Hi,

    Where are you using this code? Your code looks like a mix of c# and JavaScript.

    Please post back with where your code is used, what you are trying to do and I'm sure some on the forum will be able to help you out.


    H.

    Hassan Hussain | http://hassanhussain.wordpress.com/ | http://www.mscrmvirtualusergroup.com/
    Wednesday, October 21, 2009 12:33 PM
  • It's supposed to be Javascript, but I guess that shows what a newbie I am!!

    I would like it to be Javascript, so I can put it i the Onload event of the Quote Product, and then call it (like this GetProductPrice(); ) in the onChange event of the Quote Product Product Lookup field.

    Hopefully that makes sense!
    Wednesday, October 21, 2009 12:38 PM
  • Hi,

    I wouldn't worry about it. We are all learning. 

    Please have a read of this post by Adi Katz.
    http://mscrm4ever.blogspot.com/2008/09/ajax-using-fetch-message.html

    The post describes how to use FetchXML and AJAX to retrieve data from the server. 

    Once you have the desired data you will need to set it to the correct field. Looking at the code you posted I'm sure you'll be able to manage this.

    If you haven't already I would encourage you to download the MS CRM SDK. The SDk comes with a help file that describes how to perform common functions like setting
    fields with data.

    The Link to download the SDK below.
    http://www.microsoft.com/downloads/details.aspx?FamilyID=82E632A7-FAF9-41E0-8EC1-A2662AAE9DFB&displaylang=en

    Please feel free to post back if you need further assistance.


    Hassan.


    Hassan Hussain | http://hassanhussain.wordpress.com/ | http://www.mscrmvirtualusergroup.com/
    • Proposed as answer by Hassan Hussain Wednesday, October 21, 2009 12:46 PM
    Wednesday, October 21, 2009 12:46 PM
  • Wednesday, October 21, 2009 1:26 PM
  • Thanks for posting guys!

    Hassan,
    I'd already seen Adi's ajax using fetch posting but it looked too complicated for me, and I already have spent hours studying the SDK, but no sample seems to fit what I'm doing.  But I tried to copy what Adi did at the top of his sample.

    Sergey
    I checked out the links you suggested , but they look so complicated!  I was  hoping not to have to do all that fancy stuff building SOAP envelopes and stuff. 

    I thought that with FetchXML you don't have to do that complicated stuff! 

    Anyhow, here's the script that I have now, any clues what's wrong?

    GetProductPrice = function() {
    
    var result = Fetch('<fetch version="1.0" output-format="xml-platform" mapping="logical">
    	<entity name="productpricelevel">
    		<attribute name="amount"/>
    		<filter type="and">
    			<condition attribute="productid" operator="eq" value=crmForm.all.productid.DataValue[0].id/>
    		</filter>
    	</entity>
    </fetch>');
    
        string price; 
    
        XmlDocument doc = new XmlDocument();
        doc.LoadXml(result); 
    
        foreach(XmlNode node in doc.DocumentElement.ChildNodes)
        {
             price = node.SelectSingleNode("amount").InnerText;
        } 
        
        crmForm.all.priceperunit.DataValue = parseFloat(price);
    
    }
    


    Wednesday, October 21, 2009 1:53 PM
  • Hi,

    Please post the code on the OnLoad event of the form and see if the alert contains the correct value. 

    H.

    //Please paste the code below to the OnLoad event of your form.

    function OnCrmPageLoad()
    {
     var FetchResult = Fetch('<fetch version="1.0" output-format="xml-platform" mapping="logical"> <entity name="productpricelevel">
    <attribute name="amount"/>
    <filter type="and">
    <condition attribute="productid" operator="eq" value=crmForm.all.productid.DataValue[0].id/>
    </filter>
    </entity>
    </fetch>');
     alert( FetchResult.selectSingleNode("//amount").text ); //User Fullname
    }

    function Fetch( xml )
    {
     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(xml); // Microsoft _HtmlEncode function
      Xml += "</fetchXml>";
      Xml += "</Fetch>";
      Xml += "</soap:Body>";
      Xml += "</soap:Envelope>";

     var XmlHttp = CreateXmlHttpObject(); // Microsot 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;
    }

    function CreateXmlHttpObject() //CreateXmlHttp
    {
     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;
    }

    function FetchEncode( strInput ) //_HtmlEncode
    {
     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;
    }

    OnCrmPageLoad();


    Hassan Hussain | http://hassanhussain.wordpress.com/ | http://www.mscrmvirtualusergroup.com/
    Wednesday, October 21, 2009 2:03 PM
  • Hassan,

    Unfortunately that also seems to give an error; not sure why.   But in the meantime I found this link

    http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/4f469d60-74aa-43e3-acff-39214ff4e49f 

    And customized it to work for me and it works!

    I was hoping for something less complicated, but I guess this is as simple as it can be for now.

    Many thanks for your help!

    Nate

    GetProductPrice = function() {
        var authenticationHeader = GenerateAuthenticationHeader();
        //Prepare the SOAP message.
        var xml = "<?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'>" +
        authenticationHeader +
        "<soap:Body>" +
        "<Retrieve xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>" +
        "<entityName>product</entityName>" +
        "<id>" + crmForm.all.productid.DataValue[0].id + "</id>" +
        "<columnSet xmlns:q1='http://schemas.microsoft.com/crm/2006/Query' xsi:type='q1:ColumnSet'>" +
        "<q1:Attributes>" +
        "<q1:Attribute>price</q1:Attribute>" +
        "</q1:Attributes>" +
        "</columnSet>" +
        "</Retrieve>" +
        "</soap:Body>" +
        "</soap:Envelope>";
        //Prepare the xmlHttpObject and send the request.
        var xHReq = new ActiveXObject("Msxml2.XMLHTTP");
        xHReq.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
        xHReq.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/Retrieve");
        xHReq.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
        xHReq.setRequestHeader("Content-Length", xml.length);
        xHReq.send(xml);
        //Capture the result.
        var resultXml = xHReq.responseXML;
        var price = resultXml.selectSingleNode("//q1:price").nodeTypedValue;
        crmForm.all.priceperunit.DataValue = parseFloat(price);
    }
    Wednesday, October 21, 2009 2:34 PM
  • Not a problem. 

    Excellent. Well done.

    H.

    Hassan Hussain | http://hassanhussain.wordpress.com/ | http://www.mscrmvirtualusergroup.com/
    Wednesday, October 21, 2009 2:47 PM
  • Retrieve method is much more simple than Fetch, but Fetch more powerfull. If you can use Retrieve for your case, then use it!
    I think so.
    Wednesday, October 21, 2009 2:48 PM