How to manipulate Common Data Service (CDS) data using PowerShell

There are times when you are just lazy and want to automate things, but there are also times when you don't have other options, like changing a field value in 1000 entities in CDS...

I haven't seen anyone manipulting CDS data using a PowerShell script so I needed to find my own way.

First try - REST api

Common way of manipulating CDS data is using a CDS REST API, but to do that, we would need Access token and this is were it becomes too complicated, so I skipped it.

Second try - XRM tooling

As we all know that CDS is sort of same as Dynamics XRM, knowing that, it appears we can use same tooling. A cool part - there is a PowerShell module for that:

Install-Module -Name Microsoft.Xrm.Tooling.CrmConnector.PowerShell

Not so detailed MSDN docs can be found here: Microsoft.Xrm.Tooling.CrmConnector.PowerShell

Connecting to CDS

Before we can manage or fetch data, we need to connect to service. Below I am sharing PowerShell script to:

• Make sure we use TLS 1.2
• Ask username and password
• Install and/or load XRM tooling module
• Fetch and allow to select environment
• Return a connection to manipulate data

    # connect to CDS
    [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.SecurityProtocolType]::Tls12
    $cred = Get-Credential -UserName username@contoso.com -Message "Enter CDS user credentials"
    if ($cred -eq $null) {
        throw "Missing credentials"
    }
    Install-Module -Name Microsoft.Xrm.Tooling.CrmConnector.PowerShell
    $CRMOrgs = Get-CrmOrganizations -Credential $Cred –OnlineType Office365
    $options = [System.Management.Automation.Host.ChoiceDescription[]] ($CRMOrgs | % { $_.FriendlyName })
    $opt = $host.UI.PromptForChoice("Select organisation", "Please select organisation to connect to" , $options, 0)
    $connection = Get-CrmConnection -OnLineType Office365 -OrganizationName $CRMOrgs[$opt].UniqueName -Credential $cred

Examples of usage

Get entity row by field value:

Function Get-RowByValue
{
    Param (
        [Parameter(Mandatory=$true)]
        $connection,
        [Parameter(Mandatory=$true)]
        [string]$value
    )

    $fetchXML =
    "<fetch mapping='logical'>
        <entity name='office'>
            <attribute name='officeid' />
            <attribute name='name' />
            <filter type='and'>
                <condition attribute='id' operator='eq' value='$value' />
            </filter>
        </entity>
    </fetch>"
    $entities = $connection.GetEntityDataByFetchSearchEC($fetchXML, "00000000-0000-0000-0000-000000000000")
    if ($entities.Count -gt 0) {
        return $entities[0]
    } else {
        return $null
    }
}

Get entity rows using custom query:

Function Get-CustomEntitiesWithoutOfficeSet
{
    Param (
        [Parameter(Mandatory=$true)]
        $connection
    )
    $fetchXML =
    "<fetch mapping='logical'>
        <entity name='customentity'>
            <attribute name='officeid' />
            <attribute name='officemapid' />
            <attribute name='name' />
            <filter type='and'>
                <condition attribute='officeid' operator='null' />
            </filter>
        </entity>
    </fetch>"
    $entities = $connection.GetEntityDataByFetchSearchEC($fetchXML, "00000000-0000-0000-0000-000000000000")
    return $entities.Entities
}

Update entity row values:

Function Set-CustomEntityOffice
{
    Param (
        [Parameter(Mandatory=$true)]
        $connection,
        [Parameter(Mandatory=$true)]
        $customEntityId,
        [Parameter(Mandatory=$true)]
        [Guid]$officeId
    )
    $officefield = New-Object -TypeName 'Microsoft.Xrm.Tooling.Connector.CrmDataTypeWrapper'
    $officefield.Type = [Microsoft.Xrm.Tooling.Connector.CrmFieldType]::Lookup
    $officefield.ReferencedEntity = "office"
    $officefield.Value = $officeId
    $updateData = New-Object -TypeName 'System.Collections.Generic.Dictionary[string, Microsoft.Xrm.Tooling.Connector.CrmDataTypeWrapper]'
    $updateData.Add("officeid", $officefield)
    $isUpdated = $connection.UpdateEntity("customentity", "customentityid", $legalEntityId, $updateData, "", $false)
    if (-not $isUpdated) {
        throw $connection.LastCrmException
    }
}