Update to Microsoft Dataverse

{
  "description": "Record-level CRUD and bulk operations — create, update, delete, upsert, CSV import, multi-table foreign-key loads, AI-generated sample data. Use when the user wants to write, modify, seed, or import data records into Dataverse tables.",
  "included_files": [
    {
      "relative_path": "references/erp-writes.md",
      "size_in_bytes": 2356
    },
    {
      "relative_path": "references/multi-table-fk-import.md",
      "size_in_bytes": 11259
    },
    {
      "relative_path": "references/sample-data-generation.md",
      "size_in_bytes": 6901
    }
  ],
  "name": "dv-data",
  "skill_md_contents": "---\r\nname: dv-data\r\ndescription: Record-level CRUD and bulk operations — create, update, delete, upsert, CSV import, multi-table foreign-key loads, AI-generated sample data. Use when the user wants to write, modify, seed, or import data records into Dataverse tables.\r\n---\r\n\r\n# Skill: Data — Create, Update, Delete, and Bulk Import\r\n\r\n> **This skill uses Python and the Dataverse CLI.** Do not use Node.js, JavaScript, or any other language for Dataverse scripting. If you are about to run `npm install` or write a `.js` file, STOP — you are going off-rails. See the overview skill's Hard Rules.\r\n\r\nUse the official Microsoft Power Platform Dataverse Client Python SDK for all data write operations.\r\n\r\n**Official SDK:** https://github.com/microsoft/PowerPlatform-DataverseClient-Python\r\n**PyPI package:** `PowerPlatform-Dataverse-Client` (this is the only official one — do not use `dataverse-api` or other unofficial packages)\r\n**Status:** GA (`1.0.0`, Production/Stable)\r\n\r\n## Skill boundaries\r\n\r\n| Need | Use instead |\r\n|---|---|\r\n| Query or read records | **dv-query** |\r\n| Create tables, columns, relationships, forms, views | **dv-metadata** |\r\n| Export or deploy solutions | **dv-solution** |\r\n| ERP writes | See [`references/erp-writes.md`](references/erp-writes.md) |\r\n\r\n---\r\n\r\n## Choosing MCP, CLI, or SDK for writes\r\n\r\n**CLI fast path:** If `dataverse auth who` shows an active profile, CLI commands (`data create/update/delete/upsert/associate/upload`) work immediately — no `.env`, `auth.py`, or pip needed. SDK and bulk operations still need workspace setup.\r\n\r\n**If MCP tools are available** (`create_record`, `update_record`, `delete_record`), they are the quickest path for a **small, interactive** set of writes — up to 25 records per call, no script needed. **The Dataverse CLI** (`dataverse data create/update/upsert/delete`) handles single-record writes, associate/disassociate, and file uploads as headless one-liners. **The SDK** is the default for bulk writes beyond 25, data transformation, retry logic, CSV import, or SDK-only operations (upsert with alternate keys — MCP has no upsert tool). Pick the surface that fits the volume and shape of the work.\r\n\r\n## When you script a write, use the SDK — not hand-rolled HTTP\r\n\r\nThe MCP/CLI/SDK choice is capability-based (above; and see the overview's **Tool Capabilities** / Hard Rule 2). This section is narrower: **once you've decided to write via a script**, use the SDK for anything in its \"supports\" list rather than hand-rolled `urllib`/`requests` — the SDK carries the auth, paging, and retry those re-implement. For the rare operation the SDK doesn't cover, use the `dataverse api` escape hatch — not hand-rolled `urllib`.\r\n\r\n**Correct import** (always preceded by `sys.path.insert` in a full script — see Setup below):\r\n```\r\nfrom auth import get_client\r\n```\r\n\r\n**WRONG for SDK-supported operations:**\r\n```\r\nfrom auth import get_token, load_env  # WRONG for SDK-supported ops\r\nimport requests                        # WRONG for SDK-supported ops\r\n```\r\n\r\n`get_token()` and `requests` exist ONLY for genuine gaps with no managed path (global option sets, unbound actions) — and even then prefer the managed `dataverse api` escape hatch. Forms/views, aggregation, and N:N reads are all covered by the SDK; see **dv-query** and **dv-metadata**.\r\n\r\n---\r\n\r\n## What This SDK Supports (Data Operations)\r\n\r\n- Record writes: create, update, delete\r\n- Record reads within write workflows (e.g., lookup resolution) — for standalone queries see **dv-query**\r\n- Upsert (with alternate key support)\r\n- Bulk operations: `CreateMultiple`, `UpdateMultiple`, `UpsertMultiple`\r\n- File column uploads (chunked for files >128MB)\r\n- Context manager with HTTP connection pooling\r\n\r\n## What This SDK Does NOT Support\r\n\r\nForms/views (`systemform`/`savedquery`) **are** ordinary records — create/modify them with `client.records.*` (see **dv-metadata**), and read N:N with `records.list(expand=...)`. For the genuine gaps below, prefer the managed `dataverse api` escape hatch over raw `urllib`:\r\n- Global option sets — see **dv-metadata**\r\n- N:N record association — CLI `dataverse data associate`, or `POST /api/data/v9.2/<entity>(<id>)/<nav-property>/$ref`\r\n- `$apply` aggregation — use `client.query.fetchxml()`; see **dv-query**\r\n- Unbound actions (e.g., `PublishXml`, `InstallSampleData`) — `dataverse api request`/`invoke`\r\n- DeleteMultiple, general OData batching\r\n\r\n### Dataverse CLI data examples (copy-paste ready)\r\n\r\nAll `dataverse` commands take `--context` for skill attribution (global flag).\r\n\r\n```bash\r\n# Create a record (--table is the EntitySet name)\r\ndataverse data create --table accounts --data '{\"name\":\"Contoso\"}' --return --json --context \"app=dataverse-skills/<ver>;skill=dv-data;agent=<agent>\"\r\n\r\n# Update by GUID\r\ndataverse data update --table accounts --id <guid> --data '{\"name\":\"Contoso (updated)\"}' --json --context \"app=dataverse-skills/<ver>;skill=dv-data;agent=<agent>\"\r\n\r\n# Upsert by alternate key (idempotent — safe to re-run)\r\ndataverse data upsert --table accounts --key \"accountnumber='ACC-001'\" --data '{\"name\":\"Contoso Ltd\"}' --json --context \"app=dataverse-skills/<ver>;skill=dv-data;agent=<agent>\"\r\n\r\n# Delete (--no-confirm skips the prompt)\r\ndataverse data delete --table accounts --id <guid> --no-confirm --context \"app=dataverse-skills/<ver>;skill=dv-data;agent=<agent>\"\r\n\r\n# Associate two records (N:N or lookup)\r\ndataverse data associate --table accounts --id <guid> --relationship contact_customer_accounts --related contacts --related-id <contact-guid> --context \"app=dataverse-skills/<ver>;skill=dv-data;agent=<agent>\"\r\n\r\n# Disassociate (N:N — pass --related-id; clear a lookup — omit --related-id)\r\ndataverse data disassociate --table accounts --id <guid> --relationship contact_customer_accounts --related-id <contact-guid> --context \"app=dataverse-skills/<ver>;skill=dv-data;agent=<agent>\"\r\n\r\n# Upload a file to a file column (--table takes LogicalName, not EntitySet)\r\ndataverse data upload --table account --id <guid> --column new_document --file report.pdf --context \"app=dataverse-skills/<ver>;skill=dv-data;agent=<agent>\"\r\n\r\n# Describe entity schema (attributes, relationships, actions)\r\ndataverse data describe --table account --include all --json --context \"app=dataverse-skills/<ver>;skill=dv-data;agent=<agent>\"\r\n\r\n# Invoke a discovered custom API by name (use 'api list' to find names)\r\ndataverse api invoke <CustomApiName> --target dataverse --param Input=value --context \"app=dataverse-skills/<ver>;skill=dv-data;agent=<agent>\"\r\n\r\n# Raw API escape hatch for built-in actions (--target is required)\r\ndataverse api request --target dataverse --path \"/api/data/v9.2/WhoAmI\" --context \"app=dataverse-skills/<ver>;skill=dv-data;agent=<agent>\"\r\n```\r\n\r\n---\r\n\r\n## Setup\r\n\r\n```python\r\nimport os, sys\r\nsys.path.insert(0, os.path.join(os.getcwd(), \"scripts\"))\r\nfrom auth import get_client\r\n\r\n# get_client sets a plugin attribution context on the User-Agent header.\r\n# Do not modify the context value — it is a closed schema for server-side\r\n# telemetry (app/skill/agent). Never include secrets or PII.\r\nclient = get_client(\"dv-data\")\r\n```\r\n\r\n`get_client(skill)` handles auth, environment URL, and plugin attribution (User-Agent tagging). See `scripts/auth.py`.\r\n\r\nFor scripts that run to completion: wrap in `with DataverseClient(...) as client:` for automatic connection cleanup (recommended). For notebooks and interactive sessions, the explicit client above is simpler.\r\n\r\n---\r\n\r\n## Field Name Casing Rule\r\n\r\nGetting this wrong causes 400 errors.\r\n\r\n| Property type | Convention | Example | When used |\r\n|---|---|---|---|\r\n| **Structural** (columns) | LogicalName — always lowercase | `new_name`, `new_priority` | Record payload keys |\r\n| **Navigation** (lookups) | Navigation Property Name — case-sensitive, matches `$metadata` | `new_AccountId` | `@odata.bind` keys |\r\n\r\nThe SDK lowercases structural keys automatically but preserves `@odata.bind` key casing.\r\n\r\n---\r\n\r\n## Create a Record\r\n\r\n```python\r\nguid = client.records.create(\"new_ticket\", {\r\n    \"new_name\": \"Ticket 001\",\r\n    \"new_priority\": 100000002,          # choice column — integer value, not string\r\n    \"new_AccountId@odata.bind\": \"/accounts(<account-guid>)\",\r\n})\r\nprint(f\"Created: {guid}\")\r\n```\r\n\r\n**`@odata.bind` notes:**\r\n- Key is the Navigation Property Name: `new_AccountId@odata.bind` (the SDK preserves casing automatically, but matching the schema name is still the correct form)\r\n- Value is `\"/<EntitySetName>(<guid>)\"` — e.g., `\"/accounts(<guid>)\"`\r\n- If you just created the lookup column, wait 5–10 seconds before inserting. Metadata propagation delays cause \"Invalid property\" errors.\r\n- Choice columns use integer values, not strings: `\"new_priority\": 100000002` (not `\"High\"`)\r\n\r\n### Common `@odata.bind` patterns\r\n\r\n| Lookup | Correct key | Wrong |\r\n|---|---|---|\r\n| Custom: `new_AccountId` | `new_AccountId@odata.bind` | ~~`new_accountid@odata.bind`~~ |\r\n| System polymorphic: `customerid` | `customerid_account@odata.bind` | ~~`customerid@odata.bind`~~ |\r\n| System: `parentcustomerid` | `parentcustomerid_account@odata.bind` | ~~`_parentcustomerid_value@odata.bind`~~ |\r\n\r\n### Find the Navigation Property Name\r\n\r\nAfter creating a lookup via SDK: `result.lookup_schema_name` is the navigation property name.\r\n\r\nFor existing system tables, query:\r\n```\r\nGET /api/data/v9.2/EntityDefinitions(LogicalName='<entity>')/ManyToOneRelationships\r\n  ?$select=ReferencingEntityNavigationPropertyName,ReferencedEntity\r\n```\r\n\r\n---\r\n\r\n## Update a Record\r\n\r\n```python\r\nclient.records.update(\"new_ticket\", \"<record-guid>\",\r\n    {\"new_status\": 100000001})\r\n```\r\n\r\n---\r\n\r\n## Delete a Record\r\n\r\n```python\r\nclient.records.delete(\"new_ticket\", \"<record-guid>\")\r\n```\r\n\r\n---\r\n\r\n## Bulk Create (SDK uses CreateMultiple internally)\r\n\r\n```python\r\nrecords = [{\"new_name\": f\"Ticket {i}\", \"new_priority\": 100000000} for i in range(500)]\r\nguids = client.records.create(\"new_ticket\", records)\r\nprint(f\"Created {len(guids)} records\")\r\n```\r\n\r\nVolume guidance: CLI `dataverse data create` for one-off records. MCP `create_record` batches up to 25 per call. SDK `CreateMultiple` for larger bulk.\r\n\r\n**Important:** The SDK sends all records in a single POST to `CreateMultiple`. It does **not** chunk automatically. Dataverse has no fixed record count limit — the constraints are payload size and request timeout (SDK default: 120s for POST). For larger datasets, you **must** chunk in your script. The `bulk_upsert` and `bulk_create` helpers below use adaptive chunking: start at 1,000, double on success (up to 4,000), halve on payload/timeout failure, and cap at the last successful size. Tables with few columns can handle larger chunks than tables with many columns.\r\n\r\n---\r\n\r\n## Bulk Update\r\n\r\n```python\r\n# Broadcast same change to multiple records\r\nclient.records.update(\"new_ticket\",\r\n    [id1, id2, id3],\r\n    {\"new_status\": 100000001})\r\n```\r\n\r\n---\r\n\r\n## DataFrame Write-Back\r\n\r\nTo create or update records from a pandas DataFrame, use the `client.dataframe` namespace (`create`/`update`). This is documented in **dv-query** but is a write operation — include it in your data write workflow:\r\n\r\n```python\r\n# Update records — DataFrame must include the primary key column\r\nclient.dataframe.update(\"opportunity\", df_updates, id_column=\"opportunityid\")\r\n\r\n# Create records — returns a Series of new GUIDs\r\nguids = client.dataframe.create(\"opportunity\", df_new_records)\r\n```\r\n\r\nSee **dv-query** for the full `client.dataframe` write reference; for reads use `client.query.builder(...).execute().to_dataframe()`.\r\n\r\n---\r\n\r\n## Upsert (Alternate Keys)\r\n\r\nIdempotent — re-running the same import does not create duplicates. The alternate key must be defined on the table first — see **dv-metadata**.\r\n\r\n**Do NOT include alternate key columns in the record body.** The alternate key identifies the record; the record body contains the data to set. If the same column appears in both, `UpsertMultiple` fails with \"An unexpected error occurred\" (single upsert tolerates it, bulk does not).\r\n\r\n```python\r\nfrom PowerPlatform.Dataverse.models.upsert import UpsertItem\r\n\r\nclient.records.upsert(\"account\", [\r\n    UpsertItem(\r\n        alternate_key={\"accountnumber\": \"ACC-001\"},\r\n        record={\"name\": \"Contoso Ltd\", \"description\": \"Primary account\"},\r\n    ),\r\n    UpsertItem(\r\n        alternate_key={\"accountnumber\": \"ACC-002\"},\r\n        record={\"name\": \"Fabrikam Inc\"},\r\n    ),\r\n])\r\n```\r\n\r\n---\r\n\r\n## Bulk Import from CSV\r\n\r\n> **For imports that may be re-run** (most real-world cases), use `UpsertItem` with alternate keys instead of `create()` — see [`references/multi-table-fk-import.md`](references/multi-table-fk-import.md). The `create()` pattern here is for one-shot loads only.\r\n\r\n| Volume | Tool | Why |\r\n|---|---|---|\r\n| 1 record | CLI `dataverse data create` or MCP `create_record` | No script needed |\r\n| 2–25 records | MCP `create_record` | Batches up to 25 per call |\r\n| 25+ records | SDK `client.records.create(table, list)` | Uses CreateMultiple; chunk large datasets (start at 1K, adapt) |\r\n\r\n```python\r\nimport csv, os, sys\r\nsys.path.insert(0, os.path.join(os.getcwd(), \"scripts\"))\r\nfrom auth import get_client\r\n\r\n# get_client sets a plugin attribution context on the User-Agent header.\r\n# Do not modify the context value — it is a closed schema for server-side\r\n# telemetry (app/skill/agent). Never include secrets or PII.\r\nclient = get_client(\"dv-data\")\r\n\r\nwith open(\"data/customers.csv\", newline=\"\", encoding=\"utf-8\") as f:\r\n    rows = list(csv.DictReader(f))\r\n\r\nrecords = [{\"new_name\": row[\"name\"], \"new_email\": row[\"email\"]} for row in rows]\r\n\r\n# SDK sends all in one POST — chunk to avoid payload/timeout limits\r\n# Start at 1000; for narrow tables (few columns) you can go higher\r\nchunk_size = 1000\r\nfor i in range(0, len(records), chunk_size):\r\n    guids = client.records.create(\"new_customer\", records[i:i + chunk_size])\r\n    print(f\"Imported {i + len(guids)}/{len(records)} customers\", flush=True)\r\n```\r\n\r\n### Lookup resolution during import\r\n\r\nIf the CSV has a human-readable key (e.g., `customer_email`) but Dataverse needs a GUID, pre-resolve with a lookup dict:\r\n\r\n```python\r\n# Build email -> GUID map first\r\nemail_to_guid = {}\r\nfor r in client.records.list(\"new_customer\", select=[\"new_customerid\", \"new_email\"]):\r\n    email_to_guid[r[\"new_email\"]] = r[\"new_customerid\"]\r\n\r\n# Use it during import\r\nrecords = []\r\nfor row in rows:\r\n    customer_guid = email_to_guid.get(row[\"customer_email\"])\r\n    if not customer_guid:\r\n        print(f\"Skipping row — unknown email: {row['customer_email']}\")\r\n        continue\r\n    records.append({\r\n        \"new_channel\": row[\"channel\"],\r\n        \"new_CustomerId@odata.bind\": f\"/new_customers({customer_guid})\",  # verify entity set name via EntityDefinitions\r\n    })\r\n\r\nguids = client.records.create(\"new_interaction\", records)\r\n```\r\n\r\n### Required field discovery for system tables\r\n\r\nBefore bulk-creating in a system table (account, contact, opportunity):\r\n1. Create a single test record with your intended minimal payload\r\n2. If `HttpError` 400 is raised, the error message names the missing required field\r\n3. Some required fields are plugin-enforced and not visible in `describe`\r\n4. Delete the test record, then proceed with bulk create\r\n\r\n---\r\n\r\n## Multi-Table Import with FK Dependencies\r\n\r\nWhen importing data across multiple tables with foreign key relationships, the import must run in dependency order with `UpsertItem` + alternate keys (idempotent, safe for re-runs).\r\n\r\n**Quick reference:**\r\n1. Create tables with source ID columns + alternate keys + lookup relationships (see **dv-metadata**).\r\n2. Import Level 0 (no FK deps) tables in parallel via `ThreadPoolExecutor`. Sequential chunks within each table (concurrent writes deadlock).\r\n3. Build source-ID → GUID maps by querying back (upsert doesn't return GUIDs).\r\n4. Repeat per dependency level — Level 1 needs Level 0's maps for `@odata.bind`.\r\n\r\nFor the full pattern — adaptive `bulk_upsert` helper, composite-key handling, post-import verification, and the first-time `bulk_create` variant — see [`references/multi-table-fk-import.md`](references/multi-table-fk-import.md).\r\n\r\nKey invariants (apply even without reading the reference):\r\n\r\n- **Parallelize across tables at the same level**, sequential between levels, sequential chunks within a table.\r\n- **Alternate key columns must NOT also appear in the record body** — `UpsertMultiple` fails.\r\n- **Catch per-table failures** in the executor — one table failing must not kill the others.\r\n- Start `chunk_size=1000`; the helper ramps up adaptively.\r\n\r\n## Error Handling\r\n\r\n```python\r\nfrom PowerPlatform.Dataverse.core.errors import HttpError\r\n\r\ntry:\r\n    guid = client.records.create(\"new_ticket\", {\"new_name\": \"Test\"})\r\nexcept HttpError as e:\r\n    print(f\"Status {e.status_code}: {e.message}\")\r\n    if e.details:\r\n        print(f\"Details: {e.details}\")\r\n    # 400 — bad field name, @odata.bind format, or missing required field\r\n    # 403 — check security roles\r\n    # 404 — table or record not found\r\n    # 429 — rate limited; SDK retries automatically, reduce batch size if persistent\r\n```\r\n\r\n---\r\n\r\n## Writing ERP data\r\n\r\nOn ERP-linked envs, writes to ERP entities do not go through the Python SDK. See [`references/erp-writes.md`](references/erp-writes.md).\r\n\r\n---\r\n\r\n## Windows Scripting Notes\r\n\r\n- **ASCII only** in `.py` files — curly quotes and em dashes cause `SyntaxError` on Windows.\r\n- **No `python -c` for multiline code** — write a `.py` file instead.\r\n- **Generate GUIDs in scripts**: `str(uuid.uuid4())`, not shell backtick substitution.\r\n\r\n---\r\n\r\n## Sample Data Generation\r\n\r\nGenerate realistic sample records inline — schema-driven, table-agnostic, PII-safe defaults (`@example.com` emails, `555-01xx` phones).\r\n\r\n**Quick reference:** confirm environment + count + table → query `EntityDefinitions(LogicalName='<table>')/Attributes?$filter=AttributeOf eq null` for required columns → dispatch by `AttributeType` (String / Memo / Integer / DateTime / Picklist / etc.) → `client.records.create()` (use `CreateMultiple` for count >= 10).\r\n\r\nFor the schema-driven `fake()` template, the `EntityDefinitions` query, and the safety rules, see [`references/sample-data-generation.md`](references/sample-data-generation.md).\r\n\r\nKey invariants:\r\n- Skip Lookup, Uniqueidentifier, State, Status, Owner, Customer fields unless the user explicitly provides values.\r\n- `UserLocalizedLabel` may be null — dereference safely.\r\n\r\n### Confirmation-flow examples\r\n\r\n**Generate N sample records (destructive — preview the snippet, ask for env):**\r\n- ❌ \"Which environment should I target? Please provide the Dataverse URL.\"\r\n- ✅ \"I'll run the Sample Data Generation snippets with `TABLE=\\\"contact\\\"`, `COUNT=20`. Uses `CreateMultiple`, `.example.com` emails, `555-01xx` phones, against the active `pac auth list` environment. Confirm to proceed, or specify a different environment.\"\r\n\r\n**Sample data on a custom entity (schema unknown — prose is enough):**\r\n- ❌ \"I need more info about the entity. What are the required fields?\"\r\n- ✅ \"Custom entity — I'll query `EntityDefinitions` for `cr123_project` to discover required columns, then generate 5 records inline mapping each column to a generator by `AttributeType` and call `client.records.create(\\\"cr123_project\\\", records)`. Confirm to proceed, or tell me a different count.\"\r\n"
}