Create a table row using the Web API (Microsoft Dataverse) - Power Apps (2023)

  • Article
  • 3 minutes to read

Note

Unsure about entity vs. table? See Developers: Understand terminology in Microsoft Dataverse.

Use a POST request to send data to create a table row (entity record). You can create multiple related table rows in a single operation using deep insert. You also need to know how to set values to associate a new table row to existing tables using the @odata.bind annotation.

Note

For information about how to create and update the table (entity) definitions using the Web API, see Create and update table definitions using the Web API.

Basic Create

This example creates a new account entity record. The response OData-EntityId header contains the Uri of the created entity.

Request

POST [Organization URI]/api/data/v9.0/accounts HTTP/1.1Content-Type: application/json; charset=utf-8OData-MaxVersion: 4.0OData-Version: 4.0Accept: application/json{ "name": "Sample Account", "creditonhold": false, "address1_latitude": 47.639583, "description": "This is the description of the sample account", "revenue": 5000000, "accountcategorycode": 1}

Response

(Video) Query Dataverse Tables With HTTP POST

HTTP/1.1 204 No ContentOData-Version: 4.0OData-EntityId: [Organization URI]/api/data/v9.0/accounts(7eb682f1-ca75-e511-80d4-00155d2a68d1)

To create a new entity record you must identify the valid property names and types. For all system tables and attributes (table columns), you can find this information in the topic for that entity in the Dataverse table/entity reference. For custom tables or columns, refer to the definition of that table in the CSDL $metadata document. More information: Web API EntityTypes

Create with data returned

You can compose your POST request so that data from the created record will be returned with a status of 201 (Created). To get this result, you must use the return=representation preference in the request headers.

To control which properties are returned, append the $select query option to the URL to the entity set. You may also use $expand to return related entities.

Note

Nested $expand on collection-valued navigation properties will not return data. More information: Retrieve related entities for an entity instance by expanding collection-valued navigation properties

When an entity is created in this way the OData-EntityId header containing the URI to the created record is not returned.

This example creates a new account entity and returns the requested data in the response.

Request

POST [Organization URI]/api/data/v9.0/accounts?$select=name,creditonhold,address1_latitude,description,revenue,accountcategorycode,createdon HTTP/1.1OData-MaxVersion: 4.0OData-Version: 4.0Accept: application/jsonContent-Type: application/json; charset=utf-8Prefer: return=representation{ "name": "Sample Account", "creditonhold": false, "address1_latitude": 47.639583, "description": "This is the description of the sample account", "revenue": 5000000}

Response

HTTP/1.1 201 CreatedContent-Type: application/json; odata.metadata=minimalPreference-Applied: return=representationOData-Version: 4.0{ "@odata.context": "[Organization URI]/api/data/v9.0/$metadata#accounts/$entity", "@odata.etag": "W/\"536530\"", "accountid": "d6f193fc-ce85-e611-80d8-00155d2a68de", "accountcategorycode": 1, "description": "This is the description of the sample account", "address1_latitude": 47.63958, "creditonhold": false, "name": "Sample Account", "createdon": "2016-09-28T22:57:53Z", "revenue": 5000000.0000, "_transactioncurrencyid_value": "048dddaa-6f7f-e611-80d3-00155db5e0b6"}

You can create entities related to each other by defining them as navigation properties values. This is known as deep insert. This approach has two advantages. It is more efficient, replacing multiple simpler creation and association operations with one combined operation. Also, it is atomic where either the entire operation succeeds and all the related objects are created, or the operation fails and none are created.

(Video) How to use Microsoft Dataverse - Create your first table and columns

As with a basic create, the response OData-EntityId header contains the Uri of the created entity. The URIs for the related entities created aren't returned. You can get the primary key values of the records if you use the Prefer: return=representation header so it returns the values of the created record. More information: Create with data returned

For example, the following request body posted to the accounts entity set will create a total of four new entities in the context of creating an account.

Note

When creating a new table row, it is not possible to combine the row creation with the insert of a non-primary image. For a non-primary image to be added, the row must already exist.

Request

POST [Organization URI]/api/data/v9.0/accounts HTTP/1.1Content-Type: application/json; charset=utf-8OData-MaxVersion: 4.0OData-Version: 4.0Accept: application/json{ "name": "Sample Account", "primarycontactid": { "firstname": "John", "lastname": "Smith" }, "opportunity_customer_accounts": [ { "name": "Opportunity associated to Sample Account", "Opportunity_Tasks": [ { "subject": "Task associated to opportunity" } ] } ]}

Response

HTTP/1.1 204 No ContentOData-Version: 4.0OData-EntityId: [Organization URI]/api/data/v9.0/accounts(3c6e4b5f-86f6-e411-80dd-00155d2a68cb)

Associate table rows on create

To associate new entities to existing entities when they are created you must set the value of navigation properties using the @odata.bind annotation.

(Video) Get started using Postman with Microsoft Dataverse Web API

The following request body posted to the accounts entity set will create a new account associated with an existing contact with the contactid value of 00000000-0000-0000-0000-000000000001 and two existing tasks with activityid values of 00000000-0000-0000-0000-000000000002 and 00000000-0000-0000-0000-000000000003.

Note

This request is using the Prefer: return=representation header so it returns the values of the created record. More information: Create with data returned

Request

POST [Organization URI]/api/data/v9.0/accounts?$select=name&$expand=primarycontactid($select=fullname),Account_Tasks($select=subject) HTTP/1.1Content-Type: application/json; charset=utf-8OData-MaxVersion: 4.0OData-Version: 4.0Accept: application/jsonPrefer: return=representation{ "name": "Sample Account", "primarycontactid@odata.bind": "/contacts(00000000-0000-0000-0000-000000000001)", "Account_Tasks@odata.bind": [ "/tasks(00000000-0000-0000-0000-000000000002)", "/tasks(00000000-0000-0000-0000-000000000003)" ]}

Response

HTTP/1.1 201 CreatedOData-Version: 4.0Preference-Applied: return=representation{ "@odata.context": "[Organization URI]/api/data/v9.1/$metadata#accounts(name,primarycontactid(fullname),Account_Tasks(subject))/$entity", "@odata.etag": "W/\"36236432\"", "name": "Sample Account", "accountid": "00000000-0000-0000-0000-000000000004", "primarycontactid": { "@odata.etag": "W/\"28877094\"", "fullname": "Yvonne McKay (sample)", "contactid": "00000000-0000-0000-0000-000000000001" }, "Account_Tasks": [ { "@odata.etag": "W/\"36236437\"", "subject": "Task 1", "activityid": "00000000-0000-0000-0000-000000000002" }, { "@odata.etag": "W/\"36236440\"", "subject": "Task 2", "activityid": "00000000-0000-0000-0000-000000000003" } ]}

Check for duplicate records

By default, duplicate detection is suppressed when you are creating records using the Web API. You must include the MSCRM.SuppressDuplicateDetection: false header with your POST request to enable duplicate detection. Duplicate detection only applies when 1) the organization has enabled duplicate detection, 2) the entity is enabled for duplicate detection, and 3) there are active duplicate detection rules being applied. More information: Detect duplicate data using code

See Detect duplicate data using Web API for more information on how to check for duplicate records during Create operation.

Create a new record from another record

Use the InitializeFrom Function to create a new record in the context of an existing record where a mapping exists for the relationship between the tables. For information about creating these mappings, see:

  • Map table columns
  • Customize table and column mappings

Note

(Video) Create Tables in Dataverse – Part 1

To determine whether two entities can be mapped, use this query:
GET [Organization URI]/api/data/v9.1/entitymaps?$select=sourceentityname,targetentityname&$orderby=sourceentityname

This is a two step process. The InitializeFrom function doesn't create the record, but it returns data you can use to create a new record with specified property values mapped from the original record. You will combine the response data returned in the InitializeFrom function with any changes you want to make and then POST the data to create the new record.

The following example shows how to create an account record using the values of an existing account record with accountid value equal to 00000000-0000-0000-0000-000000000001.

Step 1: Get the data using InitializeFrom

Request

GET [Organization URI]/api/data/v9.0/InitializeFrom(EntityMoniker=@p1,TargetEntityName=@p2,TargetFieldType=@p3)?@p1={'@odata.id':'accounts(00000000-0000-0000-0000-000000000001)'}&@p2='account'&@p3=Microsoft.Dynamics.CRM.TargetFieldType'ValidForCreate' HTTP/1.1If-None-Match: nullOData-Version: 4.0OData-MaxVersion: 4.0Content-Type: application/jsonAccept: application/json

Response

{ "@odata.context": "[Organization URI]/api/data/v9.0/$metadata#accounts/$entity", "@odata.type": "#Microsoft.Dynamics.CRM.account", "parentaccountid@odata.bind": "accounts(00000000-0000-0000-0000-000000000001)", "transactioncurrencyid@odata.bind": "transactioncurrencies(732e87e1-1d96-e711-80e4-00155db75426)", "address1_line1": "123 Maple St.", "address1_city": "Seattle", "address1_country": "United States of America"}

Step 2: Create the new record

The response received from InitializeFrom function consists of values of mapped columns between the source table and target table and the GUID of the parent record. The column mapping between tables that have an relationship is different for different tables and is customizable, so the response from InitializeFrom function request may vary for different organizations.

Other property values can also be set and/or modified for the new record by adding them in the JSON request body, as shown in the example below.

POST [Organization URI]/api/data/v9.0/accounts HTTP/1.1Content-Type: application/json; charset=utf-8OData-MaxVersion: 4.0OData-Version: 4.0Accept: application/json { "@odata.context": "[Organization URI]/api/data/v9.0/$metadata#accounts/$entity", "@odata.type": "#Microsoft.Dynamics.CRM.account", "parentaccountid@odata.bind": "accounts(00000000-0000-0000-0000-000000000001)", "transactioncurrencyid@odata.bind": "transactioncurrencies(732e87e1-1d96-e711-80e4-00155db75426)", "name":"Contoso Ltd", "numberofemployees":"200", "address1_line1":"100 Maple St.", "address1_city":"Seattle", "address1_country":"United States of America", "fax":"73737" }}

Create documents in storage partitions

If you are creating large numbers of entities that contain documents, you can create the entities in storage partitions to speed up access to those entity records.

More information: Access documents faster using storage partitions

See also

Web API Basic Operations Sample (C#)
Web API Basic Operations Sample (Client-side JavaScript)
InitializeFrom Function
Perform operations using the Web API
Compose Http requests and handle errors
Query Data using the Web API
Retrieve a table row using the Web API
Update and delete table rows using the Web API
Associate and disassociate table rows using the Web API
Use Web API functions
Use Web API actions
Execute batch operations using the Web API
Impersonate another user using the Web API
Perform conditional operations using the Web API

FAQs

How will you create a Dataverse table in power app? ›

  1. Prerequisites. To create a table, you must have either a System Administrator or System Customizer security role within Microsoft Dataverse.
  2. Sign in to Power Apps. Sign in to Power Apps at https://make.powerapps.com.
  3. Create a table. In the navigation pane, expand Data, and then select Tables. ...
  4. Next steps. ...
  5. Privacy notice.
20 Jun 2022

How do you create a data table in Dataverse? ›

Create a Custom table in Microsoft Dataverse
  1. First, Sign in to Power Apps using your office 365 credentials. ...
  2. Once you will select + New table on the top command bar, then the new table panel will open where you can enter the table information like Display name, Description, etc.
21 Jun 2022

How do you count rows in a Dataverse table? ›

The CountRows function counts the number of records in a table.
...
To enable the option:
  1. Open the app where you want to use the functions.
  2. Select Settings > Upcoming features > Preview.
  3. Turn on the Enhanced delegation for Microsoft Dataverse option.
8 Jul 2022

How do you add columns in Dataverse? ›

In model-driven apps, select New Column from the form editor. Import a solution that contains the definition of the columns. Use Power Query to create new tables and fill them with data. More information: Add data to a table in the Dataverse by using Power Query.

Can you create a table in Power Apps? ›

The Table function creates a table from an argument list of records. The table's columns will be the union of all the properties from all the argument records. A blank value is added to any column for which a record doesn't include a value. A table is a value in Power Apps, just like a string or a number.

How do you create a Dataverse table in a team? ›

Sign in to Teams, and then select the link for Power Apps. Select the Build tab, and then select See all. Select New, and then select Table.

How many rows of data can Dataverse handle? ›

With Dataverse for Teams, there is a limit in storage capacity (1 million rows or 2 GB). If you think you will need to manage more data than this, then you should consider Dataverse.

What is Microsoft Dataverse table? ›

Microsoft Dataverse. Dataverse lets you securely store and manage data that's used by business applications. Standard and custom tables within Dataverse provide a secure and cloud-based storage option for your data.

What are the types of tables in Dataverse? ›

Account, business unit, contact, task, and user tables are examples of standard tables in Dataverse. Most of the standard tables included with Dataverse can be customized. Tables that are imported as part of a managed solution and set as customizable also appear as standard tables.

How do I count Datatable rows in PowerApps? ›

PowerApps countrows gallery or PowerApps count rows in gallery.
...
PowerApps countrows delegation sharepoint
  1. Open your PowerApps app.
  2. Go to the File tab and then Settings.
  3. Select on Advanced Settings.
  4. Set the value to 2000 in the Data row limit for non-delegable queries.
13 Apr 2021

How do I count rows in DB? ›

The COUNT() function returns the number of rows that matches a specified criterion.

How do you count data in a row? ›

If you need a quick way to count rows that contain data, select all the cells in the first column of that data (it may not be column A). Just click the column header. The status bar, in the lower-right corner of your Excel window, will tell you the row count.

How many columns can a Dataverse table have? ›

The SQL server tables limit is 1024 columns. This is the limit to the SQL Server views also. When we create a new field in a CRM entity, the CRM add this field in the respective table and in the respective Filtered View. Depending on the field type the number of columns added in the Filtered view is different.

How do you edit a table in Dataverse? ›

Edit an existing table
  1. From the app authoring menu, select Data.
  2. On the Data pane, select Add data.
  3. In the Select a data source dialog box, from the list of Dataverse tables, select a table to add to your app.
  4. On the newly added table select, More actions > Edit data.
26 Aug 2022

How do I create a Power Apps table in Excel? ›

You can convert your data to a table by selecting Format as Table in the Home tab of Excel. You can also create a table by selecting Table on the Insert tab. To find your table easily, go to Design under Table Tools, and rename your table.

How do I make a table? ›

Try it!
  1. Select a cell within your data.
  2. Select Home > Format as Table.
  3. Choose a style for your table.
  4. In the Create Table dialog box, set your cell range.
  5. Mark if your table has headers.
  6. Select OK.

How do you connect Tables in Power Apps? ›

On powerapps.com, expand the Data section, and then select Tables on the left navigation pane. Select the Relationships area. Select Add relationship, and then select a relationship type, such as Many-to-one. On the right pane, select a Related table for the Current table, and then select Done.

How do you create a data table in power automate? ›

Read from CSV File. Retrieve data table column to list (Creates variable for 1 column of data only) Launch excel.
...
At the moment, Power Automate Desktop provides three actions that generate a datatable as output:
  1. the Read from Excel worksheet action.
  2. the Execute SQL statement action.
  3. the Extract data from web page action.
20 Oct 2020

How do I open a Dataverse table? ›

Import or link to Dataverse tables
  1. Start Access, select Account, select Switch Account. ...
  2. Make sure you are signed into Access with the same credentials you use in Power Apps.
  3. Open the Access database for which you want to import or link Dataverse tables.

How do I get data from Microsoft Dataverse? ›

Export data
  1. Sign into Power Apps, on the left navigation pane expand Data, and then select Tables.
  2. Select Data > Export data.
  3. Select the tables that you want to export data from, and then select Export data.
26 Jul 2022

How many rows can PowerApps handle? ›

You may set the values for the data row limit from 1 to 2000. We do not allow values above 2000 may because a higher value is very likely to adversely impact system performance.

How many rows are in a power query? ›

Power Query specifications and limits in Excel
FeatureLimitation
Number of columns per table16,384
Maximum size of text in a preview cell1M characters
Maximum size of text filled to Excel or data modelNot limited by Power Query
Maximum number of rows filled to worksheet1,048,576
10 more rows

How do you filter rows in Dataverse? ›

Select Microsoft Dataverse to filter the search results to display only actions and triggers for Microsoft Dataverse. Select List rows. Select the Accounts table from the Table name list. Save and run your flow to confirm that no more than 5,000 rows are returned.

What is data table in Power Apps? ›

The Data table control shows a dataset in a format that includes column headers for each field that the control shows. As an app maker, you have full control over which fields appear and in what order. Like the Gallery control, the Data table control maintains a Selected property that points to the selected row.

Is Microsoft Dataverse an API? ›

The Dataverse Web API provides a development experience that can be used across a wide variety of programming languages, platforms, and devices. The Web API implements the OData (Open Data Protocol), version 4.0, an OASIS standard for building and consuming RESTful APIs over rich data sources.

What are the 3 types of tables? ›

The most common types are work tables, dining room tables, living room or bedroom tables, office tables, gaming tables, and decorative or pedestal tables.

What are the 4 components of a data table? ›

A data table contains a header row at the top that lists column names, followed by rows for data.
  • Table content.
  • Column headers.
  • Text alignment.

What is primary name column in Dataverse table? ›

There is a "Primary Name" column in most tables in Dataverse. This Primary Name column is used in all list views to click through (hyperlink) and get to the record's form. Whenever you add a lookup from one table to another table (i.e. a N:1) it shows this as the reference to that other record.

How do you get row count in G grid? ›

Api the AgGrid: getDisplayedRowCount() Returns the total number of displayed rows.

How do I count rows in DAX? ›

You can use the COUNT function to count column values, or you can use the COUNTROWS function to count table rows. Both functions will achieve the same result, providing that the counted column contains no BLANKs. The following measure definition presents an example. It calculates the number of OrderDate column values.

What is table row count? ›

Returns the number of rows in the table .

How do I count rows and columns in a Dataframe? ›

To get the number of rows, and columns we can use len(df. axes[]) function in Python.

How do you count rows in grid? ›

1 Answer
  1. var grid = $( "#Grid" ).data( "kendoGrid" );
  2. var dataSource = grid.dataSource;
  3. //records on current view / page.
  4. var recordsOnCurrentView = dataSource.view().length;
  5. //total records.
  6. var totalRecords = dataSource.total();

How do I count all rows in a table? ›

Use the COUNT aggregate function to count the number of rows in a table. This function takes the name of the column as its argument (e.g., id ) and returns the number of rows for this particular column in the table (e.g., 5).

What is the difference between count () and RowCount ()? ›

So, @@RowCount is used to check number of rows affected only after a query execution. But Count(*) is a function, which will return number of rows fetched from the SELECT Query only. After SELECT statement also giving number of row retrived from the query.

Which command is used to count the number of rows? ›

The SQL COUNT( ) function is used to return the number of rows in a table. It is used with the Select( ) statement.

How is data stored in Dataverse? ›

Data within Dataverse is stored within a set of tables. A table is a set of rows (formerly referred to as records) and columns (formerly referred to as fields/attributes). Each column in the table is designed to store a certain type of data, for example, name, age, salary, and so on.

Can you edit a data table in Power Apps? ›

Select + (Insert) > Edit form. On the right-pane, choose the table as the data source for the edit form control. Use the Edit fields properties option to select the columns to show on the edit form control. You can also change the column order as appropriate.

How many apps can use the same table in Dataverse? ›

Yes, you can use the same table on as many apps you need. Each app need a connection to this table.

How do you create a Dataverse database for power automate? ›

Add a Microsoft Dataverse database as a Microsoft Teams user
  1. Sign in to Microsoft Teams.
  2. Select More added apps (...).
  3. Search for Approvals, and then select the appropriate app.
  4. Select New approval request on the top right of the app.
  5. Provide a name for your request. ...
  6. Populate your username in the Approvers list.
14 Nov 2022

What is table in Dataverse? ›

Tables are used to model and manage business data. When you develop an app, you can use standard tables, custom tables, or both. Microsoft Dataverse provides standard tables by default. These are designed, in accordance with best practices, to capture the most common concepts and scenarios within an organization.

When creating a table What are the two types of relationships you can create in Dataverse? ›

1:N relationships actually exist between tables and refer to each table as either a Primary/Current table or Related table. The related table, sometimes called the child table, has a lookup column that allows storing a reference to a row from the primary table, sometimes called the parent table.

How do I get row ID in power automate Dataverse? ›

Get a row by ID from Dataverse
  1. Select New step to add an action to your flow.
  2. Enter get row into the Search connectors and actions search box on the Choose an operation card.
  3. Select Microsoft Dataverse.
  4. Select the Get a row by ID action.

How do you view a table in Dataverse? ›

Tip #1433: How to list all Dataverse tables in style
  1. Expand Data, select Tables.
  2. Locate and select table Entity. If you can't find it, make sure to switch the view from Default to All.
  3. Select Data tab, optionally change the view to All columns.
22 Mar 2022

How can we create relationship between two tables in power app? ›

On powerapps.com, expand the Data section, and then select Tables on the left navigation pane. Select the Relationships area. Select Add relationship, and then select a relationship type, such as Many-to-one. On the right pane, select a Related table for the Current table, and then select Done.

How do you create a relationship between two tables in power query in Excel? ›

Select the Home ribbon > Manage relationships, then select the relationship and then select Edit. Double-click any line between two tables. Right-click any line between two tables and then choose Properties. Select any line between two tables, then select Open relationship editor in the Properties pane.

How do I count rows in a table in Powerapps? ›

If you want to show the count of records from the Dataverse in a Label you need to include the column name to count. It is not as simple as ContRows(Collection) or CountRows(Gallery). E.g. CountRows(Customers. cr063_name) => counts the number of rows in the customer table.

Videos

1. 📱 Microsoft Dataverse Table Setup, Import Excel Data & Configure Canvas PowerApps
(KeaPoint Tech Tips)
2. Using APIs With Microsoft Flow & PowerApps
(MS Power Platform Tutorials)
3. Dataverse APIs
(Microsoft Power Platform)
4. Read related record information using Get Query in WEB API in Power Automate HTTP Action Dataverse
(Softchief Learn)
5. Lesson178 - Dataverse Show,Add data in table - Power Apps 1000 Videos
(Power Apps 1000 Videos)
6. Web API through Dataflows in Power Apps
(Eric Entsie)
Top Articles
Latest Posts
Article information

Author: Kerri Lueilwitz

Last Updated: 11/30/2022

Views: 6118

Rating: 4.7 / 5 (47 voted)

Reviews: 86% of readers found this page helpful

Author information

Name: Kerri Lueilwitz

Birthday: 1992-10-31

Address: Suite 878 3699 Chantelle Roads, Colebury, NC 68599

Phone: +6111989609516

Job: Chief Farming Manager

Hobby: Mycology, Stone skipping, Dowsing, Whittling, Taxidermy, Sand art, Roller skating

Introduction: My name is Kerri Lueilwitz, I am a courageous, gentle, quaint, thankful, outstanding, brave, vast person who loves writing and wants to share my knowledge and understanding with you.