Advanced Dataverse Data Modeling

1. Polymorphic Lookups: One Column, Many Tables

Imagine a scenario where a single record needs to relate to one of several different types of tables. For example, a "Payment" record could be for a "Course," a "Product," or a "Consulting Service."

Your experience with Dynamics 365 might bring to mind the Customer lookup, which can point to either an Account or a Contact. This is a built-in polymorphic lookup. The good news is that we can now create our own custom ones.

The Problem with Standard Lookups

Before custom polymorphic lookups, you had a few less-than-ideal workarounds:

• Create multiple lookup columns: One for "Course," one for "Product," etc., and use business rules to show/hide them. This clutters the data model and makes reporting difficult.
• Use the Activity table: You could create a custom activity type, but this mixes your business data with standard activities like emails and phone calls, which can be confusing for users and create security complications.

Polymorphic lookups solve this by allowing a single lookup column to reference records from a defined set of multiple tables.

Implementing a Polymorphic Lookup

While you can use tools like the XRM Toolbox to create these lookups, as a developer, you need to be familiar with the programmatic approach using the Dataverse Web API. This is done via a custom action called CreatePolymorphicLookupAttribute.

As you saw in the documentation, the core of the creation process is a POST request to the CreatePolymorphicLookupAttribute endpoint. The JSON payload consists of two main parts:

1. OneToManyRelationships: An array where you define each 1:N relationship that will be part of this polymorphic lookup.
2. Lookup: The metadata for the new lookup column itself, such as its SchemaName and DisplayName.

Given your background with C# and the Web API, you can see how this could be wrapped in a helper method to create complex columns as part of an automated deployment or data migration script.

Using Polymorphic Lookups in Apps

Once created, you can add the lookup to a Model-driven App form just like any other column. The user will see a dropdown to select the table type before selecting the record.

In Canvas Apps, working with polymorphic data requires special functions because the app needs to know which table the lookup is pointing to in order to access its columns. The key functions are:

• IsType(lookup_column, table_name): A boolean function that checks if the record in the lookup column is of a specific table type.
• AsType(lookup_column, table_name): A function that "casts" the record to a specific table type, allowing you to access its columns using dot notation.

2. Hierarchical Relationships: Parent and Child Within the Same Table

Hierarchical relationships are used to model parent-child structures within a single table. This is perfect for scenarios like:

• An organizational chart (Employee reports to another Employee).
• A multi-level product catalog (Category has a parent Category).
• A project with sub-projects (Project has a parent Project).

To achieve this, you create a self-referencing 1:N relationship on the table.

Implementing a Hierarchical Relationship

Setting up a hierarchy is a multi-step process that involves both the modern and, for now, the classic interface.

To summarize the key steps from the video:

1. Create a Self-Referencing 1:N Relationship: On your table (e.g., Account), create a new lookup column that points back to the Account table itself. You might call it Parent Account.
2. Mark the Relationship as Hierarchical: In the relationship's properties, there is a toggle to enable hierarchy.
3. Define Hierarchy Settings (Classic UI): This is the crucial step. You must switch to the classic solution explorer, navigate to your table, find "Hierarchy Settings," and create a new setting. Here, you associate the hierarchy with a Quick View Form that will be used as a template for the visualization cards.
4. Populate Data: Create records and use the new lookup field to set their parent-child relationships.
5. Visualize: In a Model-driven App view, records that are part of a hierarchy will now show a special icon, which you can click to see the visual hierarchy.

Querying Hierarchical Data

Once a hierarchy is defined, you gain access to a powerful set of query operators in FetchXML and QueryExpression that understand the hierarchical structure. This allows you to retrieve records based on their position in the tree.

The key FetchXML operators are:

For example, to get all accounts under 'Account D' from the image above (G, H, I, and J), your FetchXML condition would look like this:

<filter type='and'>
  <condition attribute='accountid' operator='under' value='{ID of Account D}' />
</filter>

Additionally, the rowaggregate='CountChildren' feature is an efficient way to get a count of child records without performing a separate, expensive query.

<filter type='and'>
  <condition attribute='accountid' operator='under' value='{ID of Account A}' />
  <condition attribute='accountid' operator='not-under' value='{ID of Account D}' />
</filter>

Conclusion

You can now design and implement two of the most powerful data structures in Dataverse. These tools are critical for modeling complex business logic that standard relationships cannot handle.

Key Takeaways:

• Polymorphic Lookups are for relating one table to records from multiple different tables. Use them to avoid cluttering your model with multiple lookups when a record can be related to various entity types.
• Hierarchical Relationships are for creating parent-child relationships between records within the same table. Use them for org charts, product categories, or any tree-like structure.
• Implementation Matters: Creating these structures involves specific steps, including using the Web API for polymorphic lookups and navigating the classic UI for hierarchy settings.
• Querying is Key: Both structures come with specialized ways to access the data, using IsType/AsType in Power Fx for polymorphic lookups and hierarchical operators in FetchXML.

In our next lesson, we'll shift our focus from designing the data model to understanding the platform's constraints. We will identify and plan for developer-relevant licensing constraints, such as premium connectors, API limits, and Dataverse capacity, which are critical considerations for any real-world solution architecture.