D365 – Display grandchildren in Subgrid without coding

Like most of us, on projects I sometimes bump into the limitations of default Dynamics 365 (CRM) functionality. Typically such limitations are overcome by bringing JavaScript and/or Custom FetchXML into the mix. However, this increases complexity and maintenance costs.

If you ever felt the need to display grandchild records in a Subgrid View, you must recognize this. In this post I will explain a non-coding alternative to commonly used solution directions as JavaScript and/or FetchXML.

Example

You have a set of Accounts bundled into an entity called Group Account. Each Account in its turn has many Opportunities. The database looks as follows:

Imagine you would like to know which Opportunities are relevant for a given Group Account. To retrieve this information, you must open each and every Account within that Group Account. Quite a hassle isn’t it?

If we could create a relationship opportunity n:1 groupaccount, whilst automatically keeping it in sync with opportunity n:1 account n:1 groupaccount, we could actually show a subgrid on Group Account directly:

Here is how

Step 1
Install latest version of Distributed Workflow Activity plugin (Preferably “Managed”) from CodePlex.

Step 2
Create a new Lookup Field on Opportunity pointing to Group Account:

Step 3
Create a (child) Workflow on Opportunity to clone value from `opportunity`.`parentaccountid` to `opportunityproduct`.`new_parentaccountid`:

Trigger the Workflow whenever “Record is created” and on “Record fields change“. The latter whenever Field “Account” changes. Also enable it to work “As a child process“.

Populate “Group Account” Lookup Field with the value from Lookup Field `account`.`new_groupaccountid`:

Clear “Group Account” Field whenever “Account” Lookup Field does not contain data:

Step 4
Create Workflow on “opportunity” to push value from ‘opportunity’.’parentaccountid’ to ‘opportunityproduct’.’new_parentaccountid’ using the “Distributed Workflow Activity” plugin:

Set properties for “AG Utilities:Distribute One to Many” workflow:

Result

Fully working solution on Group Account, without functional limitations. On any level (Group Account, Account and Opportunity) you can create records and define new relationships, without this solution to break down:

Alternative Solution Directions

• Build a custom FetchXML SSRS Report (= destroying User Experience)
• Overrule a Subgrid’s FetchXML using (unsupported) JavaScript

Best Practices

• Hide the redundant Group Account Lookup Field on Opportunity to prevent Users from making manual changes. If Data Import privileges are enabled; remember that Users are still able to alter this Field. An additional Workflow to update the parent record could solve this.
• Run the parent workflow in background, whilst running the child workflow in real-time. This usually is the best combination in regard of system performance and sync process reliability.
• Keep a close eye on system performance. Omit this solution in situations where large amounts of (transactional) data is supposed to be synced frequently. I personally use this solution mostly for Master Data that hardly changes, or in situations which are not likely to trigger more than a few hundred background processes per day.

Future compatibility

In case Dynamics 365 will offer multi-level Subgrid functionality in the future, by then at any given moment you can decide to delete the 2 workflows and redundant relationship without affecting User Experience, Training material or Documentation.