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
}
}
Comments