Get all Contacts of All Hierarchical Accounts Using FetchXML and Web API

For ex. To demonstrate, I created a HTML web resource with 1 button on Contact form. While clicking on button, I will query contacts to get list of all contacts under the Account hierarchy.

webapi

After clicking on Parent Account button, we will get a list of all Contacts under the Account hierarchy. Please check below Image. It will be the output of our development. Now to get this output, what we need to do in HTML file, that I am going to explain.

webapi

Microsoft Dynamics CRM 2015 and Microsoft Dynamics CRM Services Online 2015 Update introduces the capability to define specific self-referencing one-to-many entity relationships as hierarchical. You can write queries that return related data in these hierarchies.

With Web API in MS CRM, we are retrieve data using FetchXML. Easy way to construct FetchXML queries are using advanced find in CRM, you can create query in advanced find, add columns, define sorting and then download FetchXML.

For this Example, I created an Account hierarchy like this,

webapi

Once your FetchXML is ready, you can use this FetchXML in web API to retrieve data from CRM. Here is sample code for how to execute FetXML and get values from FetchXML result set. Please Note that, we need to pass the Top most Account ID of hierarchy (Main/Root Account). If you can't have that Root Account ID with you, I will explain you how to get that using any Account Id you have later in this blog as well.

Now define the FetchXML and web API as below:-

//Get the All Contacts of Root and Child Accounts //we will get using the standard Xrm.Page.context.getClientUrl() function var apiQuery2 = Xrm.Page.context.getClientUrl() + "contacts"; //Construct FetchXML for contacts in this parent account var fetchXML2 = "<fetch version='1.0' mapping='logical' distinct='true'>" + "<entity name='contact'>" + "<attribute name='contactid' />" + "<attribute name='fullname' />" + "<attribute name='emailaddress1' />" + "<order attribute='fullname' descending='false' />" + "<link-entity name='account' from='accountid' to='parentcustomerid' link-type='inner' >" + "<filter type='and'>" + "<condition attribute='accountid' operator='eq-or-under' value=' {" + Your Parent Account ID + "}' />" + "<condition attribute='accountid' operator='not-null' />" + "</filter>" + "</link-entity>" + "</entity>" + "</fetch>"; //With our request path string built, we can perform a standard XmlHttpRequest to retrieve the data from the web service and work with the results however we wish. var encodedFetchXML2 = encodeURIComponent(fetchXML2); var req = new XMLHttpRequest(); req.open("GET", apiQuery2 + "?fetchXml=" + encodedFetchXML2, false); req.setRequestHeader("OData-MaxVersion", "4.0"); req.setRequestHeader("OData-Version", "4.0"); req.setRequestHeader("Accept", "application/json"); req.onreadystatechange = function () { if (this.readyState === 4) { req.onreadystatechange = null; if (this.status === 200) { var results = JSON.parse(this.responseText); // get all contacts of the parent account var _finalContacts = results.value; for (var i = 0; i < _finalContacts.length; i++) { str += '<tr><td>' + _finalContacts[i].fullname + '</td></tr>'; } $('#myTableData').append(str); } } }; req.send();

So execution of above code will show you output as I shown in above Image. It will list down all Contacts we have in relation of all Accounts of that hierarchy.

If you want to test your fetch xml before development. You can use "FetchXML Tester" tool from XrmToolBox, you can simply put your query there and click on Execute.

webapi

Now, you click on Response button and it will show something like this to get all contact records of all accounts in hierarchy of the Main Account you passed.

webapi

How to get Root Account using FetchXML by using any Account of that Hierarchy ?

Now many of you will have a question, how you can get the Root Account ID if the Account Hierarchy. Well with FetchXML and Web API it's pretty easy to get. If you have any Account Id of that Hierarchy then just use this in below FetchXML query.

// FetchXML for Get the Main (Root) Parent Account of the current Account in Nth Level <fetch distinct="false" no-lock="false" mapping="logical" > <entity name="account" > <attribute name="name" /> <attribute name="accountid" /> <attribute name="accountid" rowaggregate="CountChildren" alias="AccountChildren" /> <filter type="and" > <condition attribute="accountid" operator="eq-or-above" value="{Your Current Account ID}" /> <condition attribute="parentaccountid" operator="null" /> </filter> </entity> </fetch>

You will get below output after executing this query. You can even integrate this on our code with same way like I did for contacts above.

webapi

 
  • img
  • img
  • img
  • img
  • img
  • img
  • img
  • img
  • img
  • img
  • img
  • img
  • img
  • img
  • img
  • img
  • img