Polymorphic lookups in Dataverse: the hidden cost most tutorials skip

The Customer lookup on a Sales Order table in Dataverse can point at either an Account row or a Contact row. One field, two possible target tables, resolved at runtime. The pattern is called a polymorphic lookup, and it looks elegant on a form designer - the user picks an account or a contact without caring which.

The elegance has a cost that tutorials routinely leave out. After shipping polymorphic lookups on three projects and unwinding them on one, here is our working rule for when they earn their keep.

What polymorphic lookups buy you

Two flavors in Dataverse:

• Built-in polymorphic columns on system tables. The customerid column on Account/Contact lookups, the regardingobjectid column on activity tables pointing at any primary entity. You cannot create more of these.
• Polymorphic lookups on custom tables via many-to-one relationships to multiple targets. Added through the maker portal or solution XML.

The appeal is obvious: one column instead of two. A form with "who is this order for?" that can be either a B2B account or a B2C contact reads more naturally than "who is this order for (if account)" and "who is this order for (if contact)" as two separate fields.

The three follow-on problems

Problem 1: reporting and joining are messy.

SQL people know the pattern: one column that references two tables means every join needs a CASE or UNION. The Dataverse equivalent is FetchXML or the Web API, which do not expose the relationship with the same syntactic ease as a simple lookup to a single target.

Example: "Show me all sales orders with the customer name, regardless of whether the customer is an account or a contact." With a polymorphic lookup, the FetchXML needs two outer joins:

Then you coalesce acct.name and ctct.fullname in the consumer. Every report, every view, every integration picks this up. Two non-polymorphic lookups would give you two simple joins, one of which is always null - clearer to reason about, less clever.

Problem 2: query cost and indexing.

Dataverse creates an index on lookup columns, but polymorphic lookups cover multiple target tables. A filter like customerid eq 'specific-guid' has to resolve the GUID against multiple index targets. The performance cost is small on lookups with two targets; it scales with the number of targets.

More subtly, views that filter by "customer name" become expensive because they implicitly join both target tables. A large view with a polymorphic-lookup-based filter is where performance degrades first as the system grows.

Problem 3: integration pain.

Upserts from an external system into a polymorphic lookup require the integration to know which target table the value belongs to. The Web API syntax:

versus

The integration has to branch on "is this an account or a contact" for every write. A non-polymorphic lookup takes one fixed binding name. On a high-volume sync, this branching is 20% of the integration code.

When polymorphic lookups are genuinely the right tool

Two scenarios make us reach for them:

• You genuinely need to store heterogeneous references. An activity-like table (calls, emails, meetings) that logically relates to "whatever is being discussed" benefits from a polymorphic link. The alternative - a separate lookup column per possible target - explodes the schema.
• The UI payoff outweighs the reporting cost. A form designer needs one field, not four, and users do not care about the underlying model. If reporting against this column is rare (audit log use case, not a weekly exec report), the cost is modest.

Patterns we use instead

When the requirement is "customer is sometimes B2B, sometimes B2C, but in practice each order has exactly one customer," we pick one of:

Pattern A: single target table (Contact with a Type field).

Make Contact the one target table. Add a acme_contact_type choice column: Individual or Company Representative. For B2B orders, create a contact row that represents the company's billing contact (linked to the account via another lookup). Every sales order lookup points at Contact; every report groups by contact's account if the contact has one.

Works when the B2B orders almost always go through an identifiable contact person anyway.

Pattern B: two non-polymorphic lookups with an exclusion rule.

Two lookups on the sales order: acme_customer_account and acme_customer_contact. A Business Rule enforces "exactly one of these must be filled." Reports join on whichever is non-null.

Works when you want the clarity of strong-typed references at the cost of form real estate.

Pattern C: a Customer Party table that accounts and contacts both reference up into.

Create a acme_party table. Every account automatically creates a party row; every contact the same. The sales order's customer lookup points at acme_party, which has a simple owner reference back to either account or contact.

Heaviest pattern, most data-warehouse-like, best for projects where you will want to unify reporting across customer types anyway.

The decision we ran on the last project

A retail client with both corporate contracts (B2B) and direct customers (B2C). Initial instinct was polymorphic customer lookup on sales order.

We caught ourselves and ran three questions:

1. Will exec reporting need to compare B2B and B2C performance? (Yes - monthly board review.)
2. Will integrations distinguish account customers from contact customers? (Yes - different billing systems.)
3. Does the UI benefit outweigh the query complexity? (No - users already work in contact-specific views for B2C and account-specific views for B2B.)

Three no-or-yes-with-cost answers. We went with Pattern B: two non-polymorphic lookups + Business Rule. Reports are straightforward joins. Integrations target one of two simple bindings. Users pick "Business customer" or "Individual customer" on form load, and the relevant lookup appears.

If we had chosen polymorphic, the first board report would have taken a week of FetchXML wrestling. Two years in, we are still glad we did not.

Rule of thumb

If the polymorphic lookup is truly the point (activity-like tables, audit references, rare reporting use), use it. If it is a convenience over two semantically different relationships, the convenience will cost you more than two cleaner columns ever would.