Deep queries for subgrids

“Please note that the methods described here are not documented in the SDK and therefore are not officially supported by the CRM product team. This works in the context used in the Wealth Management solution and may work in other scenarios, but is not a pattern that
is guaranteed to work in other scenarios.”

In This Post:
• Problem statement
• Sample definition
• Adding the customizations
• Modifications of the Customizations.xml
• Conclusion
• Further reading

Problem statement

Currently, Microsoft Dynamics CRM 2011 allows filtered views in sub-grids for the entities directly related to the opened entity.
In Microsoft Dynamics for Wealth Management Solution we enhanced this functionality and I will explain in this blog the enhanced feature that is now available in Update Rollup.

Note: These steps require that you are familiar with Fetch XML query language and you are comfortable modifying the solution’s Customizations.xml file.

Sample definition

To explain the new feature lets start by defining the custom entities hierarchy. We have a Legal Account which has a 1:N relationship to Portfolio that has a 1:N relationship to Holding. This feature works for deeper hierarchies as well, but for simplicity we will work with this three entities hierarchy.

Prior to UR7 we could not have a sub-grid on the Legal Account form showing related Holdings. Sub-grids only supported showing related entities that were 1 level deep, such as the Portfolio entity.

Adding the customizations

In UR7 we added additional functionality to support sub-grids that could display related records more than 1 level deep.

To use this new feature, I will describe below the steps necessary to enable it. I will use the same object model above. Our goal is to add a sub-grid on the Legal Account form to display related Holdings.

We start by opening our solution and navigating to the Holding Entity. Here we will create a new View. We will modify this view later using Fetch XML to enable seeing related Holdings in the Legal Account form. For the purpose of this exercise I named the view “HoldingsRelatedToLegalAccount”. We click OK after entering the name and click Save & Close on the new window.

We navigate now to the Legal Account entity and we open the Legal Account form. We click Insert on the Ribbon and choose Sub-Grid.

We give a Name and a Label,and, most importantly we choose HoldingsRelatedToLegalAccount as the default View.  Also, select “All Record Types” for the Records dropdown and “Holdings” for the Entity dropdown. In other words we will see all the Holdings in the system regardless of whether they are related to the currently opened Legal Account or not. We will modfy this via Fetch XML later so that only related Holdings will be shown.

We click OK, Save&Close on the form and close the Solution window.

We are ready right now to Publish All Customizations and Export the solution as an unmanaged one.


Modifications of the Customizations.xml

Extract the Cusomizations.xml from the zip and open the file in your favorite XML Editor.
Search for the new sub-grid name (in our example the name is Related_Holdings).
Note that the <RelationshipName> entry is empty. This means that we show all the entities in the system, not only the related entities. We will change this by adding the relationship name between the current entity (in our case Legal Account) and the immediately related entity down the hierarchy (in our case Portfolio). The name can be obtained by looking at the Relationships (1:N, N:1) of the Legal Account or Portfolio entity in the solution window.

By adding this relationship we ensure that we will filter the results by showing only the Holdings related to the currently opened Legal Account. However this is necessary but not sufficient.
<control id=”Related_Holdings” classid=”{E7A81278-8635-4d9e-8D4D-59480B391C5B}”>
<parameters>
<ViewId>{B7FAD5B3-2906-415D-9279-E94CB879A4C7}</ViewId>
<IsUserView>false</IsUserView>
<RelationshipName>msdyn_account_msdyn_portfolio_Legalaccount</RelationshipName>
<TargetEntityType>msdyn_holding</TargetEntityType>
<AutoExpand>Fixed</AutoExpand>
<EnableQuickFind>false</EnableQuickFind>
<EnableViewPicker>false</EnableViewPicker>
<ViewIds>{B7FAD5B3-2906-415D-9279-E94CB879A4C7}</ViewIds>
…………

The next step is to search for the View definition by searching the id (in this example ‘B7FAD5B3-2906-415D-9279-E94CB879A4C7’).

Here we need to enhance the FetchXML. Remember that the current FetchXML for our View is retrieving all the Holdings in the system.

We need to add a link-entity statement between the current Holding and the parent Portfolio by using the primary key/foreign key link.

<fetchxml>
<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical”>
<entity name=”msdyn_holding”>
<attribute name=”msdyn_quantity” />
<attribute name=”msdyn_asset” />
<attribute name=”msdyn_totalvalue” />
<attribute name=”msdyn_holdingid” />
<order attribute=”msdyn_asset” descending=”false” />
<link-entity name=”msdyn_portfolio” from=”msdyn_portfolioid”
to=”msdyn_portfolioid”></link-entity>
</entity>
</fetch>

If we had a deeper hierarchy we would need to add all link entities up the hierarchy except the last one. For example, if a Holding was related to Assets, and we wanted to show all the related Assets for a Holding, then in the FetchXML we would need to add the link entity from Asset to Holding, and an inner link-entity from Holding to Portfolio.
<fetchxml>
<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical”>
<entity name=”msdyn_asset”>

<link-entity name=”msdyn_portfolio” from=”msdyn_portfolioid”
to=”msdyn_portfolioid”>
<link-entity name=”msdyn_holding” from=”msdyn_holdingid”
to=”msdyn_holdingid”>
</link-entity>
</link-entity>

</fetch>

Link-entity entries in Fetch XML will be transformed into SQL Join statements. To be able to filter all the Holdings related to the currently opened Legal Account we need to build these Join/Link-Entity statements by walking up the relationship hierarchy.

The last Join/Link-Entity (in our case from Portfolio to Legal Account) we will be added by the Sub-Grid engine. The engine does this by inspecting the <RelationshipName> (i.e. msdyn_account_msdyn_portfolio_Legalaccount). It uses the relationship name to populate the last Link-Entity statement on the fly (e.g. from Portfolio to the currently opened Legal Account). This is the reason we need all the other Link-Entity statements up the hierarchy, except the last one.

Once this is done, we need to repack the Customizations.xml into the zip file and reimport the solution into the system and republish the customizations.

By opening a Legal Account, we notice that we see now only the Holdings related to the currently opened Legal Account.

Conclusion

In this blog post we have shown that having Sub-Grids that show related entities that are multiple levels down the relationship hierarchy is possible with the Dynamics CRM 2011 UR7 release.