Like most of us on every project we bump into some of the CRM restrictions and we need to somehow display some records and the only way it seems to be a SSRS report. One way I found it can be accomplished this way – Deep queries for subgrids. But in my case I need it more. Let’s dig in…

I have an OOB hierarchy for the Account: Account – Sub Account – Sub Sub Account, a tree level Account hierarchy. Directly connected to the Account entity with a one-to-many relationship I have an Account Product entity that makes a connection between Account and Product (same with a one-to-many relationship to Product). My dilemma: how to display all the Account Products from the whole hierarchy at the root Account level? Even the related child entities are the same for each hierarchy level (the Account Product) fetchXML doesn’t support multiple joins to the same entity, more than two related entities, nor old fashion sql UNION.

Again the sub grid on the form and changing the fetchXML on the fly, pops up in my mind. What I can’t accomplish with one fetchXML maybe I can make it through a sequence of fetchXMLs with the help of JScript. So I added a sub-grid for the Account Products on the Account form.

For the JScript I figure if I get all the Accounts from the hierarchy starting from the root and down to hierarchy, then I can get all the Account Products related to those accounts and display them in the sub-grid. Good plan. Let’s see the implementation…

First get recursively all the accounts ids and store them aside.

Last step – connect the form onload or tab onchange event to the JScript function. Testing it out.

Notes:

1. Be sure when you set up the sub-grid initially on the form, don’t set it as related records – it will still filter out all the sub accounts records in the end, so set it for all records.
2. If you have related fields (in my case Product fields) displayed on the grid be sure you set the aliases names like in the original view (see my previous blog – Dynamically change sub-grid fetchXML with a small twist).

Then get all the Account Products for all the account ids stored previously and display them in the sub-grid by overwriting the original fetchXML for the sub-grid.

This post was written by George Nastase
George is experienced with B2C and B2B, ERP and CRM systems for more than twelve years’ experience in Web technologies, internet/intranet, multi-tier and layered architecture. He possesses strong knowledge of database design, development and tuning. George also has excellent abilities to plan, organize, prioritize tasks and meet deadlines. He is experienced in drawing up technical documentation and providing users training and support.