Oh Look! Tables!

Lets start at the beginning – lookup fields you probably know (a field (column) in one table that looks up to another table like a field on a Contact record that allows you to select an Account record as the ‘Employer’ for example) but what is this fancy ‘polymorphic’ thing? WELL. You’re in for a treat.

Polymorphism Explained

A polymorphic lookup field is a lookup field that allows you to select a record from more than one table therefore ‘morphing’ into many (poly!) tables. What can I say, it sounds fancier than ‘Multi-Table Lookup’ so there.

These fields exist out of the box in many places like the Appointment form for example – if you take a look at the ‘Optional Attendees’ you can see it is looking at 5 different types of records (i.e. 5 different tables). Congrats, you polymorphed!

How to Create One

You will be forgiven for thinking you can’t create such fields as you can’t create one through the maker portal (yet!) – only a standard (link to a single table) lookup or a Customer lookup giving you the ability to link to the Contact/Account tables exists:

Buuuuut…. you can create (and manage) one through the wonderful XrmToolbox using the Polymorphic Lookup Creator tool created by Tanguy Touzard! Power to the C#!

My guess is that in one of the future releases this functionality will pop up as polymorphic lookups are extremely useful – but until that day, proceed with caution. As this is a third party application, this isn’t fully/officially/100% supported or incorporated into out of the box functionality as you might expect.

What you need to do

The Polymorphic Lookup Creator tool is very easy to use – just remember that the ‘greyed out’ fields are actually editable and you’ll be fine 🙂 Below I am creating a new field on the Contact called ‘Affected By’ where I want to select a record from either the Opportunity, Quote or Product table.

On the left I have selected the solution I want the field created in, which table it should be created in, what I want to do (create a new field), the display name, schema name and finally I can tick the tables I want made available for it. By selecting a table from the list you can also see the details of the relationship to be created on the right and amend this as needed.

Once you are ready, click on the ‘Create Polymorphic Lookup’ button on the top right and, as Tanguy would say, Voila! Once the field has been created you will get a confirmation message and you can then see it in the maker portal within the solution and table you selected:

But Beware…

There are a few things that will not be what they seem – consider each of these carefully:

1. The Maker Portal will lie to you

If you look at the field within the solution in the maker portal, even though it is polymorphic it will look like a normal lookup field linked to only the first table so you will have no idea or indication it is indeed a polymorphic one (so document your polymorphic lookups, make sure eeeveryone knows about it!):

2. Your solution import might fail

When the tool creates the new polymorphic lookup in your solution, it will add the field and its relationships as well as the related tables and their relationships to the solution (all good so far). The problem is that if the tables are not flagged in the solution to include their metadata, the relationship metadata won’t be part of the solution and the whole thing will fall over due to missing dependencies (not so good). If you have this problem you will need to remove and re-add the tables to the solution and don’t forget to tick the all important tick box in the process:

3. You will need to use the XrmToolBox to add/remove tables

Following on from point 1 above, you won’t be able to use the maker portal to make any changes to the field’s tables – you will need to open the field through the Polymorphic Lookup Creator tool, make any changes you need (be aware of data stored in the system if you remove a table from the list of selected tables) and then click on ‘Apply Changes’ at the top (or Delete to remove it all together – make sure no dependencies exist):

4. Cloud Flows will need Switching

If you do wish to use a polymorphic lookup in a cloud flow to get data or otherwise, be prepared for the use of the ‘Switch’ control based on the table the associated record is from before you can do anything else with it. There is a limit of 25 switches but in all honesty I would really worry about you if you create a polymorphic lookup that uses more then 5 tables let alone 25…

5. Polymorphic Lookups cannot be self-referring

In the example of the ‘Affected By’ field on the Contact, the Contact table itself cannot be selected as one of the tables available for selection. This is because the table cannot be the referencing and the referenced table at the same time.

6. Empty tables won’t appear for selection

Taking my example above of the ‘Affected By’ field allowing a user to select records from the Opportunity, Quote and Product tables, if any of those tables don’t have data, they won’t appear for selection but don’t worry the field has been created correctly. For example if my Quote table is empty, the lookup will show only 2 types of records can be selected (once you’ve added records to your table(s) they will show up):

So in summary…

Only create these fields if absolutely necessary as there is an element of ongoing admin that comes with them and they are strictly speaking not supported (yet!). So there you have it. Go crazy. But not too crazy… A big thank you to Josh Seddon for his feedback on living with polymorphic lookups! Your pain has not been in vain!