# Microsoft Dataverse

> Read and write Microsoft Dataverse, the database behind Power Apps and Dynamics 365. Discover tables and columns, create, update, upsert and delete rows, link related rows, and query with OData filters, FetchXML, saved views and search.

App · Databases · 20 actions. Web page: https://docs.lodol.com/docs/integrations/dataverse

## Actions

### Associate rows

Link a row to another through a one-to-many or many-to-many relationship, such as adding a contact to an account.

**Inputs**

- Table (`table`, text, required): The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
- Row ID (`row_id`, text, required): The row's ID - a GUID from an earlier step's id, or a record URL copied from a Power Apps or Dynamics 365 app.
- Relationship (`relationship`, text, required): The collection of related rows, by navigation property name, such as contact_customer_accounts. Pick one or type it; List relationships shows every name.
- Related table (`related_table`, text, required): The table of the row being linked, such as contact. Pick one or type its logical name.
- Related row ID (`related_row_id`, text, required): The ID of the row on the other side of the relationship.

**Outputs**

- `associated` (true/false)
- `id` (text)
- `related_id` (text)
- `related_table` (text)
- `relationship` (text)
- `table` (text)

**Example output**

```json
{
  "associated": true,
  "id": "00000000-0000-0000-0000-000000000001",
  "related_id": "00000000-0000-0000-0000-000000000002",
  "related_table": "contact",
  "relationship": "contact_customer_accounts",
  "table": "account"
}
```

### Check connection

Confirm the connection works and return the application user's ID, its business unit and the organization ID.

**Inputs**: None.

**Outputs**

- `api_version` (text)
- `business_unit_id` (text)
- `environment_url` (text)
- `organization_id` (text)
- `user_id` (text)

**Example output**

```json
{
  "api_version": "v9.2",
  "business_unit_id": "00000000-0000-0000-0000-00000000000b",
  "environment_url": "https://contoso.crm.dynamics.com",
  "organization_id": "00000000-0000-0000-0000-00000000000c",
  "user_id": "00000000-0000-0000-0000-00000000000a"
}
```

### Clear column value

Empty one column of a row. To clear a lookup, use Update row with <navigation property>@odata.bind set to nothing.

**Inputs**

- Table (`table`, text, required): The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
- Row ID (`row_id`, text, required): The row's ID - a GUID from an earlier step's id, or a record URL copied from a Power Apps or Dynamics 365 app.
- Column (`column`, text, required): The column, by logical name. Pick one or type it.

**Outputs**

- `cleared` (true/false)
- `column` (text)
- `id` (text)
- `table` (text)

**Example output**

```json
{
  "cleared": true,
  "column": "description",
  "id": "00000000-0000-0000-0000-000000000001",
  "table": "account"
}
```

### Count rows

How many rows match a filter, up to 5,000. limit_exceeded is true when there are more.

**Inputs**

- Table (`table`, text, required): The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
- Filter (`filter`, text): An OData filter on logical column names, such as statecode eq 0 and contains(name,'contoso'). Text is compared case-insensitively; write a quote inside text as two quotes. Leave empty for all rows.

**Outputs**

- `count` (number)
- `limit_exceeded` (true/false): True when more than 5,000 rows match
- `table` (text)

**Example output**

```json
{
  "count": 42,
  "limit_exceeded": false,
  "table": "account"
}
```

### Create row

Add a row to a table and return it with its new ID.

**Inputs**

- Table (`table`, text, required): The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
- Column values (`data`, object, required): Each column's logical name and the value to save, such as name = Contoso. Numbers, yes/no and choice values can be typed as text and are converted to the column's type. Set a lookup with <navigation property>@odata.bind = /<entity set>(<id>), for example parentcustomerid_account@odata.bind = /accounts(<id>).
- Columns (`columns`, list): Which columns to return from the saved row. Leave empty for all of them.
- Check for duplicates (`detect_duplicates`, true/false): Refuse the row if the environment's duplicate detection rules match an existing one. Off by default.

**Outputs**: The row's columns by logical name, as Dataverse returned them. Lookup columns appear as _<column>_value. FetchXML and view results leave out columns that are empty.

- `@odata.etag` (text): The row's version
- `id` (text): The row's ID (primary key)

**Example output**

```json
{
  "@odata.etag": "W/\"1234567\"",
  "accountid": "00000000-0000-0000-0000-000000000001",
  "id": "00000000-0000-0000-0000-000000000001",
  "name": "Contoso Ltd",
  "numberofemployees": 25
}
```

### Delete row

Permanently delete a row.

**Inputs**

- Table (`table`, text, required): The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
- Row ID (`row_id`, text, required): The row's ID - a GUID from an earlier step's id, or a record URL copied from a Power Apps or Dynamics 365 app.

**Outputs**

- `deleted` (true/false)
- `id` (text)
- `table` (text)

**Example output**

```json
{
  "deleted": true,
  "id": "00000000-0000-0000-0000-000000000001",
  "table": "account"
}
```

### Disassociate rows

Remove the link between two rows. Neither row is deleted.

**Inputs**

- Table (`table`, text, required): The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
- Row ID (`row_id`, text, required): The row's ID - a GUID from an earlier step's id, or a record URL copied from a Power Apps or Dynamics 365 app.
- Relationship (`relationship`, text, required): The collection of related rows, by navigation property name, such as contact_customer_accounts. Pick one or type it; List relationships shows every name.
- Related row ID (`related_row_id`, text, required): The ID of the row on the other side of the relationship.

**Outputs**

- `disassociated` (true/false)
- `id` (text)
- `related_id` (text)
- `relationship` (text)
- `table` (text)

**Example output**

```json
{
  "disassociated": true,
  "id": "00000000-0000-0000-0000-000000000001",
  "related_id": "00000000-0000-0000-0000-000000000002",
  "relationship": "contact_customer_accounts",
  "table": "account"
}
```

### Get row

One row, by its ID or by an alternate key.

**Inputs**

- Table (`table`, text, required): The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
- Row ID (`row_id`, text): The row's ID (a GUID or a record URL). Leave empty to find the row by its alternate key instead.
- Alternate key (`alternate_key`, object): Find the row by an alternate key defined on the table instead of its ID: each key column's logical name and its value, such as accountnumber = ACC-001. Used only when Row ID is empty.
- Columns (`columns`, list): Which columns to return. Leave empty for all of them - slower on wide tables. The row's ID is always included.
- Expand related (`expand`, text): Related rows to include, such as primarycontactid($select=fullname).
- Include display labels (`include_labels`, true/false): Also return readable labels for choice, lookup, money and date columns, as <column>@OData.Community.Display.V1.FormattedValue.

**Outputs**: The row's columns by logical name, as Dataverse returned them. Lookup columns appear as _<column>_value. FetchXML and view results leave out columns that are empty.

- `@odata.etag` (text): The row's version
- `id` (text): The row's ID (primary key)

**Example output**

```json
{
  "@odata.etag": "W/\"1234567\"",
  "accountid": "00000000-0000-0000-0000-000000000001",
  "id": "00000000-0000-0000-0000-000000000001",
  "name": "Contoso Ltd",
  "numberofemployees": 25
}
```

### List choice options

The options of a choice, multi-select choice, status or status reason column: each option's number, which is what a row stores, and its label.

**Inputs**

- Table (`table`, text, required): The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
- Column (`column`, text, required): The column, by logical name. Pick one or type it.

**Outputs**

- `column` (text)
- `column_type` (text)
- `count` (number)
- `options` (list)
- `table` (text)

**Example output**

```json
{
  "column": "accountcategorycode",
  "column_type": "Picklist",
  "count": 1,
  "options": [
    {
      "color": null,
      "description": null,
      "label": "Preferred Customer",
      "value": 1
    }
  ],
  "table": "account"
}
```

### List columns

A table's columns with their logical names, types, required level and whether each can be set on create or update.

**Inputs**

- Table (`table`, text, required): The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
- Column type (`column_type`, text): Only columns of this type. One of: `String` (Text), `Memo` (Multiline text), `Integer` (Whole number), `BigInt` (Big integer), `Decimal`, `Double` (Floating point), `Money` (Currency), `Boolean` (Yes/No), `DateTime` (Date and time), `Picklist` (Choice), and 11 more.
- Custom columns only (`custom_only`, true/false): Leave out Microsoft's standard columns.

**Outputs**

- `count` (number): How many came back
- `items` (list): The columns
- `table` (text): The table's logical name

**Example output**

```json
{
  "count": 1,
  "items": [
    {
      "attribute_of": null,
      "description": "Type the company or business name.",
      "display_name": "Account Name",
      "is_custom": false,
      "is_primary_id": false,
      "is_primary_name": true,
      "logical_name": "name",
      "metadata_type": "Microsoft.Dynamics.CRM.StringAttributeMetadata",
      "required_level": "ApplicationRequired",
      "schema_name": "Name",
      "type": "String",
      "type_name": "StringType",
      "valid_for_create": true,
      "valid_for_read": true,
      "valid_for_update": true
    }
  ],
  "table": "account"
}
```

### List related rows

The rows linked to one row through a relationship, such as an account's contacts, a page at a time.

**Inputs**

- Table (`table`, text, required): The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
- Row ID (`row_id`, text, required): The row's ID - a GUID from an earlier step's id, or a record URL copied from a Power Apps or Dynamics 365 app.
- Relationship (`relationship`, text, required): The collection of related rows, by navigation property name, such as contact_customer_accounts. Pick one or type it; List relationships shows every name.
- Columns (`columns`, list): Columns of the related table to return, by logical name, such as fullname,emailaddress1. Leave empty for all.
- Filter (`filter`, text): An OData filter on logical column names, such as statecode eq 0 and contains(name,'contoso'). Text is compared case-insensitively; write a quote inside text as two quotes. Leave empty for all rows.
- Sort by (`order_by`, text): Columns to sort by, such as createdon desc,name asc.
- Page size (`page_size`, number): Rows per page, 1-5000. Defaults to 50. Keep the same value when passing next_link. From 1 to 5000. Default: `50`.
- Include display labels (`include_labels`, true/false): Also return readable labels for choice, lookup, money and date columns, as <column>@OData.Community.Display.V1.FormattedValue.
- Next page link (`next_link`, text): To get the next page, the next_link from the previous run of this action. Leave empty for the first page; the other fields are then ignored.

**Outputs**

- `count` (number): How many rows came back
- `has_more` (true/false): Whether another page follows
- `items` (list): The rows on this page
- `next_link` (text): Pass to the same action as next_link for the next page
- `total_count` (number): Total matching rows (up to 5,000), when asked for

**Example output**

```json
{
  "count": 1,
  "has_more": false,
  "items": [
    {
      "@odata.etag": "W/\"1234567\"",
      "accountid": "00000000-0000-0000-0000-000000000001",
      "id": "00000000-0000-0000-0000-000000000001",
      "name": "Contoso Ltd",
      "numberofemployees": 25
    }
  ],
  "next_link": null
}
```

### List relationships

A table's relationships and the navigation property names the other actions need: the relationship to associate or list related rows through, or the name to set a lookup with @odata.bind.

**Inputs**

- Table (`table`, text, required): The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
- Relationship type (`relationship_type`, text): One-to-many lists child rows' relationships; many-to-one lists this table's lookups. Defaults to one-to-many. One of: `one_to_many` (One-to-many (this table is the parent)), `many_to_one` (Many-to-one (this table points to a parent)), `many_to_many` (Many-to-many). Default: `one_to_many`.

**Outputs**

- `count` (number): How many came back
- `items` (list): The relationships
- `table` (text): The table's logical name

**Example output**

```json
{
  "count": 1,
  "items": [
    {
      "is_custom": false,
      "navigation_property": "contact_customer_accounts",
      "related_column": "parentcustomerid",
      "related_navigation_property": "parentcustomerid_account",
      "related_table": "contact",
      "relationship_type": "one_to_many",
      "schema_name": "contact_customer_accounts"
    }
  ],
  "table": "account"
}
```

### List rows

Rows from a table, filtered and sorted with OData, a page at a time. Pass next_link back to get the next page.

**Inputs**

- Table (`table`, text, required): The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
- Columns (`columns`, list): Which columns to return. Leave empty for all of them - slower on wide tables. The row's ID is always included.
- Filter (`filter`, text): An OData filter on logical column names, such as statecode eq 0 and contains(name,'contoso'). Text is compared case-insensitively; write a quote inside text as two quotes. Leave empty for all rows.
- Sort by (`order_by`, text): Columns to sort by, such as createdon desc,name asc.
- Expand related (`expand`, text): Related rows to include, such as primarycontactid($select=fullname).
- Page size (`page_size`, number): Rows per page, 1-5000. Defaults to 50. Keep the same value when passing next_link. From 1 to 5000. Default: `50`.
- Include total count (`include_total`, true/false): Also return how many rows match, up to 5,000.
- Include display labels (`include_labels`, true/false): Also return readable labels for choice, lookup, money and date columns, as <column>@OData.Community.Display.V1.FormattedValue.
- Next page link (`next_link`, text): To get the next page, the next_link from the previous run of this action. Leave empty for the first page; the other fields are then ignored.

**Outputs**

- `count` (number): How many rows came back
- `has_more` (true/false): Whether another page follows
- `items` (list): The rows on this page
- `next_link` (text): Pass to the same action as next_link for the next page
- `total_count` (number): Total matching rows (up to 5,000), when asked for

**Example output**

```json
{
  "count": 1,
  "has_more": false,
  "items": [
    {
      "@odata.etag": "W/\"1234567\"",
      "accountid": "00000000-0000-0000-0000-000000000001",
      "id": "00000000-0000-0000-0000-000000000001",
      "name": "Contoso Ltd",
      "numberofemployees": 25
    }
  ],
  "next_link": null
}
```

### List tables

The tables in the environment, with each one's logical name, entity set name, display name and primary key column. Search by name, or show only custom tables.

**Inputs**

- Search (`search`, text): Only tables whose logical, entity set or display name contains this text.
- Custom tables only (`custom_only`, true/false): Leave out Microsoft's standard tables.

**Outputs**

- `count` (number): How many came back
- `items` (list): The tables

**Example output**

```json
{
  "count": 1,
  "items": [
    {
      "description": "Business that represents a customer or potential customer.",
      "display_collection_name": "Accounts",
      "display_name": "Account",
      "entity_set_name": "accounts",
      "is_activity": false,
      "is_custom": false,
      "is_intersect": false,
      "logical_name": "account",
      "object_type_code": 1,
      "primary_id_column": "accountid",
      "primary_name_column": "name",
      "schema_name": "Account",
      "table_type": "Standard"
    }
  ]
}
```

### List views

A table's active system views, with the default view's ID picked out for Run view.

**Inputs**

- Table (`table`, text, required): The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).

**Outputs**

- `count` (number): How many came back
- `default_view_id` (text): The table's default view
- `items` (list): The views
- `table` (text): The table's logical name

**Example output**

```json
{
  "count": 1,
  "default_view_id": "00000000-0000-0000-00aa-000010001002",
  "items": [
    {
      "description": "Shows all active accounts.",
      "id": "00000000-0000-0000-00aa-000010001002",
      "is_default": true,
      "is_quick_find": false,
      "name": "Active Accounts",
      "query_type": 0
    }
  ],
  "table": "account"
}
```

### Run FetchXML query

Run a FetchXML query, such as one downloaded from Advanced find. The table is read from its <entity name>. Empty columns are left out of each row.

**Inputs**

- FetchXML (`fetch_xml`, text, required): The query, starting with <fetch>. Use its top, page and count attributes to limit or page the results.
- Include display labels (`include_labels`, true/false): Also return readable labels for choice, lookup, money and date columns, as <column>@OData.Community.Display.V1.FormattedValue.

**Outputs**

- `count` (number): How many rows came back
- `has_more` (true/false): Whether another page follows
- `items` (list): The rows on this page
- `more_records` (true/false): Whether the query has further pages, when reported
- `next_link` (text): Pass to the same action as next_link for the next page
- `total_count` (number): Total matching rows (up to 5,000), when asked for

**Example output**

```json
{
  "count": 1,
  "has_more": false,
  "items": [
    {
      "@odata.etag": "W/\"1234567\"",
      "accountid": "00000000-0000-0000-0000-000000000001",
      "id": "00000000-0000-0000-0000-000000000001",
      "name": "Contoso Ltd",
      "numberofemployees": 25
    }
  ],
  "more_records": false,
  "next_link": null
}
```

### Run view

The rows a saved system view shows, with its filter, columns and sort. Empty columns are left out of each row.

**Inputs**

- Table (`table`, text, required): The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
- View (`view_id`, text, required): The view to run. Pick one or paste its ID.
- Page size (`page_size`, number): Rows per page, 1-5000. Defaults to 50. Keep the same value when passing next_link. From 1 to 5000. Default: `50`.
- Include display labels (`include_labels`, true/false): Also return readable labels for choice, lookup, money and date columns, as <column>@OData.Community.Display.V1.FormattedValue.
- Next page link (`next_link`, text): To get the next page, the next_link from the previous run of this action. Leave empty for the first page; the other fields are then ignored.

**Outputs**

- `count` (number): How many rows came back
- `has_more` (true/false): Whether another page follows
- `items` (list): The rows on this page
- `next_link` (text): Pass to the same action as next_link for the next page
- `total_count` (number): Total matching rows (up to 5,000), when asked for

**Example output**

```json
{
  "count": 1,
  "has_more": false,
  "items": [
    {
      "@odata.etag": "W/\"1234567\"",
      "accountid": "00000000-0000-0000-0000-000000000001",
      "id": "00000000-0000-0000-0000-000000000001",
      "name": "Contoso Ltd",
      "numberofemployees": 25
    }
  ],
  "next_link": null
}
```

### Search rows

Relevance search across tables, like the search box in a Power Apps app. Needs Dataverse search turned on, which Developer and trial environments leave off: Power Platform admin center > Environments > your environment > Settings > Product > Features > Dataverse search.

**Inputs**

- Search text (`search_text`, text, required): What to search for, up to 100 characters.
- Tables (`tables`, list): Only search these tables. Leave empty for every table enabled for search.
- Filter (`filter`, text): An OData filter applied to every table searched.
- Maximum results (`top`, number): 1-100. Defaults to 50. From 1 to 100. Default: `50`.
- Skip (`skip`, number): How many results to skip, for the next page. From 0 to 100000. Default: `0`.
- Include total count (`include_total`, true/false): Also return how many results match in total.

**Outputs**

- `count` (number)
- `items` (list): Hits
- `total_count` (number)

**Example output**

```json
{
  "count": 1,
  "items": [
    {
      "attributes": {
        "name": "Contoso Ltd"
      },
      "highlights": {
        "name": [
          "{crmhit}Contoso{/crmhit} Ltd"
        ]
      },
      "id": "00000000-0000-0000-0000-000000000001",
      "object_type_code": 1,
      "score": 4.2,
      "table": "account"
    }
  ],
  "total_count": null
}
```

### Update row

Change some columns of an existing row. Columns not given are left as they are. Fails rather than creating a row if the ID does not exist.

**Inputs**

- Table (`table`, text, required): The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
- Row ID (`row_id`, text, required): The row's ID - a GUID from an earlier step's id, or a record URL copied from a Power Apps or Dynamics 365 app.
- Column values (`data`, object, required): Each column's logical name and the value to save, such as name = Contoso. Numbers, yes/no and choice values can be typed as text and are converted to the column's type. Set a lookup with <navigation property>@odata.bind = /<entity set>(<id>), for example parentcustomerid_account@odata.bind = /accounts(<id>).
- Columns (`columns`, list): Which columns to return from the saved row. Leave empty for all of them.

**Outputs**: The row's columns by logical name, as Dataverse returned them. Lookup columns appear as _<column>_value. FetchXML and view results leave out columns that are empty.

- `@odata.etag` (text): The row's version
- `id` (text): The row's ID (primary key)

**Example output**

```json
{
  "@odata.etag": "W/\"1234567\"",
  "accountid": "00000000-0000-0000-0000-000000000001",
  "id": "00000000-0000-0000-0000-000000000001",
  "name": "Contoso Ltd",
  "numberofemployees": 25
}
```

### Upsert row

Update a row found by ID or alternate key, or create it if it does not exist. Reports which happened.

**Inputs**

- Table (`table`, text, required): The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
- Row ID (`row_id`, text): The row's ID (a GUID or a record URL). Leave empty to find the row by its alternate key instead.
- Alternate key (`alternate_key`, object): Find the row by an alternate key defined on the table instead of its ID: each key column's logical name and its value, such as accountnumber = ACC-001. Used only when Row ID is empty.
- Column values (`data`, object, required): The columns to save, as for Create row. Do not repeat the alternate key columns here.
- Mode (`mode`, text): Whether to allow creating, updating, or both. One of: `upsert` (Create or update), `create_only` (Create only - fail if the row exists), `update_only` (Update only - fail if the row is missing). Default: `upsert`.
- Columns (`columns`, list): Which columns to return from the saved row. Leave empty for all of them.

**Outputs**

- `created` (true/false): True if a new row was created
- `id` (text)
- `row` (object): The row's columns by logical name, as Dataverse returned them. Lookup columns appear as _<column>_value. FetchXML and view results leave out columns that are empty.

**Example output**

```json
{
  "created": false,
  "id": "00000000-0000-0000-0000-000000000001",
  "row": {
    "@odata.etag": "W/\"1234567\"",
    "accountid": "00000000-0000-0000-0000-000000000001",
    "id": "00000000-0000-0000-0000-000000000001",
    "name": "Contoso Ltd",
    "numberofemployees": 25
  }
}
```