Skill: Metadata — Making Changes

Before the first metadata change in a session:

1. Confirm the target environment with the user — see the Multi-Environment Rule in dv-overview.
2. Confirm the solution — ask "What solution should these components go into?" If SOLUTION_NAME is in .env, confirm it. If no solution exists yet, you MUST ask the user for the solution name and publisher prefix before creating anything. The publisher prefix is permanent — it cannot be changed after components are created with it.

STOP and ask the user:

"What solution name and publisher prefix should I use? The prefix (e.g., contoso, lit, soc) is permanent on every table and column."

Then query existing publishers and show them — the user may want to reuse one:

# Publisher discovery + solution creation — use SDK (never raw Web API).
# See dv-solution for the full publisher discovery flow.
pages = client.records.get("publisher",
    filter="customizationprefix ne 'none' and uniquename ne 'MicrosoftCorporation'",
    select=["publisherid", "uniquename", "friendlyname", "customizationprefix"], top=10)
publishers = [p for page in pages for p in page]
# MANDATORY: Show existing publishers to user and ask which to use or create new

After user confirms, create using SDK:

publisher_id = client.records.create("publisher", {
    "uniquename": "<name>", "friendlyname": "<display>",
    "customizationprefix": "<prefix>",  # from user input, NOT hardcoded
    "description": "<desc>",
})
solution_id = client.records.create("solution", {
    "uniquename": "<SolutionName>", "friendlyname": "<Display Name>",
    "version": "1.0.0.0",
    "publisherid@odata.bind": f"/publishers({publisher_id})",
})

Never create tables or columns outside a solution.

3. Pass solution="<UniqueName>" in every SDK call, or include "MSCRM.SolutionName": "<UniqueName>" on every raw Web API call.

Skill boundaries

Need	Use instead
Create, update, or delete data records	dv-data
Query or read records	dv-query
Export or deploy solutions	dv-solution

---

How Changes Are Made: Environment-First

Do not write solution XML by hand to create new tables, columns, forms, or views.

The environment validates metadata far more reliably than an agent editing XML. The correct workflow is:

1. Make the change in the environment via the Dataverse MetadataService API (or pac commands where available)
2. Pull the change into the repo via pac solution export + pac solution unpack
3. Commit the result

The exported XML is generated by Dataverse itself and is always valid. Hand-written XML is fragile — a single incorrect attribute or missing element causes an import failure with an opaque error.

The only time you write files directly is when editing something that already exists in the repo (e.g., tweaking an existing view's columns or modifying a form layout you've already pulled).

---

Creating a Table

If creating multiple tables for a data import, also see these sections later in this skill:

• Idempotent Table Creation — catch "already exists" for re-runnable scripts
• Alternate Keys — required for upsert; create immediately after each table
• Metadata Propagation Delays and Lock Contention — phased creation to avoid lock errors

ALWAYS use the SDK unless you need full control over OwnershipType, HasActivities, or other advanced properties. Do NOT use requests or urllib for table creation when the SDK can handle it.

SDK approach (use this by default):

import os, sys
sys.path.insert(0, os.path.join(os.getcwd(), "scripts"))
from auth import get_credential, load_env
from PowerPlatform.Dataverse.client import DataverseClient

load_env()
client = DataverseClient(os.environ["DATAVERSE_URL"], get_credential())

info = client.tables.create(
    "new_ProjectBudget",
    {"new_Amount": "decimal", "new_Description": "string"},
    solution="MySolution",
    primary_column="new_Name",
)
print(f"Created: {info['table_schema_name']}")

Web API fallback (ONLY when you need OwnershipType, HasActivities, or other properties the SDK doesn't expose):

# Helper for Label boilerplate
def label(text):
    return {"@odata.type": "Microsoft.Dynamics.CRM.Label",
            "LocalizedLabels": [{"@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                                  "Label": text, "LanguageCode": 1033}]}

entity = {
    "@odata.type": "Microsoft.Dynamics.CRM.EntityMetadata",
    "SchemaName": "new_ProjectBudget",
    "DisplayName": label("Project Budget"),
    "DisplayCollectionName": label("Project Budgets"),
    "Description": label(""),
    "OwnershipType": "UserOwned",
    "HasActivities": False, "HasNotes": False, "IsActivity": False,
    "PrimaryNameAttribute": "new_name",
    "Attributes": [{
        "@odata.type": "Microsoft.Dynamics.CRM.StringAttributeMetadata",
        "SchemaName": "new_name",
        "DisplayName": label("Name"),
        "RequiredLevel": {"Value": "ApplicationRequired"},
        "MaxLength": 100, "IsPrimaryName": True,
    }]
}
# POST to /api/data/v9.2/EntityDefinitions with MSCRM.SolutionUniqueName header

---

Column Naming: Avoid *Id Suffix Collisions

Never name a regular column with an Id suffix (e.g., prefix_CountryId, prefix_PlayerId). When you later create a lookup relationship to another table, Dataverse auto-generates a navigation property with the Id suffix (e.g., prefix_CountryId). If a regular integer column with the same name already exists, the lookup creation fails with a schema name collision.

Pattern for source system IDs:

• WRONG: prefix_DepartmentId (int) — collides when lookup prefix_DepartmentId is created later
• RIGHT: prefix_SrcDepartmentId (int) — Src prefix distinguishes source IDs from Dataverse lookups
• RIGHT: prefix_DepartmentSourceId (int) — SourceId suffix is also safe

This matters most in multi-table imports where you store the source system's integer FK as a column and then create a Dataverse lookup relationship for the same reference.

---

Adding Columns

SDK approach (preferred):

created = client.tables.add_columns(
    "new_ProjectBudget",
    {"new_Description": "string", "new_Amount": "decimal", "new_Active": "bool"},
)
print(created)  # ['new_Description', 'new_Amount', 'new_Active']

Supported type strings: "string" / "text", "int" / "integer", "decimal" / "money", "float" / "double", "datetime" / "date", "bool" / "boolean", "file", and Enum subclasses (for local option sets).

Choice (picklist) column via SDK:

from enum import IntEnum

class BudgetStatus(IntEnum):
    DRAFT = 100000000
    APPROVED = 100000001
    REJECTED = 100000002

created = client.tables.add_columns(
    "new_ProjectBudget",
    {"new_Status": BudgetStatus},
)

Web API approach (needed for column types the SDK doesn't support — e.g., currency with precision, memo with custom max length):

# Currency column
attribute = {
    "@odata.type": "Microsoft.Dynamics.CRM.MoneyAttributeMetadata",
    "SchemaName": "new_amount",
    "DisplayName": {"@odata.type": "Microsoft.Dynamics.CRM.Label",
                    "LocalizedLabels": [{"@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                                          "Label": "Amount", "LanguageCode": 1033}]},
    "RequiredLevel": {"Value": "None"},
    "MinValue": 0,
    "MaxValue": 1000000000,
    "Precision": 2,
    "PrecisionSource": 2
}
# POST to /api/data/v9.2/EntityDefinitions(LogicalName='new_projectbudget')/Attributes

---

Lookup Columns and Relationships

SDK approach — simple lookup (preferred):

result = client.tables.create_lookup_field(
    referencing_table="new_projectbudget",
    lookup_field_name="new_AccountId",
    referenced_table="account",
    display_name="Account",
    solution="MySolution",
)
print(f"Created lookup: {result.lookup_schema_name}")

SDK approach — full control over 1:N relationship:

from PowerPlatform.Dataverse.models.relationship import (
    LookupAttributeMetadata,
    OneToManyRelationshipMetadata,
    CascadeConfiguration,
)
from PowerPlatform.Dataverse.models.labels import Label, LocalizedLabel
from PowerPlatform.Dataverse.common.constants import CASCADE_BEHAVIOR_REMOVE_LINK

lookup = LookupAttributeMetadata(
    schema_name="new_AccountId",
    display_name=Label(localized_labels=[LocalizedLabel(label="Account", language_code=1033)]),
)

relationship = OneToManyRelationshipMetadata(
    schema_name="account_new_projectbudget",
    referenced_entity="account",
    referencing_entity="new_projectbudget",
    referenced_attribute="accountid",
    cascade_configuration=CascadeConfiguration(delete=CASCADE_BEHAVIOR_REMOVE_LINK),
)

result = client.tables.create_one_to_many_relationship(lookup, relationship, solution="MySolution")
print(f"Created: {result.relationship_schema_name}")

SDK approach — many-to-many relationship:

from PowerPlatform.Dataverse.models.relationship import ManyToManyRelationshipMetadata

relationship = ManyToManyRelationshipMetadata(
    schema_name="new_ticket_knowledgebase",
    entity1_logical_name="new_ticket",
    entity2_logical_name="new_knowledgebase",
)

result = client.tables.create_many_to_many_relationship(relationship, solution="MySolution")
print(f"Created: {result.relationship_schema_name}")

Web API approach (fallback when SDK patterns don't suffice):

relationship = {
    "@odata.type": "Microsoft.Dynamics.CRM.OneToManyRelationshipMetadata",
    "SchemaName": "account_new_projectbudget",
    "ReferencedEntity": "account",
    "ReferencingEntity": "new_projectbudget",
    "Lookup": {
        "@odata.type": "Microsoft.Dynamics.CRM.LookupAttributeMetadata",
        "SchemaName": "new_AccountId",
        "DisplayName": {"@odata.type": "Microsoft.Dynamics.CRM.Label",
                        "LocalizedLabels": [{"@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                                              "Label": "Account", "LanguageCode": 1033}]},
        "RequiredLevel": {"Value": "None"}
    }
}
# POST to /api/data/v9.2/RelationshipDefinitions

After creating a lookup — the @odata.bind navigation property:

When you create records that set this lookup, you need the navigation property name for @odata.bind. The navigation property name is case-sensitive and must match the entity's $metadata (usually the SchemaName of the lookup field, e.g., new_AccountId):

Navigation Property Name	@odata.bind key	Entity set
new_AccountId	new_AccountId@odata.bind	/accounts(<guid>)
new_ParentTicketId	new_ParentTicketId@odata.bind	/new_tickets(<guid>)

Common mistake: Using the logical name (lowercase) like new_accountid@odata.bind returns a 400 error. Navigation property names are case-sensitive and must match the entity's $metadata.

---

Adding a Table to a Solution

After creating a table via API, add it to your solution so it gets pulled on export:

pac solution add-solution-component \
  --solutionUniqueName <SOLUTION_NAME> \
  --component <logical_name> \
  --componentType 1 \
  --environment <url>

Component type 1 = Entity (Table). See dv-solution for the full type code list.

Or via Web API:

# POST to /api/data/v9.2/AddSolutionComponent
body = {
    "ComponentId": "<entity-metadata-id>",
    "ComponentType": 1,       # 1 = Entity
    "SolutionUniqueName": "<SOLUTION_NAME>",
    "AddRequiredComponents": True
}

---

Forms

Neither the MCP server nor the Python SDK supports forms. Use the Web API directly.

Create a form

# POST /api/data/v9.2/systemforms
import os, sys, json, urllib.request
sys.path.insert(0, os.path.join(os.getcwd(), "scripts"))
from auth import get_token, load_env  # get_token() is correct here — SDK does not support forms

load_env()
env = os.environ["DATAVERSE_URL"].rstrip("/")
token = get_token()

form_xml = """<form type="7" name="Project Budget" id="{FORM-GUID}">
  <tabs>
    <tab name="{TAB-GUID}" id="{TAB-GUID}" expanded="true" showlabel="true">
      <labels><label description="General" languagecode="1033" /></labels>
      <columns><column width="100%">
        <sections>
          <section name="{SEC-GUID}" id="{SEC-GUID}" showlabel="false" showbar="false" columns="111">
            <labels><label description="General" languagecode="1033" /></labels>
            <rows>
              <row>
                <cell id="{CELL-GUID-1}" showlabel="true">
                  <labels><label description="Name" languagecode="1033" /></labels>
                  <control id="new_name" classid="{4273EDBD-AC1D-40d3-9FB2-095C621B552D}"
                           datafieldname="new_name" disabled="false" />
                </cell>
              </row>
            </rows>
          </section>
        </sections>
      </column></columns>
    </tab>
  </tabs>
  <header><rows /></header><footer><rows /></footer>
</form>"""

body = {
    "name": "Project Budget Quick Create",
    "objecttypecode": "new_projectbudget",
    "type": 7,           # 7 = quick create, 2 = main
    "formxml": form_xml,
    "iscustomizable": {"Value": True}
}

req = urllib.request.Request(
    f"{env}/api/data/v9.2/systemforms",
    data=json.dumps(body).encode(),
    headers={"Authorization": f"Bearer {token}",
             "Content-Type": "application/json",
             "OData-MaxVersion": "4.0",
             "OData-Version": "4.0"},
    method="POST"
)
with urllib.request.urlopen(req) as resp:
    print(f"Created. FormId: {resp.headers.get('OData-EntityId')}")

Form type codes: 2 = Main, 7 = Quick Create, 6 = Quick View, 11 = Card

Retrieve and modify an existing form

# env and token must be initialized (see form creation setup above)
import json, urllib.request  # SDK does not support forms — raw Web API required

# Step 1: GET the form
url = (f"{env}/api/data/v9.2/systemforms"
       f"?$filter=objecttypecode eq 'new_projectbudget' and type eq 2"
       f"&$select=formid,name,formxml")
req = urllib.request.Request(url, headers={
    "Authorization": f"Bearer {token}",
    "OData-MaxVersion": "4.0", "OData-Version": "4.0", "Accept": "application/json",
})
with urllib.request.urlopen(req) as resp:
    forms = json.loads(resp.read()).get("value", [])

if not forms:
    raise ValueError("Form not found")

form_id = forms[0]["formid"]
form_xml = forms[0]["formxml"]

# Step 2: Modify form_xml string as needed (e.g., add a control, reorder fields)
# form_xml = form_xml.replace(...)

# Step 3: PATCH the form back
patch_body = json.dumps({"formxml": form_xml}).encode()
req = urllib.request.Request(
    f"{env}/api/data/v9.2/systemforms({form_id})",
    data=patch_body,
    headers={"Authorization": f"Bearer {token}",
             "Content-Type": "application/json",
             "OData-MaxVersion": "4.0", "OData-Version": "4.0"},
    method="PATCH"
)
with urllib.request.urlopen(req) as resp:
    print(f"Updated. Status: {resp.status}")
# Then publish (see Publish section below)

Publish forms after create/modify

Forms must be published to take effect. Do this immediately after creating or modifying a form. env and token come from the form creation setup block above — if publishing standalone, re-initialize them:

# env and token must be initialized (see form creation setup above)
# SDK does not support form publishing — raw Web API required
body = json.dumps({
    "ParameterXml": "<importexportxml><entities><entity>new_projectbudget</entity></entities></importexportxml>"
}).encode()
req = urllib.request.Request(
    f"{env}/api/data/v9.2/PublishXml",
    data=body,
    headers={"Authorization": f"Bearer {token}",
             "Content-Type": "application/json",
             "OData-MaxVersion": "4.0", "OData-Version": "4.0"},
    method="POST"
)
with urllib.request.urlopen(req) as resp:
    print(f"Published. Status: {resp.status}")

Replace new_projectbudget with the logical name of the entity whose form you modified.

---

Views

Neither the MCP server nor the Python SDK supports views. Use the Web API directly.

Create a view

# POST /api/data/v9.2/savedqueries
fetch_xml = """<fetch version="1.0" output-format="xml-platform" mapping="logical">
  <entity name="new_projectbudget">
    <attribute name="new_name" />
    <attribute name="new_amount" />
    <attribute name="new_status" />
    <order attribute="new_name" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      <condition attribute="ownerid" operator="eq-userid" />
    </filter>
  </entity>
</fetch>"""

layout_xml = """<grid name="resultset" jump="new_name" select="1" icon="1" preview="1">
  <row name="result" id="new_projectbudgetid">
    <cell name="new_name" width="200" />
    <cell name="new_amount" width="125" />
    <cell name="new_status" width="125" />
  </row>
</grid>"""

body = {
    "name": "My Open Budgets",
    "returnedtypecode": "new_projectbudget",
    "querytype": 0,       # 0 = standard view
    "fetchxml": fetch_xml,
    "layoutxml": layout_xml,
    "isdefault": False,
    "isprivate": False,
    "isquickfindquery": False,
}
# POST to /api/data/v9.2/savedqueries

querytype values: 0 = standard view, 1 = advanced find default, 2 = associated view, 4 = quick find

---

When to Edit Existing Form XML Directly

If the form is already in the repo (pulled via pac solution unpack), targeted edits are acceptable — e.g., reordering fields, changing a label, adding a control to an existing section. For these cases, use this control classid reference:

Field type	Control classid
Text (nvarchar)	{4273EDBD-AC1D-40d3-9FB2-095C621B552D}
Currency (money)	{533B9108-5A8B-42cb-BD37-52D1B8E7C741}
Choice (picklist)	{3EF39988-22BB-4f0b-BBBE-64B5A3748AEE}
Lookup	{270BD3DB-D9AF-4782-9025-509E298DEC0A}
Date/Time	{5B773807-9FB2-42db-97C3-7A91EFF8ADFF}
Whole Number	{C6D124CA-7EDA-4a60-AEA9-7FB8D318B68F}
Decimal	{C3EFE0C3-0EC6-42be-8349-CBD9079C5A6F}
Toggle (boolean)	{67FAC785-CD58-4f9f-ABB3-4B7DDC6ED5ED}
Subgrid	{E7A81278-8635-4d9e-8D4D-59480B391C5B}
Multiline Text (memo)	{E0DECE4B-6FC8-4a8f-A065-082708572369}

All id attributes in form XML must be unique GUIDs. Generate them inside your Python script:

import uuid
guid = str(uuid.uuid4()).upper()

Do not use python -c for GUID generation on Windows — multiline python -c commands break in Git Bash due to quoting differences. Always write a .py script instead.

---

Business Rules

Create business rules in the Power Apps maker portal. They are too complex to write reliably as JSON/XAML. After creation, export+unpack the solution and commit the result.

---

Publisher Prefix

All custom schema names must use your solution's publisher prefix (e.g., new_, contoso_). Find yours:

pac solution list --environment <url>

Or check solutions/<SOLUTION_NAME>/Other/Solution.xml after the first pull — look for <CustomizationPrefix>.

---

FormXml Pitfalls

• All id attributes must be valid GUIDs in {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx} format. Do not use strings like "general".
• labelid is also a GUID — not a human-readable string.
• Subgrid controls require a valid <ViewId> — must be the GUID of an existing SavedQuery. Create the view first.
• Cell, section, tab, and control IDs must all be unique across the entire form.
• Control classid values — see the classid table above.

Tip: Create forms in the maker portal and pull via pac solution export — use the pulled XML as a template for programmatic creation.

---

After Creating Columns: Report Logical Names

After creating columns (via Web API or MCP), always report the actual logical names to the user. Column names may be normalized or prefixed in ways the user doesn't expect. Summarize in a table:

Display Name	Logical Name	Type
Email	cr9ac_email	String
Tier	cr9ac_tier	Picklist
Customer	cr9ac_customerid	Lookup

This prevents downstream failures when the user tries to insert data using incorrect column names.

---

Common Web API Error Codes

Error Code	Meaning	Recovery
0x80040216	Transient metadata cache error. Column or table metadata not yet propagated.	Wait 3-5 seconds and retry. Usually succeeds on second attempt.
0x80048d19	Invalid property in payload. A field name doesn't match any column on the table.	Check logical column names — use EntityDefinitions(LogicalName='...')/Attributes to verify.
0x80040237	Schema name already exists.	Verify the column/table exists before creating a new one — it may have been created by a previous timed-out call.
0x8004431a	Publisher prefix mismatch.	Ensure all schema names use the solution's publisher prefix.
0x80060891	Metadata cache not ready after table creation.	Call GET EntityDefinitions(LogicalName='...') first to force cache refresh, then retry.

Always translate error codes to plain English before presenting them to the user.

---

Metadata Propagation Delays and Lock Contention

After creating tables, columns, or alternate keys, Dataverse runs internal metadata operations (index building, cache propagation) that can take 3-30 seconds. Submitting another metadata operation while these are still running causes lock contention errors ("another operation is running").

Common symptoms:

• Picklist columns fail with 0x80040216 immediately after table creation
• Lookup @odata.bind operations fail with "Invalid property" shortly after column creation
• update_table (MCP) fails with "EntityId not found in MetadataCache"
• Alternate key creation fails with lock contention after table creation
• Lookup creation fails with "another customization operation is running"

Mitigation — use phased creation, not interleaved:

When creating many tables with alternate keys and lookups (e.g., multi-table import schema), create them in phases rather than interleaving operations on the same table:

1. Phase 1: Create ALL tables (5-8s delay between each)
2. Wait 15-30s for metadata propagation
3. Phase 2: Create ALL alternate keys (3s delay between each)
4. Wait 15-30s for index building
5. Phase 3: Create ALL lookups (3s delay between each)

Do NOT interleave: create table A → create key A → create table B → create key B. This causes lock contention because key A's index build blocks table B's creation.

Retry pattern: Wrap all metadata operations with retry for transient lock errors:

import time

def retry_metadata(fn, description, max_attempts=5):
    for attempt in range(max_attempts):
        try:
            return fn()
        except Exception as e:
            err = str(e)
            if "already exists" in err.lower() or "0x80040237" in err:
                print(f"  {description}: already exists, skipping")
                return None
            if "another" in err.lower() and "running" in err.lower():
                wait = 10 * (attempt + 1)
                print(f"  {description}: lock contention, waiting {wait}s (attempt {attempt+1}/{max_attempts})...")
                time.sleep(wait)
                continue
            raise
    print(f"  WARNING: {description} failed after {max_attempts} attempts")
    return None

---

Session Closing: Pull to Repo

After every metadata session, perform the pull-to-repo sequence — see dv-overview "After Any Change: Pull to Repo" for the full export/unpack/commit commands.

If you used the MSCRM.SolutionName header during creation, verify components were added before exporting:

pac solution list-components --solutionUniqueName <SOLUTION_NAME> --environment <url>

---

Idempotent Table Creation

When creating tables programmatically (e.g., a schema setup script that may be re-run), catch 0x80040237 to skip tables that already exist:

try:
    info = client.tables.create(schema_name, columns, solution=SOLUTION, primary_column="prefix_name")
    print(f"Created: {info['table_schema_name']}")
except Exception as e:
    err = str(e)
    if "already exists" in err.lower() or "0x80040237" in err:
        print("Already exists, skipping")
    else:
        raise

To pre-check without creating, query EntityDefinitions directly:

# Returns 404 if the table does not exist
GET /api/data/v9.2/EntityDefinitions(LogicalName='new_projectbudget')?$select=LogicalName

---

Alternate Keys (Required for Upsert)

An alternate key tells Dataverse how to uniquely identify a record using a business column instead of the GUID primary key. This is required for UpsertMultiple — without it, Dataverse has no way to detect whether a record already exists.

When to create alternate keys: Always create them on source-system ID columns (prefix_Src*Id) during schema setup, before data import. This makes every import idempotent from the start — re-running never creates duplicates.

How the agent decides which column:

• Database source (SQLite, SQL Server): Read the schema to identify primary keys — this is unambiguous. The source PK column maps directly to the alternate key:
  • Source Country.Country_Id (INTEGER PRIMARY KEY) → alternate key on prefix_srccountryid
  • Source composite PK (Order_Id, Line_No) → composite alternate key on both columns
• Excel/CSV source: Inspect the data for columns with all-unique values and naming conventions suggesting an ID (*_ID, *_Code). Propose the candidate to the user and get confirmation before creating the key — uniqueness in the current data doesn't guarantee it's the intended business key.
• No identifiable unique column: Ask the user which column(s) uniquely identify each row. Do not guess.

SDK approach (preferred):

import os, sys
sys.path.insert(0, os.path.join(os.getcwd(), "scripts"))
from auth import get_credential, load_env
from PowerPlatform.Dataverse.client import DataverseClient

load_env()
client = DataverseClient(os.environ["DATAVERSE_URL"], get_credential())

# Single-column key (most common for imports)
key = client.tables.create_alternate_key(
    "prefix_Country",
    "prefix_SrcCountryIdKey",
    ["prefix_srccountryid"],
    display_name="Source Country ID",
)
print(f"Key created: {key.schema_name} (status: {key.status})")

# Composite key (for tables with multi-column PKs in the source)
key = client.tables.create_alternate_key(
    "prefix_OrderLine",
    "prefix_OrderLineSourceKey",
    ["prefix_srcorderid", "prefix_srclineno"],
    display_name="Source Order Line Key",
)

Idempotent key creation — catch "already exists" to make the script re-runnable:

from PowerPlatform.Dataverse.core.errors import HttpError

def ensure_alternate_key(table, key_name, columns, display_name):
    try:
        key = client.tables.create_alternate_key(table, key_name, columns, display_name=display_name)
        print(f"  Key created: {key_name} on {table}")
    except HttpError as e:
        if "already exists" in str(e).lower() or "0x80048d0b" in str(e):
            print(f"  Key already exists: {key_name}")
        else:
            raise

# Create keys for all import tables
ensure_alternate_key("prefix_Country", "prefix_SrcCountryIdKey",
    ["prefix_srccountryid"], "Source Country ID")
ensure_alternate_key("prefix_City", "prefix_SrcCityIdKey",
    ["prefix_srccityid"], "Source City ID")

Check key status — index creation is async for tables with existing data:

keys = client.tables.get_alternate_keys("prefix_Country")
for k in keys:
    print(f"  {k.schema_name}: {k.status}")  # Pending, Active, or Failed

Constraints:

• Valid column types for keys: Integer, Decimal, String, DateTime, Lookup, OptionSet
• Max 16 columns per key, 900 bytes total key size
• Max 10 alternate keys per table
• Index creation is async — Dataverse builds the index in the background. For small tables (<10K rows) this is near-instant. For large existing tables, check EntityKeyIndexStatus for Active/Failed before using the key.
• If the key column has non-unique data, index creation fails (no data corruption — the key just stays in Failed state). Fix the data, then call ReactivateEntityKey.

Safety: Creating an alternate key on a column with unique data is a non-destructive metadata operation. It adds a database index — it does not modify existing records. If the column data isn't actually unique, the key creation fails harmlessly.

---

EntityDefinitions Filter Limitation

startswith() is NOT supported as a filter on EntityDefinitions. This query will return a 400 error:

GET /api/data/v9.2/EntityDefinitions?$filter=startswith(LogicalName,'new_')  # BROKEN

To retrieve metadata for multiple custom tables, query each table individually:

GET /api/data/v9.2/EntityDefinitions(LogicalName='new_projectbudget')?$select=LogicalName,EntitySetName

Or query all entities and filter in Python:

GET /api/data/v9.2/EntityDefinitions?$select=LogicalName,EntitySetName
# Then filter: [e for e in result["value"] if e["LogicalName"].startswith("new_")]

This matters for import scripts that need to discover entity set names (e.g., new_projectbudgets) before writing records with @odata.bind.

---

MCP Table Creation Notes

When using MCP create_table or update_table:

• Timeouts don't mean failure. Always describe_table before retrying. If the table exists, skip creation.
• Self-referential lookups (e.g., Parent → same table) must be added via update_table after the table is created.
• Metadata cache delays. After create_table, call describe_table before update_table to force cache refresh.
• Column name normalization. Spaces in column names become underscores: "Specialty Area" → cr9ac_specialty_area. Always verify with describe_table.