Here we will learn to create a multi-table lookup field in Dynamics 365 CRM. Before we start, make sure to subscribe to CRM Crate so that you stay up-to-date in the field of Dynamics 365.

What are multi table lookups in Dynamics 365?

Multi-table lookup or Polymorphic lookup type field allow a user to use a specific table that has multiple One-To-Many (1:M) relationships to other entities / tables in the environment. A single lookup type column can refer to multiple other tables. A lookup value submitted to the multi-table type column will be matched to a record in any of the related tables.

Multi-table types are currently available in Microsoft Dataverse as static type attributes such as “Customer” lookup, which connects to Account and Contact. The new multi-table features gives users the power to define any other multi-table lookups they may need.

Example of multi-table lookup –

Let’s consider you are anchoring media for users in a library. You have many different MediaTypes, many of them have the same name but are in different formats like “Audio”, “Books”, and “Video”. Creating a multi-table lookup called “new_Media” that has 1:M relationships to “new_Audio”, “new_Books”, and “new_Video” will result in a “new_Media” lookup table that provides quick identifications of records stored in specific tables.

The Media look up can return records across all the tables in the polymorphic lookup.

• A lookup on Media with the name ContentA would retrieve records for <booksA> and <audioA>.
• A lookup on Media of ContentC would retrieve records for <audioB> and <videoA>.

Ways to create a multi-table lookup in Dynamics 365.

Below are few easy ways to create a multi-table lookup fields.

• XRM Tool Box Plugin – Polymorphic Lookup Creator [Recommended/No Code Solution].
• Web API Request [POST].
• Console Application.

Create a multi-table lookup field using Polymorphic Lookup Creator plugin –

• We can use the ready-made tool called “Polymorphic Lookup Creator” plugin in the XRM Tool Box.
• Find the plugin details using the given link : https://www.xrmtoolbox.com/plugins/MscrmTools.PolymorphicLookupCreator/.
• Open the XRM Tool Box and connect it to your organization as shown below.

• Navigate to Configuration >> Tool Library and search for the plugin called “Polymorphic Lookup Creator”. Install the plugin as shown below.

• In our scenario, we will create a multi-table lookup field in “Subscriber” entity which will have target reference to two entities named as “Deliverable” and “Marketing” entity.
• Configure the tool with the components details as shown below.

• Once the configuration is completed, click on the button “Create Polymorphic Lookup”.

• Now lets validate the newly created Polymorphic Lookup field in Dynamics 365 CRM.

Create a multi-table lookup field using a .Net based console application –

Use the below C# code to create / request a new polymorphic lookup field creation in the Dataverse.

string ConnectionString = "AuthType = OAuth; " +
                  "AppId=51f81489-12ee-4a9e-aaae-a2591f45987d; " +
                  "Username=myuser@mytenant.com; " +
                  "Password=mypassword; " +
                  "RedirectUri=app://58145B91-0C36-4500-8554-080854F2AC97;" +
                  "Url = https://orgname.crm4.dynamics.com/;";
 
 
 CrmServiceClient svc = new CrmServiceClient(ConnectionString);
 
            if (svc.IsReady)
            {
                // Create PolymorphicLookupAttribute 
                var varOrgRequest = new OrganizationRequest();
 
                // Specify the request name
                varOrgRequest.RequestName = "CreatePolymorphicLookupAttribute";
 
                // Specify the lookup attribute details
                varOrgRequest.Parameters["Lookup"] = new LookupAttributeMetadata()
                {
                    SchemaName = "new_polylookup",
                    DisplayName = new Label("Polymorphic Lookup", 1033)
                };
 
                // Referencing entity is our custom entity named Subscriber
			    // Referenced entity is Deliverable

                var oneToManyRelation1 = new OneToManyRelationshipMetadata();
                oneToManyRelation1.ReferencingEntity = "cc_subscriber";
                oneToManyRelation1.ReferencedEntity = "cc_deliverable";
                oneToManyRelation1.SchemaName = "cc_subscriber_cc_deliverable";
 
                // Referencing entity is our custom entity named Subscriber
                // Referenced entity is Marketing
                var oneToManyRelation2 = new OneToManyRelationshipMetadata();
                oneToManyRelation2.ReferencingEntity = "cc_subscriber";
                oneToManyRelation2.ReferencedEntity = "cc_marketing";
                oneToManyRelation2.SchemaName = "cc_subscriber_cc_marketing";

 
                // populate OneToManyRelationships parameter of CreatePolymorphicLookupAttribute request
                varOrgRequest.Parameters["OneToManyRelationships"] = new OneToManyRelationshipMetadata[]
                {
                    oneToManyRelation1, oneToManyRelation2
                };
 
                // specify the existing solution name 
                varOrgRequest.Parameters["SolutionUniqueName"] = "CRM Crate India";
 
                var response = svc.Execute(varOrgRequest);
            }

Create a multi-table lookup field using a Web API –

Shown below is an HTTP post for a polymorphic lookup attribute.

POST [Organization URI]/api/data/v9.0/CreatePolymorphicLookupAttribute HTTP/1.1 

Accept: application/json 
Content-Type: application/json; charset=utf-8 
OData-MaxVersion: 4.0 
OData-Version: 4.0 

{
 "OneToManyRelationships": [
   {
     "SchemaName": "new_media_new_book",
     "ReferencedEntity": "new_book",
     "ReferencingEntity": "new_media"
   },
   {
     "SchemaName": "new_media_new_video",
     "ReferencedEntity": "new_video",
     "ReferencingEntity": "new_media"
   },
   {
     "SchemaName": "new_media_new_audio",
     "ReferencedEntity": "new_audio",
     "ReferencingEntity": "new_media",
     "CascadeConfiguration": {  
        "Assign": "NoCascade",  
        "Delete": "RemoveLink",  
        "Merge": "NoCascade",  
        "Reparent": "NoCascade",  
        "Share": "NoCascade",  
        "Unshare": "NoCascade"  
     }
   }
 ],

 "Lookup": {
   "AttributeType": "Lookup",
   "AttributeTypeName": {
     "Value": "LookupType"
   },

   "Description": {
     "@odata.type": "Microsoft.Dynamics.CRM.Label",
     "LocalizedLabels": [
       {
         "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
         "Label": "Media Polymorphic Lookup",
         "LanguageCode": 1033
       }
     ],

     "UserLocalizedLabel": {
       "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
       "Label": " Media Polymorphic Lookup Attribute",
       "LanguageCode": 1033
     }
   },

   "DisplayName": {
     "@odata.type": "Microsoft.Dynamics.CRM.Label",
     "LocalizedLabels": [
       {
         "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
         "Label": "MediaPolymorphicLookup",
         "LanguageCode": 1033
       }
     ],

     "UserLocalizedLabel": {
       "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
       "Label": "MediaPolymorphicLookup",
       "LanguageCode": 1033
     }
   },

   "SchemaName": "new_mediaPolymporphicLookup",
   "@odata.type": "Microsoft.Dynamics.CRM.ComplexLookupAttributeMetadata"
 }
}

The response from the HTTP post is shown below containing the ID of the polymorphic attribute and all the relationships created.

{
    "@odata.context":
      "http://<organization URL>/api/data/v9.1/$metadata#Microsoft.Dynamics.CRM.CreatePolymorphicLookupAttributeResponse",

    "RelationshipIds":[
        "77d4c6e9-0397-eb11-a81c-000d3a6cfaba",
        "7ed4c6e9-0397-eb11-a81c-000d3a6cfaba",
        "85d4c6e9-0397-eb11-a81c-000d3a6cfaba"
    ],

    "AttributeId":"d378dd3e-42f4-4bd7-95c7-0ee546c7de40"

Multi-table lookup APIs in Dynamics 365.

The following table contains the API’s required for the polymorphic lookup related operations.

Thus we learned to create a Multi-Table Lookup field in Dynamics 365 CRM.

• LinkedIn
• Facebook
• Telegram