SBX - Search With Button

SBX - Forum Post Title

Best way to qry large set of data and display for user to select a row and it populate crm form

Microsoft Dynamics CRM Forum

Matthew Hirst asked a question on 13 Jul 2018 8:48 AM

Question Status

Suggested Answer

I have a requirement to sift through postcode data that is held in a CRM entity, Postcode_Data.  It ideally needs to return all the records where the data matches and populate a grid whereby the user can select a row and the data from this row be written to the Account entity.

I've been struggling with writing a fetchXML query in a webresource as the toolkits I have added all appear to fail - currently using xrmsvctoolkit and the error is fetchDetails is null or undefined, which appears to be an error within the toolkit???

So where should I start, any pointers welcome.

Matt

Reply
Andrew Butenko responded on 13 Jul 2018 2:44 PM

Hello Matt,

It's hard to help you without code that you use. Can you please post code?

Reply
Drew Poggemann responded on 13 Jul 2018 3:23 PM
Suggested Answer

Here is a similar example to what you would want.  We created a web resource for a search and then put on the page in an iFrame.   

The following code will hopefully get you started...  Assumptions on your entity named "new_postcode_data" and field names are made up as well :)

            function PostalSearchClick() {

                var baseFetch = '<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"><entity name="new_postcode_data"><attribute name="new_postcodeid" /><attribute name="new_postalcode" /><attribute name="new_field1" /><attribute name="new_field2" /><order attribute="new_postalcode" descending="false" /><filter type="and">{FILTER}</filter></entity></fetch>';

                var postalCondition = "";
                var postalValue = $('#postal-control').find(":selected").attr('value');
                if (postalValue != -1) {
					postalValue = postalValue.replaceAll("*", "%");
                    postalCondition = '<condition attribute="new_postalcode" operator="eq" value="' + postalValue + '" />';
                }

				// other conditions you may want...
                var anotherCondition = "";
                var anotherValue = $('#another-control').val();
                if (anotherValue) {
                    anotherValue = anotherValue.replaceAll("*", "%");
                    var anotherCondition = '<condition attribute="new_anothervalue" operator="like" value="' + anotherValue + '" />';
                }

 
                var filterFetch = postalCondition + anotherCondition;
                var rawFetch = baseFetch.replace("{FILTER}", filterFetch)

                var fetch = encodeURI(rawFetch);
                var queryPath = "/api/data/v8.0/new_postcode_data?fetchXml=" + fetch;
                var req = new XMLHttpRequest();
                req.open("GET", parent.Xrm.Page.context.getClientUrl() + queryPath, true);
                req.setRequestHeader("Accept", "application/json");
                req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
                req.setRequestHeader("Prefer", "odata.maxpagesize=5000");
                req.onreadystatechange = function () {
                    if (this.readyState === 4) {
                        this.onreadystatechange = null;
                        if (this.status === 200) {                            
                            var results = JSON.parse(this.responseText);
                            //console.log(results);   

                            if (results)
                            {
                                //process your results...
                            }
                            
                        }
                        else {
                            //alert(this.statusText);
                        }
                    }
                };
                req.send();
            }

Hope this helps get you started.
Reply
Matthew Hirst responded on 16 Jul 2018 5:29 AM

Thanks for your help on this, so far I have created a HTML webresource, added it to the Account entity but its not working, here is the code:

<HTML><HEAD><TITLE>Opportunity Header</TITLE>

<SCRIPT src="a_crmsystem/.../jquery1.4.1.min.js&quot;></SCRIPT>

<SCRIPT type=text/javascript src="../WebResources/ClientGlobalContext.js.aspx"></SCRIPT>

<SCRIPT language=javascript>

           function PostalSearchClick()

           {

               var baseFetch = '<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"><entity name="a_citybypostalcode"><attribute name="a_citybypostalcodeid" /><attribute name="a_Street1" /><attribute name="a_Street2"/><attribute name="a_Street3" /><attribute name="a_postalcode" /><attribute name="a_country" /><attribute name="a_city" /><order attribute="a_Street3" descending="false" /><entity></fetch>';

               var postalCondition = "";

               var postalValue = $('#SrcCriteria').val();

               if (postalValue != -1)

               {

                   //postalValue = postalValue.replaceAll("*", "%");

                   postalCondition = '<condition attribute="a_postalcode" operator="eq" value="' + postalValue +'" />';

               }

               var filterFetch = postalCondition;

               var rawFetch = baseFetch.replace("{FILTER}", filterFetch)

               var fetch = encodeURI(rawFetch);

               var queryPath = "/api/data/v8.0/a_citybypostalcode?fetchXml=" + fetch;

               var req = new XMLHttpRequest();

               req.open("GET", parent.Xrm.Page.context.getClientUrl() + queryPath, true);

               req.setRequestHeader("Accept", "application/json");

               req.setRequestHeader("Content-Type", "application/json; charset=utf-8");

               req.setRequestHeader("Prefer", "odata.maxpagesize=5000");

               req.onreadystatechange = function () {

                   if (this.readyState === 4) {

                       this.onreadystatechange = null;

                       if (this.status === 200) {                            

                           var results = JSON.parse(this.responseText);

                           //console.log(results);  

                           if (results)

                           {

                               alert("Results");

                               //process your results...

                           }

                           {

                               alert("No Results");

                           }

                       }

                       else

                       {

                           alert(this.statusText);

                       }

                   }

               };

               req.send();

            }

       </SCRIPT>

<META charset=utf-8></HEAD>

<BODY contentEditable=true><INPUT id=SrcCriteria maxLength=20 name=SrcCriteria></INPUT> <INPUT onclick=PostalSearchClick(); id=btn_Src type=button value=Search></INPUT></BODY></HTML>

It all appears to work down to req and it then does nothing so I am assuming it is crashing out - what is the best way to error trap this?

This is on CRM 2011 RU 11, just for info.

Regards,

Matt

Reply
Drew Poggemann responded on 16 Jul 2018 5:55 AM
Suggested Answer

Sorry, I didn't know you were on CRM 2011...  

Some things:

1.  You are missing the {FILTER} in your baseFetch so it won't replace anything with your postal code criteria

2.  Overall some things need to change a bit with 2011 version since the API is different.  See the following example article:

www.crmsoftwareblog.com/.../using-the-fetchxml-crm-2011-service-within-a-javascript-web-resource

Hope this helps.

Reply
Matthew Hirst responded on 16 Jul 2018 7:01 AM

Hi,

Yes sorry I should have mentioned the version.

OK so I have altered the code to

               req.open("GET", parent.Xrm.Page.context.getServerUrl() + queryPath, true);

Also noticed I missed a / in the </entity> tag.

Removed the filter to see where it gets to and also added the debugger option.

The code now gets to alert(this.statusText); and outputs "Not Found"

Any pointers?

Matt

Reply
Andrew Butenko responded on 16 Jul 2018 8:12 AM
Suggested Answer

Matt,

Based on your code you use WebApi that became available in CRM 2016 and you're on CRM 2011 where webapi is not available.

The only way for you is to use Soap endpoint. Here is an example what you can do - www.crmsoftwareblog.com/.../execute-fetch-from-javascript-in-crm-2011

Reply
Matthew Hirst responded on 16 Jul 2018 8:39 AM

Thanks for your help, struggling with that to get it to work, either I'm being extremely stupid (could well be the case with this)- or there is an issue with that example.

Matt

Reply
Matthew Hirst responded on 16 Jul 2018 9:25 AM

Changed it around and got rid of the fetch to:

var postcodeArea = "a_postcode";

   var serverUrl = document.location.protocol + "//" + document.location.host + "/" + Xrm.Page.context.getOrgUniqueName();

   var oDataEndpointUrl = serverUrl + "/XRMServices/2011/OrganizationData.svc/";

   oDataEndpointUrl += "a_citybypostalcodeSet?$select=a_Street1,a_Street2,a_Street3&$filter=a_postalcode eq '" + postcodeArea +"'";

   var service = GetRequestObject();

   if (service != null)

   {

       service.open("GET", oDataEndpointUrl, false);

       service.setRequestHeader("X-Requested-Width", "XMLHttpRequest");

       service.setRequestHeader("Accept", "application/json, text/javascript, */*");

       service.send(null);

       var requestResults = eval('(' + service.responseText + ')').d;

       if (requestResults != null && requestResults.results.length > 0)

       {

           alert(requestResults.results.length);

       }

   }

This returns the required records in a timely fashion, can or rather how can I pass these returned records to a webresource to display, allowing the user to select one?  Or another option would be to open a lookup which would allow the user to select and it populate the address fields or even create a new address if not found?

Matt

Reply
Matthew Hirst responded on 18 Jul 2018 5:46 AM

Had a good search around the net and got an example working, I now face another issue.

Lets say we have the following

var sFetch = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>"+

                                 "<entity name='a_citybypostalcode'>"+

                                 "<attribute name='a_postalcode' />"+

                                 "<attribute name='a_country' />"+

                                 "<attribute name='a_city' />"+

                                 "<attribute name='a_citybypostalcodeid' />"+

                                 "<attribute name='a_street3' />"+

                                 "<attribute name='a_street2' />"+

                                 "<attribute name='a_street1' />"+

                                 "<order attribute='a_country' descending='false' />"+

                                 "<order attribute='a_postalcode' descending='false' />"+

                                 "<filter type='and'>"+

                                 "<condition attribute='a_postalcode' operator='eq' value='"+ pcode +"' />"+

                                 "</filter>"+

                                 "</entity>"+

                                 "</fetch>";

           _oService = new FetchUtil(_sOrgName, _sServerUrl);

           var res = _oService.Fetch(sFetch);

The fetch works and retrieces multiple records, however, in the postcode being searched for a_street3 does not contain any data, therefore the column is not returned.

How do I check for the column being returned before assigning its value?

Matt

Reply
Drew Poggemann responded on 13 Jul 2018 3:23 PM
Suggested Answer

Here is a similar example to what you would want.  We created a web resource for a search and then put on the page in an iFrame.   

The following code will hopefully get you started...  Assumptions on your entity named "new_postcode_data" and field names are made up as well :)

            function PostalSearchClick() {

                var baseFetch = '<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"><entity name="new_postcode_data"><attribute name="new_postcodeid" /><attribute name="new_postalcode" /><attribute name="new_field1" /><attribute name="new_field2" /><order attribute="new_postalcode" descending="false" /><filter type="and">{FILTER}</filter></entity></fetch>';

                var postalCondition = "";
                var postalValue = $('#postal-control').find(":selected").attr('value');
                if (postalValue != -1) {
					postalValue = postalValue.replaceAll("*", "%");
                    postalCondition = '<condition attribute="new_postalcode" operator="eq" value="' + postalValue + '" />';
                }

				// other conditions you may want...
                var anotherCondition = "";
                var anotherValue = $('#another-control').val();
                if (anotherValue) {
                    anotherValue = anotherValue.replaceAll("*", "%");
                    var anotherCondition = '<condition attribute="new_anothervalue" operator="like" value="' + anotherValue + '" />';
                }

 
                var filterFetch = postalCondition + anotherCondition;
                var rawFetch = baseFetch.replace("{FILTER}", filterFetch)

                var fetch = encodeURI(rawFetch);
                var queryPath = "/api/data/v8.0/new_postcode_data?fetchXml=" + fetch;
                var req = new XMLHttpRequest();
                req.open("GET", parent.Xrm.Page.context.getClientUrl() + queryPath, true);
                req.setRequestHeader("Accept", "application/json");
                req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
                req.setRequestHeader("Prefer", "odata.maxpagesize=5000");
                req.onreadystatechange = function () {
                    if (this.readyState === 4) {
                        this.onreadystatechange = null;
                        if (this.status === 200) {                            
                            var results = JSON.parse(this.responseText);
                            //console.log(results);   

                            if (results)
                            {
                                //process your results...
                            }
                            
                        }
                        else {
                            //alert(this.statusText);
                        }
                    }
                };
                req.send();
            }

Hope this helps get you started.
Reply
Drew Poggemann responded on 16 Jul 2018 5:55 AM
Suggested Answer

Sorry, I didn't know you were on CRM 2011...  

Some things:

1.  You are missing the {FILTER} in your baseFetch so it won't replace anything with your postal code criteria

2.  Overall some things need to change a bit with 2011 version since the API is different.  See the following example article:

www.crmsoftwareblog.com/.../using-the-fetchxml-crm-2011-service-within-a-javascript-web-resource

Hope this helps.

Reply
Andrew Butenko responded on 16 Jul 2018 8:12 AM
Suggested Answer

Matt,

Based on your code you use WebApi that became available in CRM 2016 and you're on CRM 2011 where webapi is not available.

The only way for you is to use Soap endpoint. Here is an example what you can do - www.crmsoftwareblog.com/.../execute-fetch-from-javascript-in-crm-2011

Reply

SBX - Two Col Forum

SBX - Migrated JS