The problem
Model-Driven App and Dataverse are great are modeling relationships. Unfortunately, it’s not always easy to build an application with an intuitive navigation. This is especially true when dealing with N:N relationships.
One scenario I encounter frequently is the need to group records by a relationship’s column. Let’s consider the following model:
As you can see this is a pretty straightforward model. A deal can be done in partnership with one or more partners and a partner can be part of multiple deals. We also need to qualify the relationship by adding some metadata. In this example the percentage of ownership must be specified on the relationship to specify each partner’s share in the deal.
A Dataverse N:N relationship cannot be used since it doesn’t allow the addition of custom attributes. The ownership percentage couldn’t be specified. The solution is to create an associative table to maintain the N:N relationship between the DEAL and PARTNER tables. This is why we have a DEALPARTNER table in the model.
In practice it means a DEALPARTNER table is created with one lookup pointing to the DEAL table and one lookup pointing to the PARTNER table.
Note: DEAL, PARTNER and DEALPARTNER are examples of custom tables. In a real-world scenario you would have prefixes in schema names.
Let’s see how such a model would look like once implemented in a Model-Driven App:
At first sight this application seems to be just fine. We’ll all agree it’s a very basic example, but it works. At least until someone ask to see the list of active deals grouped by partner. If you try to edit the partners’ view you won’t be able to add a column related to the DEALPARTNER table. That’s because a PARTNER record can be related to multiple DEALPARTNER records. The same will apply when editing the deals’ view.
Solution #1 – using an associative table’s view
The easiest solution consists of using the associative table to create a view that displays the partners and deals.
There is nothing in particular here. A simple view of the DEALPARTNER table displaying the two lookup fields does the trick. The view is sorted on the Partner column to group the deals by partner. It’s also possible to display fields from the deal directly in this view.
Pros
- Export to Excel is supported.
- Users can easily filter the records on both related tables.
Cons
- The DEALPARTNER table must be displayed in the app’s navigation. For most users opening the associative table is not intuitive since they probably don’t understand the underlying concept. From a user’s perspective seeing the deals grouped by partners should be a view of the DEAL table.
- Technically the records are not grouped in the view. There is no way to expand / collapse a group as you could in a view that would supports the grouping (the editable grid supports grouping but as we’ll see in the next section there is a better way to use the editable grid).
Solution #2 – the editable grid
A great way of solving this issue is to use the subgrid option of the Editable grid control. When this option is configured it’s possible to display a list of related records for each record in the editable grid.
Here is the result:
As you can see it is now very easy to browse the list of partners and display the deals related to each partner. Of course, you can use any view from the related table to display the relevant columns depending on your context (in this example you’ll note the Partner column which is redondant).
Editable grid configuration
All the following steps must be completed in classic mode. Open your solution and select the table on which you want to enable the editable grid (1), then select the Controls tab (2) and click on Add Control… (3).
Select Editable grid in the controls list.
Select Web and Tablet to display the editable grid by default for all the views. Then, configure the Subgrid option by selecting the table used to maintain the N:N relationship (DEALPARTNER in our model). You also must select the view used to display the related records.
Finally, in the Subgrid parent id option you must select the lookup field used to link the related record to the parent one. In our model that’s the Partner field of the DEALPARTNER table.
Don’t forget to save and publish your changes. You can now edit your Model-Driven App’s navigation to add a link to the right table (Partner in this example) and test the editable grid.
Pros
- Intuitive (users don’t have to navigate to the associative table)
- Groups can be expanded / collapsed.
Cons
- Displaying related records is only available in the Editable grid control. In some cases, the editable grid doesn’t make sense (see the limitations of the editable grid).
- The Editable grid cannot be configured at the view level unlike other controls. You have to make it the default control at the table level, then override that setting in each view where other controls must be displayed.
- Only one record can be expanded at the same time. You cannot display the related records of multiple records at the same time.
- The related records are not included in an Excel export.
Solution #3 – The subgrid
Most of the time users will ask for a view displaying the records grouped by a relationship’s column but this is more a solution than a requirement. In my example translating it to a requirement would be something like this:
As a user I need to browse all the partners and their related deals in order to conduct a due diligence.
One solution to fulfill that requirement would be to add a subgrid pointing the DEAL table in the PARTNER form. Users would be able to display the deals related to each partner. Browsing across the partners can easily be done thanks to the Record set pane.
Although this solution doesn’t display the partners and related deals in the same view, I find it very interesting since it’s very close to the editable grid solution in term of user experience.
With the editable grid the user has to expand each partner to display the related deals. With the subgrid it’s the exact same number of clicks once the Record set pane is opened. Of course, that solution works if you have a simple form with few fields. Otherwise, the loading times can make the navigation tedious.
I voluntary added this solution to demonstrate that taking a step back and analyzing the “why” of a request is always important when designing a solution. Also, I think a lot of requirements can be answered by correctly using the product as you can see with that Record set pane example.
Pros
- You may already have everything in place in your app (it’s very common to add subgrids in forms to display the related records).
Cons
- All the fields of the main form are displayed when browsing the PARTNER table which can cause confusion. In comparison, you can select exactly the fields you want when using a view (solutions #1 and #2).
- Performance can be an issue depending on the form (the more fields the slower).
- The Record set pane only displays the records of the current page. Once the user reaches the end of the list, he must go back to the list and load the next page.
- Records from both tables (PARTNER and DEAL in my example) cannot be exported in Excel.
Conclusion
One strength of Model-Driven App / Dynamics 365 is how easy it is to model complex relationships. The fact the UI is built based on the data model and doesn’t require any code is also an important selling point of the platform, but it comes at a price. Thus, the user experience is not always very intuitive.
As described in this post there is no perfect solution when it comes to N:N relationships but I think the following points shouldn’t be understimated when developing a Model-Driven App:
- Reuse the same patterns as much as possible.
- For example, choose one pattern when displaying N:N relationships and stick with it across an application.
- Ideally, list the available patterns and explain when each pattern must be used. That documentation should be used by the developers during the development.
- Train your users.
- A lot can be done with the native features of the platform but that requires training.
