Docs
Open Lodol
DocumentationIntegrationsAPI

Microsoft Dataverse

App · Databases · 20 actions

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.

Actions

ActionWhat it does
Associate rowsLink a row to another through a one-to-many or many-to-many relationship, such as adding a contact to an account.
Check connectionConfirm the connection works and return the application user's ID, its business unit and the organization ID.
Clear column valueEmpty one column of a row.
Count rowsHow many rows match a filter, up to 5,000.
Create rowAdd a row to a table and return it with its new ID.
Delete rowPermanently delete a row.
Disassociate rowsRemove the link between two rows.
Get rowOne row, by its ID or by an alternate key.
List choice optionsThe 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.
List columnsA table's columns with their logical names, types, required level and whether each can be set on create or update.
List related rowsThe rows linked to one row through a relationship, such as an account's contacts, a page at a time.
List relationshipsA 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.
List rowsRows from a table, filtered and sorted with OData, a page at a time.
List tablesThe tables in the environment, with each one's logical name, entity set name, display name and primary key column.
List viewsA table's active system views, with the default view's ID picked out for Run view.
Run FetchXML queryRun a FetchXML query, such as one downloaded from Advanced find.
Run viewThe rows a saved system view shows, with its filter, columns and sort.
Search rowsRelevance search across tables, like the search box in a Power Apps app.
Update rowChange some columns of an existing row.
Upsert rowUpdate a row found by ID or alternate key, or create it if it does not exist.

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

TabletabletextRequired
The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
Row IDrow_idtextRequired
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.
RelationshiprelationshiptextRequired
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 tablerelated_tabletextRequired
The table of the row being linked, such as contact. Pick one or type its logical name.
Related row IDrelated_row_idtextRequired
The ID of the row on the other side of the relationship.

Outputs

associatedtrue/false
idtext
related_idtext
related_tabletext
relationshiptext
tabletext
Example output
{
  "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_versiontext
business_unit_idtext
environment_urltext
organization_idtext
user_idtext
Example output
{
  "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

TabletabletextRequired
The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
Row IDrow_idtextRequired
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.
ColumncolumntextRequired
The column, by logical name. Pick one or type it.

Outputs

clearedtrue/false
columntext
idtext
tabletext
Example output
{
  "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

TabletabletextRequired
The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
Filterfiltertext
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

countnumber
limit_exceededtrue/false
True when more than 5,000 rows match
tabletext
Example output
{
  "count": 42,
  "limit_exceeded": false,
  "table": "account"
}

Create row

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

Inputs

TabletabletextRequired
The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
Column valuesdataobjectRequired
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>).
Columnscolumnslist
Which columns to return from the saved row. Leave empty for all of them.
Check for duplicatesdetect_duplicatestrue/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.etagtext
The row's version
idtext
The row's ID (primary key)
Example output
{
  "@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

TabletabletextRequired
The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
Row IDrow_idtextRequired
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

deletedtrue/false
idtext
tabletext
Example output
{
  "deleted": true,
  "id": "00000000-0000-0000-0000-000000000001",
  "table": "account"
}

Disassociate rows

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

Inputs

TabletabletextRequired
The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
Row IDrow_idtextRequired
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.
RelationshiprelationshiptextRequired
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 IDrelated_row_idtextRequired
The ID of the row on the other side of the relationship.

Outputs

disassociatedtrue/false
idtext
related_idtext
relationshiptext
tabletext
Example output
{
  "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

TabletabletextRequired
The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
Row IDrow_idtext
The row's ID (a GUID or a record URL). Leave empty to find the row by its alternate key instead.
Alternate keyalternate_keyobject
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.
Columnscolumnslist
Which columns to return. Leave empty for all of them - slower on wide tables. The row's ID is always included.
Expand relatedexpandtext
Related rows to include, such as primarycontactid($select=fullname).
Include display labelsinclude_labelstrue/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.etagtext
The row's version
idtext
The row's ID (primary key)
Example output
{
  "@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

TabletabletextRequired
The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
ColumncolumntextRequired
The column, by logical name. Pick one or type it.

Outputs

columntext
column_typetext
countnumber
optionslist
tabletext
Example output
{
  "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

TabletabletextRequired
The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
Column typecolumn_typetext
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 onlycustom_onlytrue/false
Leave out Microsoft's standard columns.

Outputs

countnumber
How many came back
itemslist
The columns
tabletext
The table's logical name
Example output
{
  "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"
}

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

Inputs

TabletabletextRequired
The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
Row IDrow_idtextRequired
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.
RelationshiprelationshiptextRequired
The collection of related rows, by navigation property name, such as contact_customer_accounts. Pick one or type it; List relationships shows every name.
Columnscolumnslist
Columns of the related table to return, by logical name, such as fullname,emailaddress1. Leave empty for all.
Filterfiltertext
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 byorder_bytext
Columns to sort by, such as createdon desc,name asc.
Page sizepage_sizenumber
Rows per page, 1-5000. Defaults to 50. Keep the same value when passing next_link.From 1 to 5000. Default: 50.
Include display labelsinclude_labelstrue/false
Also return readable labels for choice, lookup, money and date columns, as <column>@OData.Community.Display.V1.FormattedValue.
Next page linknext_linktext
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

countnumber
How many rows came back
has_moretrue/false
Whether another page follows
itemslist
The rows on this page
next_linktext
Pass to the same action as next_link for the next page
total_countnumber
Total matching rows (up to 5,000), when asked for
Example output
{
  "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

TabletabletextRequired
The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
Relationship typerelationship_typetext
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

countnumber
How many came back
itemslist
The relationships
tabletext
The table's logical name
Example output
{
  "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

TabletabletextRequired
The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
Columnscolumnslist
Which columns to return. Leave empty for all of them - slower on wide tables. The row's ID is always included.
Filterfiltertext
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 byorder_bytext
Columns to sort by, such as createdon desc,name asc.
Expand relatedexpandtext
Related rows to include, such as primarycontactid($select=fullname).
Page sizepage_sizenumber
Rows per page, 1-5000. Defaults to 50. Keep the same value when passing next_link.From 1 to 5000. Default: 50.
Include total countinclude_totaltrue/false
Also return how many rows match, up to 5,000.
Include display labelsinclude_labelstrue/false
Also return readable labels for choice, lookup, money and date columns, as <column>@OData.Community.Display.V1.FormattedValue.
Next page linknext_linktext
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

countnumber
How many rows came back
has_moretrue/false
Whether another page follows
itemslist
The rows on this page
next_linktext
Pass to the same action as next_link for the next page
total_countnumber
Total matching rows (up to 5,000), when asked for
Example output
{
  "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

Searchsearchtext
Only tables whose logical, entity set or display name contains this text.
Custom tables onlycustom_onlytrue/false
Leave out Microsoft's standard tables.

Outputs

countnumber
How many came back
itemslist
The tables
Example output
{
  "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

TabletabletextRequired
The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).

Outputs

countnumber
How many came back
default_view_idtext
The table's default view
itemslist
The views
tabletext
The table's logical name
Example output
{
  "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

FetchXMLfetch_xmltextRequired
The query, starting with <fetch>. Use its top, page and count attributes to limit or page the results.
Include display labelsinclude_labelstrue/false
Also return readable labels for choice, lookup, money and date columns, as <column>@OData.Community.Display.V1.FormattedValue.

Outputs

countnumber
How many rows came back
has_moretrue/false
Whether another page follows
itemslist
The rows on this page
more_recordstrue/false
Whether the query has further pages, when reported
next_linktext
Pass to the same action as next_link for the next page
total_countnumber
Total matching rows (up to 5,000), when asked for
Example output
{
  "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

TabletabletextRequired
The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
Viewview_idtextRequired
The view to run. Pick one or paste its ID.
Page sizepage_sizenumber
Rows per page, 1-5000. Defaults to 50. Keep the same value when passing next_link.From 1 to 5000. Default: 50.
Include display labelsinclude_labelstrue/false
Also return readable labels for choice, lookup, money and date columns, as <column>@OData.Community.Display.V1.FormattedValue.
Next page linknext_linktext
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

countnumber
How many rows came back
has_moretrue/false
Whether another page follows
itemslist
The rows on this page
next_linktext
Pass to the same action as next_link for the next page
total_countnumber
Total matching rows (up to 5,000), when asked for
Example output
{
  "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 textsearch_texttextRequired
What to search for, up to 100 characters.
Tablestableslist
Only search these tables. Leave empty for every table enabled for search.
Filterfiltertext
An OData filter applied to every table searched.
Maximum resultstopnumber
1-100. Defaults to 50.From 1 to 100. Default: 50.
Skipskipnumber
How many results to skip, for the next page.From 0 to 100000. Default: 0.
Include total countinclude_totaltrue/false
Also return how many results match in total.

Outputs

countnumber
itemslist
Hits
total_countnumber
Example output
{
  "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

TabletabletextRequired
The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
Row IDrow_idtextRequired
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 valuesdataobjectRequired
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>).
Columnscolumnslist
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.etagtext
The row's version
idtext
The row's ID (primary key)
Example output
{
  "@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

TabletabletextRequired
The table to work with. Pick one, or type its logical name (account) or entity set name (accounts).
Row IDrow_idtext
The row's ID (a GUID or a record URL). Leave empty to find the row by its alternate key instead.
Alternate keyalternate_keyobject
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 valuesdataobjectRequired
The columns to save, as for Create row. Do not repeat the alternate key columns here.
Modemodetext
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.
Columnscolumnslist
Which columns to return from the saved row. Leave empty for all of them.

Outputs

createdtrue/false
True if a new row was created
idtext
rowobject
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
{
  "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
  }
}

Microsoft Clarity

Previous Page

Microsoft Dynamics 365

Next Page

On this page

ActionsAssociate rowsCheck connectionClear column valueCount rowsCreate rowDelete rowDisassociate rowsGet rowList choice optionsList columnsList related rowsList relationshipsList rowsList tablesList viewsRun FetchXML queryRun viewSearch rowsUpdate rowUpsert row