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
Install latest version of Distributed Workflow Activity plugin (Preferably “Managed”) from CodePlex.
Create a new Lookup Field on Opportunity pointing to Group Account:
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:
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:
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)
- 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.
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.