Multi-table lookups

Article
12/06/2022
2 minutes to read

Multi-table lookup type columns allow a user to use a specific table that has multiple one-to-many (1:N) relationships to other 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 lookups can be created with both local tables and virtual tables as referenced tables.

Multi-table types are currently built into Microsoft Dataverse as static types like Customer, which connects to Account and Contact. This new feature gives users the power to define any other multi-table lookups they may need.

Note

At this time users can create and modify custom multi-table lookups via the SDK or Web APIs. Interactive user interface support will be coming in a future release.

Examples

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

new_Media lookup table

PrimaryID	PrimaryName	RelatedID	Related Name
<media1>	MediaObjectOne	<books1>	Content1
<media2>	MediaObjectTwo	<audio1>	Content1
<media3>	MediaObjectThree	<video1>	Content3
<media4>	MediaObjectFour	<audio2>	Content3

new_Books table

PrimaryID	PrimaryName	CallNumber
<books1>	Content1	1ww-3452
<books2>	Content2	a4e-87hw

new_Audio table

PrimaryID	PrimaryName	AudioFormat
<audio1>	Content1	mp4
<audio2>	Content3	wma

new_Video table

PrimaryID	PrimaryName	VideoFormat
<video1>	Content3	wmv
<video2>	Content2	avi

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

A lookup on Media with the name Content1 would retrieve records for <books1> and <audio1>
A lookup on Media of Content3 would retrieve records for <audio2> and <video1>

Web API example

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"

Use the multi-table lookup API's

The following table lists the operations relevant for table and attribute definitions.

Operation (method)	Description	URL 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>)

The following table lists the operations relevant for table and attribute data.

Operation (method)	Description	URL format
Create (POST)	See the "new_checkouts" example below	[OrganizationUrl]/api/data/v9.0 /<entitysetName>
Retrieve (GET)	Add the following header to get annotations: Content-Type: application/json Prefer: odata.include-annotations="*"	[OrganizationUrl]/api/data/v9.0 /<entitysetName>(<recordId>)

Below is an example request that creates a new entityset with 2 rows.

POST [OrganizationUrl]/api/data/v9.1/new_checkouts

{
  "new_name": "c1",
  new_CheckedoutItem_new_book@odata.bind: "/new_books(387a2c9b-ecc6-ea11-a81e-000d3af68bd7)"
}

{
  "new_name": "c2",
  new_CheckedoutItem_new_device@odata.bind: "/new_devices(6472e7ba-ecc6-ea11-a81e-000d3af68bd7)"
}

Create polymorphic lookup (example payload)

POST [OrganizationUrl]/api/data/v9.0/CreatePolymorphicLookupAttribute

{
  "OneToManyRelationships": [
    {
      "SchemaName": "new_checkout_poly_new_book",
      "ReferencedEntity": "new_book",
      "ReferencingEntity": "new_checkout"
    },
    {
      "SchemaName": "new_checkout_poly_new_device",
      "ReferencedEntity": "new_device",
      "ReferencingEntity": "new_checkout"
    },
    {
      "SchemaName": "new_checkout_poly_new_dvd",
      "ReferencedEntity": "new_dvd",
      "ReferencingEntity": "new_checkout",
      "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": "Checkouted item Polymorphic Lookup Attribute",
          "LanguageCode": 1033
        }
      ],
      "UserLocalizedLabel": {
        "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
        "Label": "Checkedout item Polymorphic Lookup Attribute",
        "LanguageCode": 1033
      }
    },
    "DisplayName": {
      "@odata.type": "Microsoft.Dynamics.CRM.Label",
      "LocalizedLabels": [
        {
          "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
          "Label": "Checkedout item",
          "LanguageCode": 1033
        }
      ],
      "UserLocalizedLabel": {
        "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
        "Label": "Checkedout item",
        "LanguageCode": 1033
      }
    },
    "SchemaName": "new_CheckedoutItem",
    "@odata.type": "Microsoft.Dynamics.CRM.ComplexLookupAttributeMetadata"
  }
}

Add relationship to existing polymorphic lookup (example payload)

POST [OrganizationUrl]/api/data/v9.0/RelationshipDefinitions

{
  "SchemaName": "new_checkout_poly_new_researchresource",
  "@odata.type": "Microsoft.Dynamics.CRM.OneToManyRelationshipMetadata",
  "CascadeConfiguration": {
    "Assign": "NoCascade",
    "Delete": "RemoveLink",
    "Merge": "NoCascade",
    "Reparent": "NoCascade",
    "Share": "NoCascade",
    "Unshare": "NoCascade"
  },
  "ReferencedEntity": "new_researchresource",
  "ReferencingEntity": "new_checkout",
  "Lookup": {
    "AttributeType": "Lookup",
    "AttributeTypeName": { "Value": "LookupType" },
    "Description": {
      "@odata.type": "Microsoft.Dynamics.CRM.Label",
      "LocalizedLabels": [
        {
          "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
          "Label": "Checkout Polymorphic Lookup Attribute",
          "LanguageCode": 1033
        }
      ],
      "UserLocalizedLabel": {
        "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
        "Label": "Checkout Polymorphic Lookup Attribute",
        "LanguageCode": 1033
      }
    },
    "DisplayName": {
      "@odata.type": "Microsoft.Dynamics.CRM.Label",
      "LocalizedLabels": [
        {
          "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
          "Label": "Checkout item",
          "LanguageCode": 1033
        }
      ],
      "UserLocalizedLabel": {
        "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
        "Label": "Checkout item",
        "LanguageCode": 1033
      }
    },
    "SchemaName": "new_CheckedoutItem",
    "@odata.type": "Microsoft.Dynamics.CRM.LookupAttributeMetadata"
  }
}