@microsoft/dataverse-metadata

---
name: dataverse-metadata
description: >
  Create or modify Dataverse tables, columns, relationships, forms, and views.
  USE WHEN: "add column", "create table", "add relationship", "lookup column", "create form",
  "create view", "modify form", "FormXml", "SavedQuery", "option set", "picklist",
  "MetadataService", "EntityDefinitions".
  DO NOT USE WHEN: reading/writing data records (use dataverse-python-sdk),
  exporting solutions (use dataverse-solution).
---

# Skill: Metadata — Making Changes

**Before the first metadata change in a session, confirm the target environment with the user.** See the Multi-Environment Rule in the overview skill for the full confirmation flow.

---

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

**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
from auth import get_credential, load_env
from PowerPlatform.Dataverse.client import DataverseClient
import os

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

```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
```

---

## 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-reference \
  --environment <url> \
  --solution-unique-name <SOLUTION_NAME> \
  --entity <logical_name>
```

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

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

### Create a form

```python
# POST /api/data/v9.2/systemforms
import os, json, urllib.request
from auth import get_token, load_env

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

```python
# GET the form first, modify the XML, then PATCH it back
url = f"{env}/api/data/v9.2/systemforms?$filter=objecttypecode eq 'new_projectbudget' and type eq 2&$select=formid,name,formxml"
# ... parse response, modify formxml string, then:
# PATCH /api/data/v9.2/systemforms(<formid>)
# body: {"formxml": "<modified xml>"}
```

### Publish forms after create/modify

Forms must be published to take effect:
```python
body = {"ParameterXml": "<importexportxml><entities><entity>new_projectbudget</entity></entities></importexportxml>"}
# POST /api/data/v9.2/PublishXml
```

---

## Views

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

### Create a view

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

After creating tables or columns via the Web API, metadata propagation can take 3-10 seconds. 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"

**Mitigation:** Add a 3-5 second delay after table creation before adding columns. After creating lookup columns, wait 5-10 seconds before inserting records that use `@odata.bind` on those lookups. If a column creation fails, verify it doesn't already exist, then retry once.

---

## Session Closing: Pull to Repo

**After every metadata session, perform the pull-to-repo sequence.** This is not optional — work that exists only in the environment is lost if the environment is reset.

```bash
pac solution export --name <SOLUTION_NAME> --path ./solutions/<SOLUTION_NAME>.zip --managed false
pac solution unpack --zipfile ./solutions/<SOLUTION_NAME>.zip --folder ./solutions/<SOLUTION_NAME>
rm ./solutions/<SOLUTION_NAME>.zip
git add ./solutions/<SOLUTION_NAME>
git commit -m "feat: <description of change>"
```

If you used the `MSCRM.SolutionName` header during creation, verify components are in the solution before exporting:
```bash
pac solution list-components --solutionUniqueName <SOLUTION_NAME> --environment <url>
```

---

## 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`.