In my first post in this series I discussed how virtual entities and the link-entity node in FetchXML don’t mix well. In this post I’m diving deeper into virtual entities and how they can be related with other virtual, as well as non-virtual, entities.
First, let’s deal with the easy case of N:N relationships. You can create an N:N relationship between a virtual entity and a non-virtual entity. However, you cannot create an N:N relationship between two virtual entities. If you try, you’ll get an error message when you try to save the relationship. The error message is pretty descriptive: An N:N relationship between virtual entities is not supported. To be honest, I’m not sure why this limitation exists – I would have thought the joining table required for N:N relationships could have just as easily stored the IDs from two virtual entities but alas, at this point, it doesn’t work.
You can create 1:N relationships (and N:1, since they are the same thing, just looking at it from the other entity) between two virtual entities, as well as virtual entities and non-virtual entities, and vice versa. However, it’s important to remember how 1:N relationships are represented in the database in order to understand if it is feasible to do so.
For an 1:N relationship to exist, a lookup field is required on the entity on the ‘N’ side. This field stores the ID of the ‘1’ record. The requirement for a lookup field holds true for virtual entities as well. So, if you want to create a relationship of 1 virtual entity to N non-virtual entities, it’s pretty straight-forward because you can easily add a field to the non-virtual entity (this is done automatically when you create the relationship). This is what makes it simple to add notes, activities, etc to virtual entities; the lookup field is on the non-virtual entity so it is easily created in Dynamics 365, and stores the ID of the virtual entity.
If you want the virtual entity to be on the ‘N’ side of the relationship, then it needs to have the lookup field that contains the ID of the related record. This is true whether the relationship points to another virtual entity, or a non-virtual entity.
Now, it’s probably much more common for an existing database to have links between different tables within that database. So I think that having 1:N relationships between two virtual entities will be quite common. The field that contains the ID of the related record would already exist, and it would just be a case of setting up the relationship in the virtual entity definition and pointing it to the appropriate fields.
However, having a 1 non-virtual entity to N virtual entities relationship takes a bit more work. This requires your external data source to have the ID of Dynamics 365 records. This is not an impossible task, but I think most of the time this won’t already be the case, so some work might need to be done to synchronize that ID to the other system. The good news is, if you can get that ID over to the other system, you can create the relationship.
Now that I’ve laid the foundation of what virtual entities are, and how they can be related to other virtual and non-virtual entities, my next post will cover how virtual entities work with Microsoft Dynamics 365 Portals.