Web API table schema operations sample (PowerShell)

This PowerShell sample demonstrates how to perform operations that create and modify table, column, and relationship definitions using the Dataverse Web API.

This sample implements the Dataverse operations and console output detailed in Web API table schema operations sample and uses the Dataverse Web API PowerShell Helper functions to manage authentication and provide reusable functions to perform common operations. These scripts are referenced using dot sourcing with the following lines:

. $PSScriptRoot\..\Core.ps1
. $PSScriptRoot\..\TableOperations.ps1
. $PSScriptRoot\..\CommonFunctions.ps1
. $PSScriptRoot\..\MetadataOperations.ps1

Prerequisites

Before running this sample, you should read these articles that explain concepts and patterns used by these samples:

• Quick Start Web API with PowerShell and Visual Studio Code
• Use PowerShell and Visual Studio Code with the Dataverse Web API

These articles have the same prerequisites.

Install or verify that the following are installed

• Install Visual Studio Code. See Download Visual Studio Code

• Install the PowerShell extension for Visual Studio Code. See PowerShell for Visual Studio Code

• Install PowerShell 7.4 or higher. See Install PowerShell on Windows, Linux, and macOS

• Install the Az PowerShell module version 11.1.0 or higher. See How to install Azure PowerShell

  To update an existing installation to the latest version, use Update-Module -Name Az -Force

Verify installation

1. Open Visual Studio Code.

2. In the Terminal menu, select New Terminal.

3. In Visual Studio Code navigation pane, select the icon for the PowerShell extension.

4. Copy and paste the following script in the Visual Studio Code terminal window:

   Write-Host 'PowerShell Version:'$PSVersionTable.PSVersion.ToString()
   Write-Host 'PowerShell Az version:'(Get-InstalledModule Az).Version
   

5. Press Enter. The output should resemble the following:

   PowerShell Version: 7.4.0
   PowerShell Az version: 11.1.0
   

If you don't see results like this, install or update the prerequisites.

You'll also need

• A valid user account for a Dataverse environment
• The Url to the Dataverse environment you want to connect to. See View developer resources to learn how to find it. It looks something like this: https://yourorg.crm.dynamics.com/, where yourorg.crm is different.
• Basic understanding of the PowerShell scripting language

How to run this sample

1. Clone or download the PowerApps-Samples repository.

2. Open the /dataverse/webapi/PS/MetadataOperations/MetadataOperationsSample.ps1 file using Visual Studio Code

3. Edit this line to use the URL of the environment you want to connect to:

   Connect 'https://yourorg.crm.dynamics.com/' # change this

4. (Optional) Set the $deleteCreatedRecords variable to $false if you don't want to delete the records this sample creates.

5. Press F5 to run the sample.

6. The first time you run the sample a browser window opens. In the browser window, enter or select the credentials you want to use to authenticate.

To connect as a different user, run the Disconnect-AzAccount command and try again.

Code

The code for this sample is at: PowerApps-Samples/dataverse/webapi/PS/MetadataOperations/MetadataOperationsSample.ps1

Demonstrates

This sample has 11 regions:

Section 0: Create Publisher and Solution

Operations: Create a solution record and an associated publisher record.

Section 1: Create, Retrieve and Update Table

Operations:

1. Create a new sample_BankAccount user-owned table by sending a POST request to /EntityDefinitions.
2. Retrieve the created table by sending a GET request to /EntityDefinitions(LogicalName='sample_bankaccount').
3. Update the table by sending a PUT request to /EntityDefinitions(LogicalName='sample_bankaccount').

Section 2: Create, Retrieve and Update Columns

Operations:

1. Attempt to retrieve a sample_boolean boolean column by sending a GET request to /EntityDefinitions(LogicalName='sample_bankaccount')/Attributes(LogicalName='sample_boolean').
2. If the column doesn't already exist, create a new sample_boolean boolean column for the sample_BankAccount table by sending a POST request to /EntityDefinitions(LogicalName='sample_bankaccount')/Attributes.
3. Update the sample_boolean boolean column by sending a PUT request to /EntityDefinitions(LogicalName='sample_bankaccount')/Attributes(LogicalName='sample_boolean').
4. Update the option labels for the sample_boolean boolean column using the UpdateOptionValue action.
5. Attempt to retrieve a sample_datetime datetime column for the sample_BankAccount table, and create it if it doesn't exist.
6. Attempt to retrieve a sample_decimal decimal column for the sample_BankAccount table, and create it if it doesn't exist.
7. Attempt to retrieve asample_integer integer column for the sample_BankAccount table, and create it if it doesn't exist.
8. Attempt to retrieve a sample_memo memo column for the sample_BankAccount table, and create it if it doesn't exist.
9. Attempt to retrieve a sample_money money column for the sample_BankAccount table, and create it if it doesn't exist.
10. Attempt to retrieve a sample_choice choice column for the sample_BankAccount table, and create it if it doesn't exist.
11. Add a new option to the sample_choice column using the InsertOptionValue action.
12. Change the order of the options of the sample_choice column using the OrderOption action.
13. Delete one of the options of the sample_choice column using the DeleteOptionValue action.
14. Attempt to retrieve a sample_multiselectchoice multi-select choice column for the sample_BankAccount table, and create it if it doesn't exist.
15. Create a new Status option for the sample_BankAccount table using the InsertStatusValue Action.

Section 3: Create and use Global OptionSet

Operations:

1. Create a new global choice named sample_colors by sending a POST request to /GlobalOptionSetDefinitions.
2. Retrieve the sample_colors global choice by sending a GET request to /GlobalOptionSetDefinitions(<id value>).
3. Create a new sample_colors choice column for the sample_BankAccount table using the sample_colors global choice by sending a POST request to /EntityDefinitions(LogicalName='sample_bankaccount')/Attributes and associating it to the global choice.

Section 4: Create Customer Relationship

Operations:

1. Create a new sample_customerid customer column for the sample_BankAccount table using the CreateCustomerRelationships Action.
2. Retrieve the sample_customerid customer column by sending a GET request to /EntityDefinitions(LogicalName='sample_bankaccount')/Attributes(LogicalName='sample_customerid').
3. Retrieve the relationships created for the customer column by sending GET requests to /RelationshipDefinitions(<id>)/Microsoft.Dynamics.CRM.OneToManyRelationshipMetadata.`.

Section 5: Create and retrieve a one-to-many relationship

Operations:

1. Verify that the sample_BankAccount table is eligible to be referenced in a 1:N relationship using the CanBeReferenced function.
2. Verify that the contact table is eligible to be reference other tables in a 1:N relationship using the CanBeReferencing function.
3. Identify what other tables can reference the sample_BankAccount table in a 1:N relationship using the GetValidReferencingEntities function.
4. Create a 1:N relationship between sample_BankAccount and contact tables by sending a POST request to /RelationshipDefinitions.
5. Retrieve the 1:N relationship by sending GET request to /RelationshipDefinitions(<id>)/Microsoft.Dynamics.CRM.OneToManyRelationshipMetadata.

Section 6: Create and retrieve a many-to-one relationship

Operations:

1. Create a N:1 relationship between sample_BankAccount and account tables by sending POST a request to /RelationshipDefinitions.
2. Retrieve the N:1 relationship by sending a GET request to /RelationshipDefinitions(<id>)/Microsoft.Dynamics.CRM.OneToManyRelationshipMetadata.

Section 7: Create and retrieve a many-to-many relationship

Operations:

1. Verify that the sample_BankAccount and contact tables are eligible to participate in a N:N relationship using the CanManyToMany function.
2. Verify that the sample_BankAccount and contact tables are eligible to participate in a N:N relationship using the GetValidManyToMany Function.
3. Create a N:N relationship between sample_BankAccount and contact tables by sending a POST request to /RelationshipDefinitions.
4. Retrieve the N:N relationship by sending GET request to /RelationshipDefinitions(<id>)/Microsoft.Dynamics.CRM.ManyToManyRelationshipMetadata.

Section 8: Export managed solution

Operations: Export the solution created in Section 0: Create Publisher and Solution containing the items created in this sample using the ExportSolution Action.

Section 9: Delete sample records

Operations: A reference to each record created in this sample was added to a list as it was created. In this sample the records are deleted in the reverse order in which they were created.

Section 10: Import and Delete managed solution

Operations:

1. Import the solution exported in Section 8 using the ImportSolution action.
2. Query the solution table to get the solutionid of the imported solution.
3. Delete the imported solution using the solutionid.

Clean up

By default this sample will delete all the records created in it. If you want to view created records after the sample is completed, change the deleteCreatedRecords variable to false and you will be prompted to decide if you want to delete the records.

See also

Use the Dataverse Web API
Use the Web API with table definitions
Web API Samples
Web API Basic Operations Sample (PowerShell)