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:
- 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).
- 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.
Hi Jukka
It’s really helpful to have the case study you’ve presented in your post.
This is definitely something I’ll consider using.
I’d be interested in your thoughts about possible limitations of this approach.
The ones that occur to me are mainly in the area of presenting information in views. If there is more than one service, it wouldn’t be possible to have an out-of-the-box view that combines across the services. Likewise, it wouldn’t be possible to use service fields (columns) in a marketing list.
Are there any other considerations?
Best wishes, Alex
Alex, there certainly is a lot to configure and validate when choosing an approach like this. The security model implications are one interesting area. As the 1:1 model creates a lookup field on the parent table, in some places that will need to be hidden via Field Level Security. Form switching logic for service specific case forms will also need to be developed, if a single user group will work across multiple services.
Separating the customer relationship data specific to a particular service into their own tables does mean that there’s no one list to view it all from. However, in this particular scenario the needs for cross-service reporting in the organization can be covered by using the common tables for contacts, cases and activities. Anything that dives deeper into a value that’s not used across all services isn’t likely relevant to be reported on that high level anyway.
In general, the standard CRM data model that assumes the account (in B2B) to represent both A) the customer relationship, B) the legal entity, and C) the office location – that in itself is often a source of many practical challenges when using the system. Taking these into a more granular data model where the objects are in dedicated tables, and in some scenarios several tables (like with the service specific data model) – that will solve some problems and introduce other limitations.
I’d say that as long as you use the default tables for their most logical purposes (physical person / identity in contact table, support tickets in case table) and augment them with new tables that cover concepts missing from the standard setup, you’re not “misusing” the system. Trying to keep everything within the standard tables can introduce problems on its own, like with the max number of fields limitation mentioned in the post.
Thank you for this tutorial! It helped me figure out how to do something similar in my business case. Appreciate it! 🙂
Hey, thank you for me as well. I don’t understand why MS doesn’t add 1-1 relationship support to CRM (perhaps with some limitations). I am trying this out now.
I feel like your post was targeted at fairly experienced devs. I’m not sure it presented enough detail to hand hold the average person through this process. Perhaps that was intentional?
1:1 relationships are a bit dangerous from a data modeling perspective, so I kind of understand why MS doesn’t want to present them in the UI for all citizen developers creating apps on top of Dataverse. Remember to go and vote for feature suggestions on Power Apps Ideas site if you’d like to see it added into the product.
Yes, this concept of creating manual 1:1 relationships definitely requires good understanding of how Dataverse and Model-driven apps work. My blog has pretty much always been targeted more at the advanced app maker audience, to introduce tips & tricks I’ve learned along the way when trying to bend the platform to meet various business requirements – without resorting to custom code. Traditionally Dynamics CRM used to be an area where the persons configuring such solutions were quite often full-time professionals working on the technology. Now as the underlying tech has been expanded to cover also citizen developer apps via Power Platform, there certainly is a wider audience of users with varying backgrounds using the same tools.
Luckily this expansion has also grown the community content around Power Platform. People like Matthew Devaney are writing awesome “how to” articles that are easy for also new app makers to follow. Now, such “how to” blogs tend to focus on the Canvas app side, which is understandably where most app makers start their learning journey (simply because all Office users have access to it), so Dataverse / Model-driven still remains a bit more complex area to approach. It would certainly present opportunities for experienced CRM pros to revisit the basics of the platform and educate newer app makers on them. This recent article on 11 Tips & Tricks In Model-driven Power Apps from Megan Walker is a wonderful example of such content that doesn’t focus on any shiny new feature but rather it highlights UI/UX aspects that everyone can quickly make use of.