Microsoft Dataverse is a powerful data platform that allows organizations to store and manage data securely. If you could create Dataverse tables with the Web API, you could save time in your development. This blog will show you how to automatically create Dataverse tables using the Web API.
Benefits of Automate Creation of Dataverse Tables using Web API
Automates the repetitive process of creating tables, saving time compared to manual configuration.
Backs the dynamic solutions where tables get generated as per business.
Ensures table configurations (columns, relationships, and permissions) are uniformly created to prevent human error.
You can write a script for adjustments that will save you work later.
Setting up Automate Creation of Dataverse Tables using Web API
Firstly, go to Power Apps Studio and log in with your organizational account.
To get the API link for a table definition in Dataverse, click on the Tables icon and select the three dots (⋮) icon beside the table.
Click on "Advanced," go to "Tools," and select "API Link to Table Definition"; this generates a Web API endpoint that provides the table’s schema, allowing developers to automate table creation, modify structures, and integrate with external applications.
Once you click on "API Link to Table Definition", you will get a Web API endpoint that provides all schema details, including the table’s structure, columns, relationships, and metadata, allowing for automation and integration with external applications.
Now, open Power Automate and create an Instant cloud flow to trigger an automated process on demand, enabling dynamic interactions with Dataverse, such as creating or modifying tables using the Web API.
Click on "More", then select "Connections" to establish a link between Power Automate and Dataverse or other services. This connection allows Power Automate to interact with Dataverse, enabling actions like retrieving data, creating records, or automating workflows.
On the search bar, type "HTTP" and select "HTTP with Entra (Microsoft Entra ID)" to establish a secure connection. This allows Power Automate to make authenticated HTTP requests to Dataverse and other Microsoft services using Entra ID for authorization.
On the URL field, enter your organization's Dataverse URL. This ensures that the HTTP request is directed to the correct Dataverse environment for executing API calls.
Enter the URLs into the two provided fields and click Save to store the configuration. This ensures that Power Automate can securely communicate with Dataverse using the specified API endpoints.
Now, click on "Create" and select "Instant cloud flow" to set up a manually triggered workflow that can execute actions, such as interacting with Dataverse via the Web API.
Give your flow a name of your choice, select the appropriate trigger "Manually trigger a flow", and click "Create" to initialize the workflow.
This will set up the flow to execute actions based on the selected trigger.
Click the plus (+) icon to add a new step, then type "Invoke" in the search bar; from the listed actions, select "Invoke an HTTP request" to configure an API call to Dataverse or other external services.
In the Parameters section, under the Method dropdown, select GET to retrieve data from the specified Dataverse API endpoint.
This allows you to fetch table schema details or records as part of your automation.
In the URL field, provide the API link to the table definition that you obtained earlier. This ensures that the HTTP request fetches the schema details and column information for the specified Dataverse table.
Repeat the same action by clicking the plus (+) icon to add a new step, search for "Invoke an HTTP request", and in the Method dropdown, select POST.
Now, in the URL field of the "Invoke an HTTP request" step, enter the POST URL you created, which should look like this,
To send a request to create or modify entity definitions in your Dataverse environment.
Enter the URL in Field To send a request to create or modify entity definitions in your Dataverse environment.
In the Advanced Parameters section, select Header and Body of the request to configure the necessary HTTP headers (like Content-Type and Accept).
Provide the request body with the data you want to send (such as the entity definition JSON).
This ensures the correct metadata is passed along with the API request.
In the Header section, enter the following key-value pairs based on your provided code
These headers are used to define the content type and API versioning for the request.
In the Body section of the request, enter the following JSON code you provided, which contains the column definitions and data types.
This JSON body defines the schema for an entity (in this case, a Bank Account) with an attribute new_AccountName of type String and sets the required properties like Display Name, Malings, and Required Level. It is used to create or update the entity in Dataverse.
Refer the Below code.
Once you have entered the header and body details, click Save to store the flow configuration. After saving, click the Test icon to trigger the flow and verify that the HTTP request is working as expected.
In the Test Flow window, select Manually and click on Test to initiate the flow. Once the test is completed, your flow will be ready to work, executing the actions as configured.
Now, you can see that your flow works, and at the top of the screen, you will see "Your flow ran successfully", indicating that the flow was triggered and executed without any issues.
Now, the table has been successfully created in Dataverse (CRM) with the columns and data types defined in your provided code.
The flow executed the API request to create the entity, including the specified attributes like new_AccountName.
This automated process ensures that the table structure aligns with your requirements, streamlining data management.
Conclusion:
Automating the creation of tables and columns in Dataverse using Power Automate and the Web API simplifies the process of managing and structuring data in CRM. By configuring HTTP requests with the necessary headers and body, you can efficiently define and customize your entity schemas. This approach not only saves time but also ensures that data structures are consistent and aligned with business requirements.
Frequently Asked Questions:
1. What is the purpose of using the Web API to create tables in Dataverse?
The Web API allows for programmatic creation and modification of tables and columns in Dataverse, making it easier to automate tasks, integrate with external systems, and ensure consistency in your data structure without manual intervention.
2. Do I need any specific permissions to create tables using the Web API?
Yes, you need appropriate permissions in your Dataverse environment, such as System Administrator or custom roles with privileges to create and modify entities, to use the Web API for creating tables and columns.
3. Can I modify existing tables using the Web API in Power Automate?
Yes, you can modify existing tables, add new columns, or change metadata using the Web API. However, certain changes might require careful consideration of dependencies or data integrity.
4. What are the common headers required when making API requests to create a table in Dataverse?
Common headers include Accept (for response format), Content-Type (to specify the format of the request body), OData-MaxVersion (for OData version), and OData-Version (for the version of OData being used).
5. How do I test my flow after setting up the Web API call in Power Automate?
After saving your flow, you can test it by selecting "Manually" under the Test Flow option and clicking Test. This will run the flow and display the success or failure status to ensure it functions as expected.
