Automating Dataverse Record Updates using PowerShell

Updating records in Microsoft Dataverse manually can be time-consuming, especially when dealing with large datasets. Using PowerShell, you can automate these updates efficiently.

This guide explains how to automate Dataverse record updates using PowerShell, covering:
Connecting to Dataverse
Retrieving existing records
Updating specific records
Automating bulk updates
Exporting update logs

Step 1: Prerequisites

1. Required Permissions

System Administrator or Dataverse Security Role with Write/Update permissions on the target table.
Power Platform API enabled.

2. Install and Import Required Modules

Ensure you have the Power Platform and Dataverse PowerShell modules installed.

# Install Power Platform Administration module
Install-Module -Name Microsoft.PowerPlatform.Administration -Scope CurrentUser -Force

# Install Dataverse Client module
Install-Module -Name Microsoft.PowerPlatform.Cds.Client -Scope CurrentUser -Force

# Import the modules
Import-Module Microsoft.PowerPlatform.Administration
Import-Module Microsoft.PowerPlatform.Cds.Client

Step 2: Connect to Dataverse

Option 1: Interactive Login

# Connect to Dataverse interactively
$connection = Connect-CdsService -ConnectionString "AuthType=OAuth;Url=https://yourorg.crm.dynamics.com;Prompt=Login"

A sign-in window will appear for authentication.

Option 2: Using Service Principal (App Registration)

For automation scripts, use an Azure AD App Registration.

# Define credentials
$clientId = "your-app-client-id"
$clientSecret = "your-app-client-secret"
$tenantId = "your-tenant-id"
$orgUrl = "https://yourorg.crm.dynamics.com"

# Convert secret to secure string
$secureSecret = ConvertTo-SecureString $clientSecret -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($clientId, $secureSecret)

# Connect to Dataverse
$connection = Connect-CdsService -Url $orgUrl -ClientId $clientId -ClientSecret $secureSecret -TenantId $tenantId

Step 3: Retrieve Existing Records in Dataverse

# Define table name
$tableName = "account"   # Change to your table name

# Fetch all records from the table
$records = Get-CdsRecord -Connection $connection -EntityLogicalName $tableName

# Display records
$records | Select-Object accountid, name

This retrieves all records from the specified table.

Step 4: Update a Single Record in Dataverse

# Define record ID and new values
$recordId = "GUID-of-the-record"   # Replace with the actual record ID
$newValues = @{
    "name" = "Updated Company Name"
    "address1_city" = "New York"
}

# Update the record
Set-CdsRecord -Connection $connection -EntityLogicalName $tableName -Id $recordId -Fields $newValues

Write-Host "Record updated successfully!"

This updates the name and city fields of a specific record.

Step 5: Automate Bulk Record Updates in Dataverse

# Define update parameters
$tableName = "account"
$filter = "address1_city eq 'Los Angeles'"  # Update only records in Los Angeles
$updateValues = @{
    "address1_city" = "San Francisco"
}

# Fetch records that match the filter
$records = Get-CdsRecord -Connection $connection -EntityLogicalName $tableName -Filter $filter

# Loop through records and update
foreach ($record in $records) {
    $recordId = $record.accountid
    Set-CdsRecord -Connection $connection -EntityLogicalName $tableName -Id $recordId -Fields $updateValues
    Write-Host "Updated Record ID: $recordId"
}

Write-Host "Bulk update completed successfully!"

This script updates all Los Angeles-based accounts to San Francisco.

Step 6: Logging Updates to a CSV File

# Define log file path
$csvFilePath = "C:\Dataverse_Export\UpdatedRecords.csv"

# Initialize log list
$logList = @()

# Update records and log changes
foreach ($record in $records) {
    $recordId = $record.accountid
    $oldValue = $record.address1_city
    $newValue = "San Francisco"

    # Update record
    Set-CdsRecord -Connection $connection -EntityLogicalName $tableName -Id $recordId -Fields $updateValues

    # Store log entry
    $logList += [PSCustomObject]@{
        RecordID = $recordId
        OldValue = $oldValue
        NewValue = $newValue
        UpdatedAt = Get-Date
    }
}

# Export log to CSV
$logList | Export-Csv -Path $csvFilePath -NoTypeInformation -Encoding UTF8

Write-Host "Update log saved to $csvFilePath"

This logs all updates, including old values, new values, and timestamps.

Step 7: Disconnect from Dataverse

Disconnect-CdsService -Connection $connection
Write-Host "Disconnected from Dataverse."