Dynamics 365 Multi-table lookups

Multi-table lookups, are now finally live (Preview) for use via API or SDK. Multi-table lookups (polymorphic lookups) allow the creation of a lookup in one table that looks up records in multiple other tables at once. This provides much greater flexibility in retrieving data within your environments.

Let’s imagine we have many different objects that are in different formats but have the same name. For example we have Books, Audio, Video and Pictures. Creating a multi-table lookup called mt_MultiMedia that has 1:M relationships all to these individuals mt_Pictures, mt_Audio, mt_Books and mt_Video will result in a mt_MultiMedia lookup table provides the records stored in individual tables.

new_Media lookup table

PrimaryIDPrimaryNameRelatedIDRelated Name
<multimedia1>MediaObjectOne<books1>Media1
< multimedia2>MediaObjectTwo<audio1>Media1
< multimedia3>MediaObjectThree<video1>Media1
< multimedia4>MediaObjectFour<pictures1>Media2

mt_Books table

PrimaryIDPrimaryNameISBNNUMBER
<books1>Media11ww-3452
<books2>Media2a4e-87hw

mt_Auido table

PrimaryIDPrimaryNameCodec
<audio1>Media1mp4
<audio2> Media2wma

mt_Video table

PrimaryIDPrimaryNamecodec
<video1>Media1wmv
<video2>Media2avi

mt_Pictures table

PrimaryIDPrimaryNamepıctureformat
<picture1>Media2jpg
<picture2>Media3png

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

  • A lookup on Multimedia with the name Media1 would retrieve records for <video1>, <books1> and <audio1> .
  • A lookup on Multimedia of Media3 would retrieve records for <picture2>.

Executing CreatePolymorphicLookupAttribute Request

public Guid CreatePolymorphicLookup(string prefix, string displayName, string schemaName, string referencingEntity, string[] referencedEntities, string solutionUniqueName)
        {
            var label = new LocalizedLabel()
            {
                Label = displayName,
                LanguageCode = languageCode
            };

            var relations = new OneToManyRelationshipMetadata[referencedEntities.Length];
            for (var i = 0; i < referencedEntities.Length; i++)
            {
                var referencedEntity = referencedEntities[i];

                relations[i] = new OneToManyRelationshipMetadata
                {
                    SchemaName = $"{prefix}{referencingEntity}_{referencedEntity}_{schemaName}",
                    ReferencedEntity = referencedEntity,
                    ReferencingEntity = referencingEntity
                };

                if (relations[i].SchemaName.Length > 100)
                {
                    relations[i].SchemaName = relations[i].SchemaName.Substring(0, 100);
                }
            }

            var request = new OrganizationRequest
            {
                RequestName = "CreatePolymorphicLookupAttribute",
                Parameters =
                {
                    {"OneToManyRelationships", relations },
                    {
                        "Lookup", new LookupAttributeMetadata()
                        {
                            DisplayName = new Label(label, new LocalizedLabel[] {label}) ,
                            SchemaName = schemaName
                        }
                    },
                    {
                        "SolutionUniqueName", solutionUniqueName}
                }
            };

            var response = _service.Execute(request);

            return new Guid(response.Results["AttributeId"].ToString());
        }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s