Update to Microsoft Dataverse

{
  "description": "Bulk reads, multi-page iteration, and analytics over Dataverse data. Use when the user wants to read, list, filter, aggregate, group, join, or analyze records — including pandas DataFrame workflows and notebook exploration.",
  "included_files": [
    {
      "relative_path": "references/erp-reads.md",
      "size_in_bytes": 2829
    },
    {
      "relative_path": "references/jupyter-setup.md",
      "size_in_bytes": 1125
    },
    {
      "relative_path": "references/querybuilder.md",
      "size_in_bytes": 5155
    },
    {
      "relative_path": "references/web-api-advanced.md",
      "size_in_bytes": 6392
    }
  ],
  "name": "dv-query",
  "skill_md_contents": "---\r\nname: dv-query\r\ndescription: Bulk reads, multi-page iteration, and analytics over Dataverse data. Use when the user wants to read, list, filter, aggregate, group, join, or analyze records — including pandas DataFrame workflows and notebook exploration.\r\n---\r\n\r\n# Skill: Query — Read and Analyze Dataverse Records\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. See the overview skill's Hard Rules.\r\n\r\n## Reads: prefer a managed surface, choose by shape\r\n\r\n**Fast path for simple reads:** If `dataverse auth who` shows an active profile, skip workspace setup and query directly with the CLI examples below. No `.env`, `auth.py`, pip install, or PAC needed for data reads.\r\n\r\nPick **MCP, the Dataverse CLI, or the SDK by the shape of the read** — all three handle auth and retry (see the routing table below and the overview's **Tool Capabilities** / Hard Rule 2). MCP fits small, interactive reads; the CLI fits headless one-liners (OData, SQL, count); the SDK fits bulk iteration and analytics. For `$apply` aggregation and N:N `$expand`, prefer `client.query.fetchxml()` (aggregates + link-entity) or the managed `dataverse api` escape hatch; reach for hand-rolled `urllib`/`get_token()` **only** to stay in-process inside a tight Python loop (e.g. paging thousands of rows with client-side processing — see web-api-advanced.md).\r\n\r\n### Dataverse CLI gotchas (custom tables + Windows)\r\n\r\nWhen you drive the `dataverse` CLI directly (headless reads/CRUD; note the CLI needs .NET + a keyring, so it is blocked on ChatGPT web / Codex cloud — use the SDK there), two empirical traps:\r\n\r\n- **Custom-table SQL pluralization.** `dataverse data query` in SQL mode auto-pluralizes the table name, and irregular plurals resolve wrong: `FROM im_category` looks up entity set `im_categorys` and returns a **404** that reads like \"table missing.\" It is not — switch to OData mode with the explicit entity set: `dataverse data query --table im_categories --select im_name`. Discover the real `EntitySetName` from `EntityDefinitions` when unsure; never conclude the table doesn't exist from this 404.\r\n- **Windows shell quoting.** Wrap the whole `--path` value in double quotes so `cmd.exe`/PowerShell don't treat `&` as a command separator. Keep `&` **literal** — it separates OData query options; encoding it to `%26` merges them and breaks the query. Encode only `$`->`%24` (in PowerShell a bare `$select` is read as a variable). If an unquoted `&` splits the command, the wrapper can exit nonzero *even when the API returned valid JSON* — quoting prevents it. (This is why the `dataverse api request` examples in other skills quote the path, use `%24`, and leave `&` literal.)\r\n\r\n### Dataverse CLI query examples (copy-paste ready)\r\n\r\nAll `dataverse` commands take `--context` for skill attribution (global flag).\r\n\r\n```bash\r\n# OData filtered read (--table takes the EntitySet name, e.g. accounts not account)\r\ndataverse data query --table accounts --select \"name,accountid\" --filter \"name eq 'john'\" --top 10 --json --context \"app=dataverse-skills/<ver>;skill=dv-query;agent=<agent>\"\r\n\r\n# Count records\r\ndataverse data count --table accounts --context \"app=dataverse-skills/<ver>;skill=dv-query;agent=<agent>\"\r\n\r\n# SQL mode (uses the logical name, e.g. account not accounts)\r\ndataverse data query --sql \"SELECT name, accountid FROM account WHERE name LIKE '%john%'\" --json --context \"app=dataverse-skills/<ver>;skill=dv-query;agent=<agent>\"\r\n\r\n# Get single record by ID\r\ndataverse data get --table accounts --id <guid> --json --context \"app=dataverse-skills/<ver>;skill=dv-query;agent=<agent>\"\r\n\r\n# Raw API escape hatch\r\ndataverse api request --target dataverse --path \"/api/data/v9.2/accounts?%24select=name&%24top=5\" --context \"app=dataverse-skills/<ver>;skill=dv-query;agent=<agent>\"\r\n```\r\n\r\n**ERP target is a separate path.** ERP (Finance and Operations), when linked to a Dataverse env, does not go through the Python SDK. See [`references/erp-reads.md`](references/erp-reads.md).\r\n\r\n## How to Answer Data Questions\r\n\r\nWhen the user asks a question about their data, pick the approach by **what they're asking**, not by which API you know:\r\n\r\n| User asks... | Approach | Why |\r\n|---|---|---|\r\n| \"show me open tickets\" / simple filter | **MCP** `read_query`, **CLI** `dataverse data query --table ... --filter ...`, or `client.records.list(table, filter=...)` | Small result, no aggregation |\r\n| \"how many X\" / simple count | **CLI** `dataverse data count --table ...`, **MCP** `read_query`, or `client.query.sql(\"SELECT COUNT(*) ...\")` | Server-side count (no row download) |\r\n| Single-table aggregation (most/sum/avg/top-N) | **`$apply`** (raw) or **`client.query.sql()`** GROUP BY | Both run server-side, return only grouped results |\r\n| Cross-table aggregation | **`client.query.sql(\"...INNER JOIN...GROUP BY...\")`** or **`client.query.fetchxml(...)`** (server-side); else builder->DataFrame + `pd.merge()` | `sql()` supports INNER/LEFT JOIN + GROUP BY; pandas merge for shapes SQL can't express |\r\n| \"show me X with related Y\" / resolve lookups | `client.records.list(table, expand=...)` or **QueryBuilder** | Lookup resolution |\r\n| \"export this data\" / bulk extract | **`client.query.builder(t).select(...).execute().to_dataframe()`** | Direct to DataFrame → CSV |\r\n| \"load into notebook\" / interactive analysis | **`client.query.builder(t).select(...).execute().to_dataframe()`** | pandas native |\r\n| \"find duplicates\" / complex filter | `client.records.list(table, filter=...)` or **QueryBuilder** | SDK handles pagination |\r\n| Simple filtered read (<5K rows) | **CLI** `dataverse data query --sql \"SELECT ...\"`, or **`client.query.sql()`** | Lightweight single call |\r\n\r\n**Key principle:** Let the server do the work. For single-table aggregation, use `$apply` (raw) or `client.query.sql()` GROUP BY — both run server-side and return only grouped results. For cross-table questions, prefer a server-side `sql()` JOIN (INNER/LEFT) or `fetchxml()` link-entity; when SQL can't express it, pull each table via `client.query.builder(t).select(...).execute().to_dataframe()` and `pd.merge()` — the merge is sub-second; the bottleneck is network transfer, which `select` minimizes.\r\n\r\n**Always query the live Dataverse environment.** Do not query local copies, cached files, or source databases when the user expects results from Dataverse. The data in Dataverse is the source of truth.\r\n\r\n---\r\n\r\n## SQL Queries — `client.query.sql()`\r\n\r\n`client.query.sql()` uses the Dataverse Web API `?sql=` parameter — a **T-SQL subset**. It **supports** `SELECT` / `SELECT DISTINCT` / `SELECT TOP N` (0-5000), `INNER JOIN` / `LEFT JOIN`, `WHERE`, `GROUP BY`, `ORDER BY`, `OFFSET`/`FETCH`, and `COUNT/SUM/AVG/MIN/MAX`. It does **NOT** support `SELECT *`, subqueries, CTEs, `HAVING`, `UNION`, `RIGHT`/`FULL`/`CROSS JOIN`, `CASE`, or string/date/math functions. Results are capped at ~5,000 rows.\r\n\r\n**When to use:** Fast filtered reads on tables with <5K rows. For these, it's significantly faster (~2-6s) than page iteration or DataFrames because it's a single HTTP call.\r\n\r\n```python\r\n# Fast filtered read on small tables (<5K rows)\r\nresults = client.query.sql(\r\n    \"SELECT TOP 100 name, estimatedvalue \"\r\n    \"FROM opportunity \"\r\n    \"WHERE statecode = 0 \"\r\n    \"ORDER BY estimatedvalue DESC\"\r\n)\r\nfor r in results:\r\n    print(f\"{r['name']}: ${r.get('estimatedvalue', 0):,.0f}\")\r\n```\r\n\r\n**Do NOT use for:** Tables >5K rows (results silently truncated), `SELECT *`, subqueries/CTEs, `HAVING`, `UNION`, `RIGHT`/`FULL`/`CROSS JOIN`, or functions. `INNER`/`LEFT JOIN` and `GROUP BY` **are** supported — use them for server-side joins/aggregation on <5K-row results; for larger or unsupported shapes use `fetchxml()` or `$apply`.\r\n\r\n## FetchXML — server-side joins and aggregates\r\n\r\nFor SQL-JOIN scenarios or aggregates the OData builder cannot express, use FetchXML. `client.query.fetchxml(xml)` returns an inert query object — no HTTP is made until you call `.execute()` (eager, all pages) or `.execute_pages()` (lazy, one page at a time). Both return `QueryResult` pages with `.to_dataframe()`.\r\n\r\n```python\r\nquery = client.query.fetchxml(\"\"\"\r\n  <fetch top=\"50\">\r\n    <entity name=\"account\">\r\n      <attribute name=\"name\" />\r\n      <link-entity name=\"contact\" from=\"parentcustomerid\" to=\"accountid\" alias=\"c\" link-type=\"inner\">\r\n        <attribute name=\"fullname\" />\r\n      </link-entity>\r\n    </entity>\r\n  </fetch>\r\n\"\"\")\r\n\r\nresult = query.execute()          # collect all pages\r\ndf = result.to_dataframe()\r\n\r\n# Or stream one page at a time for large results:\r\nfor page in query.execute_pages():\r\n    print(page.to_dataframe().shape)\r\n```\r\n\r\n## Discover queryable columns — `client.query.sql_columns()`\r\n\r\nBefore writing a SQL or `$select` read, list the columns the SQL endpoint can actually query — virtual and computed lookup-display columns are excluded. Each entry has `name`, `type`, `is_pk`, `is_name`, and `label`.\r\n\r\n```python\r\nfor c in client.query.sql_columns(\"account\"):\r\n    print(f\"{c['name']:30s} {c['type']:20s} PK={c['is_pk']}\")\r\n```\r\n\r\nFor deeper schema inspection — full column metadata and table relationships — use `dv-metadata`\r\n(`client.tables.list_columns()`, `client.tables.list_relationships()`,\r\n`client.tables.list_table_relationships()`).\r\n\r\n## Skill boundaries\r\n\r\n| Need | Use instead |\r\n|---|---|\r\n| Create, update, delete records (Dataverse) | **dv-data** |\r\n| Query, create, update, delete records (ERP) | See [`references/erp-reads.md`](references/erp-reads.md) and [`erp-writes`](../dv-data/references/erp-writes.md) |\r\n| Create tables, columns, relationships | **dv-metadata** |\r\n| Export or deploy solutions | **dv-solution** |\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-query\")\r\n```\r\n\r\n`get_client(skill)` handles auth, environment URL, and plugin attribution (User-Agent tagging). See `scripts/auth.py`. For scripts that run to completion, wrap the returned client in a `with` statement for automatic connection cleanup. For ERP, use ERP MCP or the Dataverse CLI `--target erp` path — see [`references/erp-reads.md`](references/erp-reads.md).\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` | `$select`, `$filter`, `$orderby` |\r\n| **Navigation** (lookups) | Navigation Property Name — case-sensitive, matches `$metadata` | `new_AccountId` | `$expand` |\r\n\r\n- System table navigation properties (e.g., `parentaccountid`, `ownerid`): lowercase\r\n- Custom lookup navigation properties: case-sensitive, match `$metadata` SchemaName (e.g., `new_AccountId`)\r\n\r\n---\r\n\r\n## Query Records\r\n\r\n`client.records.list()` is the primary read method on the GA SDK. It collects all pages and returns a flat `QueryResult` you iterate directly (records, not pages). For very large result sets, `client.records.list_pages()` streams one `QueryResult` per HTTP page. **Always use `select=` to limit columns.**\r\n\r\n```python\r\n# list() -- flat QueryResult, iterate records directly\r\nresult = client.records.list(\r\n    \"new_ticket\",\r\n    select=[\"new_name\", \"new_priority\", \"new_status\"],\r\n    filter=\"new_status eq 100000000\",\r\n    orderby=[\"new_name asc\"],\r\n    top=50,\r\n)\r\nfor r in result:\r\n    print(r[\"new_name\"], r[\"new_priority\"])\r\n\r\nprint(f\"{len(result)} tickets\")   # QueryResult supports len(), indexing, .first(), .to_dataframe()\r\n```\r\n\r\nFor large tables where you do not want every row in memory at once, stream pages:\r\n\r\n```python\r\nfor page in client.records.list_pages(\"new_ticket\", select=[\"new_name\"], page_size=200):\r\n    for r in page:          # each page is a QueryResult\r\n        print(r[\"new_name\"])\r\n```\r\n\r\nEach record is a `Record` object that supports dict-like access: `r[\"column\"]`, `r.get(\"column\")`, `r.keys()`. Do not use `r.data.get()` -- use `r.get()` directly.\r\n\r\n> **Migrating from `records.get()`:** `records.get()` is deprecated on the GA SDK. Replace `for page in client.records.get(...): for r in page:` with `for r in client.records.list(...):` (flat), or keep the page loop using `list_pages(...)`. Replace a by-GUID `records.get(table, guid)` with `records.retrieve(table, guid)` (returns `None` if not found).\r\n\r\n---\r\n\r\n## Fetch a Single Record by ID\r\n\r\n`client.records.retrieve()` returns the record, or `None` if no row has that GUID (no exception on 404).\r\n\r\n```python\r\nrecord = client.records.retrieve(\"new_ticket\", \"<record-guid>\",\r\n    select=[\"new_name\", \"new_priority\", \"new_status\"])\r\nif record is not None:\r\n    print(record[\"new_name\"])\r\nelse:\r\n    print(\"Ticket not found\")\r\n```\r\n\r\n---\r\n\r\n## $select with Lookup Columns (GUID-free display)\r\n\r\nTo show display names instead of GUIDs, request the formatted value annotation via `include_annotations`:\r\n\r\n```python\r\nfor r in client.records.list(\"opportunity\",\r\n    select=[\"name\", \"estimatedvalue\", \"_parentaccountid_value\"],\r\n    include_annotations=\"OData.Community.Display.V1.FormattedValue\",\r\n):\r\n    account_name = r.get(\"_parentaccountid_value@OData.Community.Display.V1.FormattedValue\")\r\n    print(f\"{r['name']} — {account_name}\")\r\n```\r\n\r\n**You MUST pass `include_annotations`** — without it, the `Prefer: odata.include-annotations` header is not sent and formatted values are not in the response. Use `\"*\"` for all annotations or the specific annotation name above.\r\n\r\nFormatted values are available for lookup, choice, status, and owner fields.\r\n\r\n---\r\n\r\n## $expand — Resolve Lookup to Full Related Record\r\n\r\n```python\r\nfor r in client.records.list(\"opportunity\",\r\n    select=[\"name\", \"estimatedvalue\"],\r\n    expand=[\"parentaccountid($select=name)\"],   # nested $select avoids fetching all account columns\r\n):\r\n    account = r.get(\"parentaccountid\") or {}\r\n    print(f\"{r['name']} — {account.get('name', 'Unknown')}\")\r\n```\r\n\r\nAlways use nested `$select` inside `$expand` — without it, Dataverse returns every column on the related entity, which wastes bandwidth and memory.\r\n\r\n### $expand with multiple custom lookups\r\n\r\n```python\r\nfor r in client.records.list(\r\n    \"new_ticket\",\r\n    select=[\"new_name\", \"new_priority\", \"new_status\"],\r\n    expand=[\"new_CustomerId($select=new_name)\", \"new_AgentId($select=new_name)\"],  # nested $select + case-sensitive nav props\r\n):\r\n    customer = r.get(\"new_CustomerId\") or {}\r\n    agent    = r.get(\"new_AgentId\") or {}\r\n    print(f\"{r['new_name']} | {customer.get('new_name','')} | {agent.get('new_name','')}\")\r\n```\r\n\r\n> `expand` uses the Navigation Property Name (`new_CustomerId`), not the lowercase logical name (`new_customerid`). Using lowercase causes a 400 error.\r\n\r\n---\r\n\r\n## Advanced query patterns (raw Web API)\r\n\r\n`$apply` aggregation and N:N `$expand` on the OData path are raw-only. Note the SDK **does** cover most aggregation/joins — `client.query.sql()` (INNER/LEFT JOIN, GROUP BY, COUNT/SUM/AVG) and `client.query.fetchxml()` (aggregate + link-entity). Reach for raw Web API only for the `$apply` transform and N:N `$expand`. See [`references/web-api-advanced.md`](references/web-api-advanced.md) for full code samples.\r\n\r\n**Quick reference:**\r\n- **`$expand` on N:N relationships:** `GET /<entitySet>?$expand=<n:n_nav>($select=...)` — single page only; follow `@odata.nextLink` for >5,000 results.\r\n- **`$apply` for aggregations:** runs server-side, returns grouped results in one call. Patterns: `groupby((col),aggregate(metric with sum as total))`, `aggregate($count as count)`, `aggregate(amount with average as avg)`. 50K source-record limit.\r\n- **Cross-table aggregation:** `$apply` only works within one entity set. Prefer `client.query.sql()` (INNER/LEFT JOIN + GROUP BY) or `fetchxml()` link-entity; else pull each table via `client.query.builder(t).select(...).execute().to_dataframe()` → `pd.merge()` → `groupby()`. Always pass `select`; without it transfers 10-20x more data.\r\n\r\n## QueryBuilder — Fluent Query API\r\n\r\nChainable builder for complex queries that would be awkward as a single OData URL or FetchXML string. Full reference and examples in [`references/querybuilder.md`](references/querybuilder.md).\r\n\r\n## Jupyter Notebook Setup\r\n\r\nFor interactive querying in notebooks (auth + DataverseClient + DataFrame display), see [`references/jupyter-setup.md`](references/jupyter-setup.md).\r\n\r\n## Querying ERP data\r\n\r\nOn ERP-linked envs, ERP reads do not go through `DataverseClient`. Use ERP MCP or `dataverse data query/get/count --target erp`. See [`references/erp-reads.md`](references/erp-reads.md).\r\n\r\n## Common Query Errors\r\n\r\n| Status | Cause | Fix |\r\n|---|---|---|\r\n| 400 | Wrong field casing in `$select`/`$filter` (must be lowercase LogicalName) or `$expand` (must be case-sensitive Navigation Property Name) | Verify names via `EntityDefinitions(LogicalName='...')/Attributes` |\r\n| 400 | Unsupported SQL — MCP `read_query` rejects DISTINCT/HAVING/subqueries/OFFSET/UNION/CAST/CONVERT/CASE/date-functions (but **allows** JOIN + GROUP BY); `client.query.sql()` rejects `SELECT *`/subqueries/CTE/HAVING/UNION/RIGHT/FULL/CROSS JOIN/functions (but **allows** INNER/LEFT JOIN, GROUP BY, DISTINCT) | Use `fetchxml()`/`$apply` for shapes `sql()` can't express, or pandas for cross-table |\r\n| 404 | Table logical name not found | Check spelling — use `client.tables.get(\"<name>\")` to verify |\r\n| 429 | Rate limited | SDK retries automatically; reduce page size or add delays between pages |\r\n\r\nFor `HttpError` handling in SDK scripts, see the error handling pattern in **dv-data**.\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"
}