One-to-one relationships and forms within forms

There’s no such thing as 1:1 relationship in Dataverse, and hence your Power Apps Model-driven apps or Dynamics 365 Customer Engagement apps can’t directly have such a data model. Only 1:N (one-to-many), N:1 (many-to-one) and N:N (many-to-many) relationships are available between tables, be it standard or custom ones.

In practice, even the N:N relationship doesn’t actually exist in the database. While the Dataverse table configuration UI allows you to create this relationship type, it actually consists of a hidden intersect table and two 1:N / N:1 relationships that connect the actual tables together (see Dataverse table relationships documentation). Seasoned XRM professionals may even discourage the use of native N:N relationships, as you lose some control and visibility to the relationship due to its hidden nature.

Just because it’s not available in the platform, doesn’t mean there aren’t many real life business scenarios where a requirement to have exactly one record per a record in another table. (OK, “rows” in the latest Dataverse terminology, but I prefer the business process lingo where “record” still is more appropriate.) Also, like with N:N relationships, just because it’s not directly possible to create one, doesn’t mean we couldn’t build the required functionality by using the no-code tools in Power Platform.

In this blog post I’ll demonstrate not only how to create a 1:1 relationship but also how you can offer a pretty nice user experience for working with related records – thanks to the new Form Component Control feature. I’ve covered the feature details in an earlier blog post (“Relational data on Model-driven forms, part 2: Form Component Control”) so please refer to that for more info.

Why would we need 1:1 relationships?

From a theoretical data modelling perspective, you probably shouldn’t be splitting data into multiple tables if there is only a single match expected from either side. On a practical level there can be reasons why it makes sense to not cram everything into a single table, though.

A common source of such requirements are the restrictions of access rights to data. Let’s say that the contact information of a person needs to be widely available to users of the application for various purposes (billing, marketing etc.). However, this contact also happens to be a patient, with details about his or her medical profile being recorded into the same system. Only the doctors should have access to this data. A single contact will match a single patient record (or none, if it has been created for other purposes). If these are in two separate tables, granting access rights can be easily achieved via standard Dataverse security roles: everyone sees the contact table data, but only doctors see the patient details.

“Couldn’t we just use field level security to hide the confidential stuff?” We could, but you have to evaluate whether the approach will really scale to how the system will be used. You see, in addition to security we’ll also need to consider if we’re overloading a single table with too much data. There are hard limits of the maximum number of columns that SQL Server supports for a single table. Thanks to the value-add provided by Dataverse, adding one column into the data model can create many columns in SQL. This means you don’t have anywhere near the 1024 columns per table at your disposal. Also, if you’re working with a standard CDM entity like contact, there will already be close to 300 attributes taking up space before you extend the data model for your specific needs.

I was recently working with a customer that is planning to use Dynamics 365 Customer Service for managing all their service requests in every department they have. This will mean that tens of different types of services will be creating case records into the system. The amount of service specific information that must be available to be captured on case records is easily hundreds, if not thousands of fields. Adding all of these to the case (incident) table wouldn’t be feasible, so instead the solution architecture was designed to incorporate “service detail” tables specific to each service. Each case will have one (or zero) of these records, so it’s a 1:1 relationship between the standard case table and these custom service detail tables.

Establishing 1:1 in the data model

In the scope of our example, the data model will consist of these main tables:

• Service 1, Service 2, …, Service N: parental record under which the cases will be created. Think of these as service contracts that a contact person can have for one or more services.
• Case: the standard Dataverse / Dynamics 365 table, with lookups to all of the aforementioned Service tables. No other service specific data is stored here.
• Service 1 Detail, Service 2 Detail, …, Service N Detail: service specific information that should be found from under each Case, depending on which service it applies to.

Just like the N:N relationships in Dataverse consist of two 1:N’s, the same applies to our manually created 1:1 relationship. Only this time we’re not going to need an intersect table, rather we’ll just link the two records together via the relationships like this:

The Case record will be parental to the Service Detail record, but at the same time the Service Detail will be the Case’s parent. These will appear just as two custom relationships under our table:

Next, we’ll want to ensure that there is always one and only one Service Detail record for a case – IF the case is related to the delivery of the specific Service. Furthermore, we’ll want to get the Service Detail created automatically immediately after case creation, so that users can start entering data on it.

The real-time requirement rules out Power Automate that is asynchronous by nature, so we’ll use the classic XRM workflow engine instead. There will be two levels in the automation:

1. When a Case record is created, check which of the many Services it is linked to and create a record in the corresponding Service Detail table (establish 1:N relationship).
2. When a Service Detail record is created, update its parent Case with a reference that sets the Service Detail to also be the parent of that Case (establish N:1 relationship).

Workflow 1 looks like this:

It will then in turn trigger workflow 2:

Notice that we have a check in place that stops the creation of a Service 1 Detail record if one already exists for the Case. If the lookup to Service 1 Detail is empty, we put the reference to our newly created record there and establish the 1:1 relationship.

Working with 1:1 data in the user interface level

This is where the Form Component Control comes in handy. In short, the control is meant to allow both the display and inline editing of a parental record’s form, embedded inside another form. An example of the standard data model use cases would be to show the fields of a the customer contact on a Case form and allow the service representatives to update them without having to open the actual Contact form.

It works in our 1:1 scenario, whereby we can edit the Service Details fields directly on Case form. The reason is that not only is the Service Detail a child record of the Case, it is also the parent – thanks to what we’ve just built above.

You’ll find the explanation of how to use Form Component Controls in my earlier blog post. For now you need to do the configuration in the legacy Solution Explorer side, by editing the form and setting one of the lookup fields to be rendered as Form Component Control:

Now when we create a new Case record and have the Service 1 lookup value populated, after the first save the user can immediately continue to fill the Service 1 Detail values right within the same Case form:

The beauty here is that for the user who’s working with a Case record, they won’t need to know there are two different Dataverse tables used for storing the data. Both the Case record details, Service 1 Details and even the Contact record details are all editable on the single screen. The world looks flat, regardless of our data model with several relationships configured behind the scenes.

Conclusions

Dataverse offers you plenty of configuration tools to get creative with both the data model and the UI in Model-driven Power Apps. While the standard hierarchical structure of parent-child records and table (entity) specific forms is the most common pattern, there are alternatives that may be useful when faced with more complex business requirements.

Dividing the business data into multiple tables with 1:1 relationship may sometimes be perfectly justified, to accomodate the security and data storage requirements. The user interace of Model-driven apps today offers great tools like the Main Form Dialog and Form Component Control to simplify working with proecsses that span across different tables in the underlying database.

If you’d like to see Microsoft implement a native one-to-one feature for Dataverse, please vote on this idea.