# Records Query Reference ## Basic List (All Records) ```bash # First page uip df records list --limit 50 --output json # Next page — pass Data.NextCursor.Value from the previous response uip df records list --limit 50 --cursor --output json ``` Response wrapper: `{ Result, Code: "RecordList" | "RecordQuery", Data: { Items, TotalCount, HasNextPage, NextCursor, CurrentPage, TotalPages, SupportsPageJump } }`. - **Records live in `Data.Items`** (array). Not `Data.Records` — the older docs used that name; the actual key the CLI emits is `Items`. - **`Data.NextCursor` is an object `{ "Value": "" }`, not a flat string.** Pass `Data.NextCursor.Value` to `--cursor` on the next call (unwrap one level). Passing the whole `NextCursor` object errors out. - Use `Data.HasNextPage` to check if more records exist. Stop when it's `false`. ## MULTILINE_MAX Fields — Marker vs Full Content `records list` and `records query` do NOT return `MULTILINE_MAX` content. Each such field comes back as a size marker string starting `HasValue=true Length=N` — live form: `"HasValue=true Length=20000 — call Get Entity Record By Id activity to retrieve content"`. Only single-record read returns the full content: ```bash uip df records get --output json ``` 1. **Never treat the marker as the value.** Don't display, compare, or persist `"HasValue=true Length=N"` as field content — fetch via `records get` first. 2. **Never write the marker back.** A `records update` body built by echoing a record from `list` / `query` overwrites the real content with the literal marker string — verified: the server accepts it as a normal value, `Result: Success`, content silently destroyed. Omit `MULTILINE_MAX` keys from update bodies unless intentionally replacing the content. 3. **No filter, no sort.** `queryFilters` / `sortOptions` naming a `MULTILINE_MAX` field → 400: *"Field '' is of type MULTILINE_MAX and cannot be used in filters."* / *"Sort field '' is of type MULTILINE_MAX and cannot be used for sorting."* Surface verbatim (data-fabric.md Rule 18); don't retry with other operators. Full type contract: [entity-schema.md → MULTILINE_MAX fields](entity-schema.md#multiline_max-fields). ## Pagination Offset-based under the hood. Available on both `records list` and `records query`: - `-l, --limit ` — page size, default `50`, min `1`. Keep constant across a sweep (changing it re-slices the offset and can skip/duplicate records). - `--cursor ` — the inner `Value` string from the previous response's `Data.NextCursor` object. Pass verbatim; never hand-craft. Do **not** pass the wrapper object. - `-o, --offset ` — non-negative record index. Rounded down to the nearest page boundary (`jumpToPage = floor(offset / limit) + 1`). **Mutually exclusive with `--cursor`** — passing both errors with *"--offset and --cursor are mutually exclusive"*. - Stop when `HasNextPage: false`. `CurrentPage` / `TotalPages` / `SupportsPageJump` are informational. ## Folder scope (`--folder-key`) `records list`, `records get`, `records insert`, `records update`, `records delete`, `records query`, and `records import` all accept `--folder-key `. Pass it when the parent entity is folder-scoped. Look up the key from `entities list --include-folders --output json` (`FolderId` per row). ```bash uip df records list --folder-key --output json uip df records query --folder-key \ --body '{"filterGroup":{"logicalOperator":0,"queryFilters":[{"fieldName":"Status","operator":"=","value":"active"}]}}' \ --output json ``` Omit `--folder-key` for tenant-scoped entities. ## Always Query the Server for Answers Issue a fresh `records query` (or `records list`) — don't filter cached transcript data. Records mutate between turns, and the CLI call is the audit trail. Patterns: [filtered query](#filtered-query), [choice-set](#filtering-on-choice-set-fields), [relationship](#filtering-on-relationship-fields), [aggregates](#aggregates-server-side). ## Filtered Query ```bash uip df records query \ --body '{"filterGroup":{"logicalOperator":0,"queryFilters":[{"fieldName":"Status","operator":"=","value":"active"}]}}' \ --output json ``` Pagination uses the same `--limit` / `--cursor` / `--offset` flags as `records list` — never body keys. The `filterGroup` shape, operators, response, and per-type support are in [`filter-platform-contract.md`](filter-platform-contract.md). Beyond the filter, the query body accepts: - `"selectedFields": ["F1","F2"]` — projection. Default is all fields (data-fabric.md Rule 17). - `"sortOptions": [{ "fieldName": "Score", "isDescending": true }]` — server-side sort. ### Verifying a filter applied Check that every returned record satisfies the requested predicate. Equal filtered and unfiltered counts are valid when every record matches. ### Nested Filter Groups ```json { "filterGroup": { "logicalOperator": 1, "filterGroups": [ { "logicalOperator": 0, "queryFilters": [ { "fieldName": "Status", "operator": "=", "value": "active" }, { "fieldName": "Score", "operator": ">", "value": "50" } ] }, { "logicalOperator": 0, "queryFilters": [ { "fieldName": "Priority", "operator": "=", "value": "high" } ] } ] } } ``` ### Filtering on Choice-Set Fields Filter on the integer `NumberId` (as a string in `value` / `valueList`), never the display label. Resolve via `choice-sets list-values ` first. ```bash # CHOICE_SET_SINGLE — category == "travel" (NumberId 1) uip df records query --body \ '{"filterGroup":{"logicalOperator":0,"queryFilters":[{"fieldName":"category","operator":"=","value":"1"}]}}' \ --output json ``` **`CHOICE_SET_MULTIPLE`** has special `=` vs `contains` semantics — see the [filter contract](filter-platform-contract.md#operator-support-by-field-type) Complex-field values line. Practical examples: ```bash # Membership — records tagged with NumberId 1 uip df records query --body \ '{"filterGroup":{"logicalOperator":0,"queryFilters":[{"fieldName":"tags","operator":"contains","value":"1"}]}}' \ --output json # Set equality — tags == exactly {1,3} uip df records query --body \ '{"filterGroup":{"logicalOperator":0,"queryFilters":[{"fieldName":"tags","operator":"=","value":"[1,3]"}]}}' \ --output json ``` Failure modes: `=` with a bare value (`"1"`) → HTTP 400. `contains` with brackets (`"[1]"`) → HTTP 400. For per-value reporting, run `contains` per `NumberId`; for distribution of exact combinations, use `groupBy: ["tags"]` with `COUNT`. ### Filtering on Relationship Fields Filter on the target record's UUID `Id`, regardless of which field was bound as `referenceFieldId` (that controls the join, not the stored value). If the user describes the parent by another field (email, name, etc.), resolve the UUID first on the parent entity, then filter the child. ```bash # Direct uip df records query --body \ '{"filterGroup":{"logicalOperator":0,"queryFilters":[{"fieldName":"customerId","operator":"=","value":""}]}}' \ --output json # Resolve-first: email → Id on parent, then filter child uip df records query --body \ '{"filterGroup":{"logicalOperator":0,"queryFilters":[{"fieldName":"Email","operator":"=","value":"alice@example.com"}]},"selectedFields":["Id"]}' \ --output json ``` ## Aggregates (server-side) Add `aggregates` and optional `groupBy` to the query body to return aggregated rows instead of records. Each entry in `aggregates` produces one column on each result row, keyed by `alias`. > **Field names are case-sensitive.** Examples below use `Status` as a placeholder — substitute the exact casing from the target entity's schema (`uip df entities get ` lists the real names). ```bash # Total count of records (no grouping → single result row) uip df records query \ --body '{"aggregates":[{"function":"COUNT","field":"Id","alias":"total"}]}' \ --output json ``` Response: `Data.Items` is a one-row array — e.g. `[{ "Total": 250 }]`. The server **PascalCases your alias** in the response (`alias: "total"` → key `"Total"`). Read by the PascalCase key when parsing. ```bash # Count per group (one result row per distinct value) uip df records query \ --body '{"selectedFields":["Status"],"groupBy":["Status"],"aggregates":[{"function":"COUNT","field":"Id","alias":"total"}]}' \ --output json ``` Response: `Data.Items` — one row per group, each with the group fields + every aggregate alias (PascalCased) — e.g. `[{ "Status": "Open", "Total": 12 }, { "Status": "Closed", "Total": 5 }]`. ### Functions | `function` | Applies to | Notes | |------------|-----------|-------| | `COUNT` | Any field | Counts non-null values. For total row count use `field: "Id"` | | `SUM` | Numeric only | | | `AVG` | Numeric only | | | `MIN` | Numeric / date | | | `MAX` | Numeric / date | | Values are the **uppercase strings** above — `"COUNT"` not `"Count"`. ### Aggregate Body Schema ```json { "selectedFields": ["Status"], "groupBy": ["Status"], "aggregates": [ { "function": "COUNT", "field": "Id", "alias": "total" }, { "function": "AVG", "field": "amount", "alias": "avgAmount" } ] } ``` - `aggregates[].alias` is optional. When omitted, the server returns the column keyed as `{FUNCTION}_{field}` (for example `COUNT_Id`, `AVG_amount`). Provide an `alias` for stable, readable keys in your downstream code. - When `selectedFields` is present alongside `aggregates`, every entry in `selectedFields` must also appear in `groupBy` — otherwise the API rejects the request. The shortcut: use the same array for both, as in the examples above. - `groupBy` and `selectedFields` may reference root-entity fields only — expansions are not supported in aggregate mode. - The same `filterGroup`, `sortOptions`, and pagination flags (`--limit`, `--cursor`) work alongside aggregates. Filters are applied **before** grouping (SQL `WHERE`). - `groupBy` names the choice-set field. Group keys and filter values use numeric `NumberId` values rather than display labels. ## Insert Records The CLI routes by body shape: a JSON object (or 1-element array) calls the single-record endpoint; a JSON array with 2+ elements calls the batch endpoint. ```bash # Single record — JSON object uip df records insert --body '{"Name":"Alice","Score":95}' --output json # Batch insert — JSON array with 2+ records uip df records insert \ --body '[{"Name":"Alice","Score":95},{"Name":"Bob","Score":82}]' \ --output json # From JSON file uip df records insert --file records.json --output json ``` Single insert response: `{ Code: "RecordInserted", Data: { ...record with Id } }` Batch insert response: `{ Code: "RecordsBatchInserted", Data: { SuccessCount, FailureCount, SuccessRecords, FailureRecords } }` ### Writing Choice-Set and Relationship Values | Field type | Value | Resolve via | |------------|-------|-------------| | `CHOICE_SET_SINGLE` | Integer `NumberId` | `choice-sets list-values ` | | `CHOICE_SET_MULTIPLE` | Integer `NumberId` array | `choice-sets list-values ` | | `RELATIONSHIP` | Target record's UUID `Id` (always — the binding `referenceFieldId` controls the join, not the stored value) | `records query ` on the unique field | | `FILE` | **Not writable through `records insert` / `records update`** — see below | `files upload` | ```bash uip df records insert \ --body '{"amount":250,"category":1,"tags":[1,3],"customerId":""}' --output json ``` Display labels, choice-value UUIDs, and non-UUID relationship values are rejected — resolve first. Reads echo the same shape. ### FILE fields Never include a FILE key in record insert/update/import payloads; the platform silently drops it. Insert the row without that key, then use `files upload`. See data-fabric.md Rule 6 and [`file-attachments.md`](file-attachments.md). ## Update Records The CLI routes by body shape: a JSON object (or 1-element array) calls the single-record endpoint; a JSON array with 2+ elements calls the batch endpoint. Both require `Id` in the body. ```bash # Single record — JSON object with Id uip df records update --body '{"Id":"","Score":100}' --output json # Batch update — JSON array, each element must include Id uip df records update \ --body '[{"Id":"","Score":100},{"Id":"","Score":90}]' \ --output json ``` Choice / relationship fields use the same value form as insert — see [Writing Choice-Set and Relationship Values](#writing-choice-set-and-relationship-values). Single update response: `{ Code: "RecordUpdated", Data: { ...updated record } }` Batch update response: `{ Code: "RecordsBatchUpdated", Data: { SuccessCount, FailureCount, SuccessRecords, FailureRecords } }` ## Delete Records Irreversible — `--yes` and `--reason` are required (server-gated, same as `entities delete` / `choice-sets delete`). Pass each record ID as a **separate positional argument**; do not space-join them inside one quoted string (the CLI then tries to parse the whole string as a single GUID and errors with *"Error converting value '… …' to type 'System.Guid'"*). ```bash uip df records delete \ [--folder-key <…>] \ --yes --reason "" \ --output json ``` Response: `{ Code: "RecordsDeleted", Data: { SuccessCount, FailureCount, SuccessRecords, FailureRecords, Reason } }` — `Reason` echoes the `--reason` value for audit logging.