CRM Crate

Create a Multi-Table Lookup field in Dynamics 365 CRM

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.

PrimaryIDPrimaryNameRelatedIDRelated Name
<mediaA>MediaTypeOne<booksA>ContentA
<mediaB>MediaTypeTwo<audioA>ContentB
<mediaC>MediaTypeThree<videoA>ContentC
new_Media lookup table
PrimaryIDPrimaryNameCallNumber
<booksA>ContentA1ww-3451
<booksB>ContentBa4e-87hb
new_Books table
PrimaryIDPrimaryNameAudioFormat
<audioA>ContentAmp4
<audioB>ContentCwma
new_Audio table
PrimaryIDPrimaryNameVideoFormat
<videoA>ContentCwmv
<videoB>ContentBavi
new_Video table

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.
ComponentDetails
SolutionName of the solution which needs to layer the polymorphic lookup.
Referencing TableA table or entity in which we have to create the polymorphic lookup.
AttributeWe can either create a new polymorphic lookup or transform an existing lookup field into a polymorphic lookup.
Referenced TablesSelect more than one targeting entities for the polymorphic lookup.
Lookup Display NameName of the polymorphic lookup field.
Lookup Schema NameSchema name of the polymorphic lookup field.
  • 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.
A new lookup field named “Polymorphic Lookup” is created and later added in form as shown above.

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.

Operation
(method)
DescriptionURL format
Create
(POST)
New API[OrganizationUrl]/api/data/v9.0
/CreatePolymorphicLookupAttribute
Retrieve attribute
(GET)
Existing API[OrganizationUrl]/api/data/v9.0
/EntityDefinitions(<EntityId>)/Attributes(<AttributeId>)
Retrieve relationship
(GET)
Existing API[OrganizationUrl]/api/data/v9.0
/RelationshipDefinitions(<RelationshipId>)
Add relationship
(POST)
Adds a relationship
to an existing
polymorphic lookup
attribute
[OrganizationUrl]/api/data/v9.0
/RelationshipDefinitions
Remove relationship
(DELETE)
Existing API[OrganizationUrl]/api/data/v9.0
/RelationshipDefinitions(<RelationshipId>)
Remove attribute
(DELETE)
Existing API[OrganizationUrl]/api/data/v9.0
/EntityDefinitions(<EntityId>)/Attributes(<AttributeId>)

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

5 1 vote
Article Rating
Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments
error: CRM Crate Security Engine - Disabled Right Click & Selection!

Congratulations!

Well Done,
Welcome to CRM Crate

Stay tuned with us and get all latest updates and learning in Microsoft CRM and related techonologes.

Practise EXams

Free!!

Sharpen your skills with our free practise exams.