Dataverse Allow multiple table types to be entered in the search column. | multi-table lookup columns

I tried one of the lesser-known Dataverse features, “multi-table lookup columns”.

Multi-table lookup columns
What we want to do
Implementation method
Using the API “CreatePolymophicLookupAttribute” from Postman
Execution
Note: Adding is easy, but editing and deleting is hard!

Multi-table lookup columns

One of Dataverse’s lesser-known features is the “Multi-table lookup” function.

This is a feature that allows multiple tables to be set up for a table’s search columns, which can be used to achieve polymorphic associations.

It is difficult to explain in words, so I explained with what I actually want to do and the operation.

*Click here for the official

複数テーブルのルックアップ列を使用する - Power Apps

単一ルックアップタイプの列を使用して、他の複数テーブルのデータを参照する方法について説明します。

What we want to do

For example, suppose we have a “cat” table like this,

When you add a column called “Favorite Food” to the “Cat” table, you want to be able to set this column to any record in the three tables “Meat”, “Fish”, and “Churu”, and so on.
*If I add a search column normally, I can only choose one of the tables.
*”Churu” is the name of a candy sold in Japan that cats love.

ノートパソコンタッチパネル Office搭載 Windows11 軽量薄型 15.6インチ FHD...

￥50,983￥59,980

（2024/11/21 12:00 時点）

【整備済み品】富士通ノートパソコン LifeBook A577 15.6型／第7世代CPU cor...

￥19,900￥33,800

（2024/11/21 12:00 時点）

タブレット 12インチ wi-fiモデル Android14 アーアユー M50 2K タブレットP...

￥22,999￥32,999

（2024/11/21 12:00 時点）

LG ゲーミングモニター UltraGear 27GR93U-B 27インチ/PCゲーム、家庭用ゲー...

￥57,587￥67,525

（2024/11/21 12:00 時点）

Implementation method

In this case, we will use the “multi-table lookup” function discussed at the beginning of this article to allow any of the three tables to be entered in the “Favorite Food” column of the “Cat” table.

At the time of this writing, “multi-table lookup” function can only be configured from the WebAPI or SDK (not from Power Apps Studio).

So this time, we will use Postman to set it up via WebAPI.

*Here is how to call WebAPI using Postman

Postmanを使用しDataverseにWebAPI経由でアクセスしてみる

Postmanを使用し、DataverseにWebAPI経由でアクセスする方法を調べてみたので、メモ。 DataverseはWebAPI経由でアクセス可能 DataverseはWebAPIからアクセスすることができ、様々な操作や設定を行った

Using the API “CreatePolymophicLookupAttribute” from Postman

After completing the preliminary preparation in Postman to “Obtain an access token”, call the API “CreatePolymorphicLookupAttribute” using a POST request.

The contents of the body are here, and options for creating columns can also be set.

{
  "OneToManyRelationships": [
    {
      "SchemaName": "cr9cb_favorite_fish",
      "ReferencedEntity": "cr9cb_fish",
      "ReferencingEntity": "cr9cb_cat"
    },
    {
      "SchemaName": "cr9cb_favorite_meat",
      "ReferencedEntity": "cr9cb_meat",
      "ReferencingEntity": "cr9cb_cat"
    },
    {
      "SchemaName": "cr9cb_favorite_ciaochuru",
      "ReferencedEntity": "cr9cb_churu",
      "ReferencingEntity": "cr9cb_cat",
      "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": "Favorite food",
          "LanguageCode": 1041
        }
      ],
      "UserLocalizedLabel": {
        "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
        "Label": "Favorite food",
        "LanguageCode": 1041
      }
    },
    "DisplayName": {
      "@odata.type": "Microsoft.Dynamics.CRM.Label",
      "LocalizedLabels": [
        {
          "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
          "Label": "Favorite food",
          "LanguageCode": 1033
        }
      ],
      "UserLocalizedLabel": {
        "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
        "Label": "Favorite food",
        "LanguageCode": 1033
      }
    },
    "SchemaName": "cr9cb_favorite_food",
    "@odata.type": "Microsoft.Dynamics.CRM.ComplexLookupAttributeMetadata"
  }
}

*As you can see from the JSON, OneToManyRelationships defines all the relationships (cat and fish, cat and meat, cat and churu).

呼び出しが成功すると「ねこ」テーブルに列「大好物」が作られる。