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.

【ドイツレッド•ドット賞受賞製品】姿勢サポート椅子【2024最新昇級腰サポート椅子】 DEZ...

￥5,759￥9,599

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

ミニpc 最新第12世代 n95 ミニpc動作より安定 2023新版最大3.4GHz mini pc 4C/4T 小型pc 16GB DDR4 512GB M.2 SSD 容量拡大可能 4K 静音高速熱放散ミニパソコン超軽量豊富なインターフェース USB3.0/HDMI 2.0 高速2.4G/5GWi-Fi BT4.2 省電力 Win11対応

￥22,798￥26,998

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

CORONA(コロナ) 石油ストーブ【日本生産】 (木造6畳 / コンクリート8畳まで) 電源不...

￥11,520￥12,800

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

Great Home JP 湯たんぽゆたんぽ充電式【大きめサイズ&保温ポケット型デザイン...

￥2,999￥3,799

（2024/11/07 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).

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