---
name: dv-metadata
description: Dataverse schema authoring via the Python SDK and Web API — tables, columns, relationships, forms, and views. Use when the user wants to define or evolve the data model — add a column, create a table, set up a lookup, customize a form, or build a view.
---

# 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:

```python
# 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:

```python
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** — check-first pattern 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):**

```python
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",
    display_name="Project Budget",  # human-readable name; plural auto-appends "s"
)
print(f"Created: {info['table_schema_name']}")
```

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

```python
# 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`). Dataverse auto-generates a navigation property with the `Id` suffix when you create a lookup — if a regular column with that name exists, lookup creation fails with a schema name collision.

- WRONG: `prefix_DepartmentId` (int) — collides with auto-generated lookup
- RIGHT: `prefix_SrcDepartmentId` or `prefix_DepartmentSourceId`

---

## Adding Columns

**SDK approach (preferred):**

```python
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:**

```python
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):**

```python
# 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):**

```python
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:**

```python
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:**

```python
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):**

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

---

## Forms and Views

The MCP server and Python SDK do not support forms or views — both require raw Web API calls (`urllib`).

**Quick reference:**
- **Create form:** `POST /api/data/v9.2/systemforms` with `formxml` (form type: `2`=Main, `7`=Quick Create, `6`=Quick View, `11`=Card).
- **Modify form:** `GET` filtered by `objecttypecode` + `type`, edit `formxml`, `PATCH` back, then publish.
- **Publish:** `POST /api/data/v9.2/PublishXml` with `<importexportxml><entities><entity>...` — required for forms to take effect.
- **Create view:** `POST /api/data/v9.2/savedqueries` with `fetchxml` + `layoutxml` (querytype: `0`=standard, `1`=advanced find default, `2`=associated, `4`=quick find).

For full code samples, the form-XML templates, the control `classid` table for editing existing forms, and the publish workflow, see [`references/forms-and-views.md`](references/forms-and-views.md).

Key invariants:
- All `id` attributes in form XML must be unique GUIDs (`str(uuid.uuid4()).upper()`).
- Do not use `python -c` for GUID generation on Windows — write a `.py` file.
- Forms must be published after every create or modify, otherwise changes are invisible to users.

---

## 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 / alternate keys, Dataverse runs internal metadata operations (index build, cache propagation) for 3–30 seconds. Submitting another metadata operation while these run causes lock-contention errors.

**Mitigation — phased creation, not interleaved.** Create ALL tables → wait 15–30s → create ALL alternate keys → wait 15–30s → create ALL lookups. Do NOT interleave operations on the same table.

**Symptoms** (any of these means propagation isn't done):
- Picklist column creation fails with `0x80040216`
- Lookup `@odata.bind` fails with "Invalid property"
- `update_table` (MCP) fails with "EntityId not found in MetadataCache"
- Lookup or alternate-key creation fails with "another customization operation is running"

For the `retry_metadata` helper that catches transient lock errors and the full phased-creation sequence, see [`references/metadata-propagation.md`](references/metadata-propagation.md).

## 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:
```bash
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), use a check-first pattern — query `client.tables.get()` before creating. This is explicit, avoids masking unrelated errors, and lets you branch logic based on whether the table was created or reused:

```python
def ensure_table(client, schema_name, columns, solution, primary_column="prefix_Name", display_name=None):
    existing = client.tables.get(schema_name)
    if existing:
        print(f"Reusing: {schema_name}")
        return existing
    info = client.tables.create(schema_name, columns, solution=solution,
                                primary_column=primary_column, display_name=display_name)
    print(f"Created: {info['table_schema_name']}")
    return info
```

---

## Alternate Keys (Required for Upsert)

`UpsertMultiple` requires an alternate key on the column(s) Dataverse should use to identify existing records. Always create alternate keys on source-system ID columns (`prefix_Src*Id`) at schema-setup time so every import is idempotent.

**Quick reference:**
- SDK call: `client.tables.create_alternate_key(table, key_name, [columns], display_name=...)`. Composite keys: pass multiple columns.
- Use a check-first pattern with `client.tables.get_alternate_keys(table)` to skip keys that already exist — see `references/alternate-keys.md` for the `ensure_alternate_key` helper.
- Index creation is **async** — for large tables, poll `client.tables.get_alternate_keys(table)` until `status == "Active"` before using.
- Constraints: max 16 columns / 900 bytes / 10 keys per table; valid types are Integer / Decimal / String / DateTime / Lookup / OptionSet.

For SDK code samples (single + composite + idempotent + status-check), the agent decision rules for which column to pick (DB source vs Excel/CSV), and the failure-handling notes, see [`references/alternate-keys.md`](references/alternate-keys.md).

## 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:
```python
GET /api/data/v9.2/EntityDefinitions(LogicalName='new_projectbudget')?$select=LogicalName,EntitySetName
```

Or query all entities and filter in Python:
```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`.