Powershell & Microsoft Dynamics CRM: how to get results using a FetchXml

If you’ve used Microsoft CRM as a power user (on-premise or online), chances are you’ve come across the standard way of querying CRM data, FetchXml.

You can run this by hand but of course the real power of it is using it to automate tasks. And another great way to automate tasks in Windows is, naturally, powershell.

So here’s a script I’m using to run a fetch xml and export the results to a csv file:

#
# Source: DotJim blog (http://dandraka.com)
# Jim Andrakakis, May 2018
#
# ============ Constants to change ============
# note: create pwd file with the following command:
# read-host -assecurestring | convertfrom-securestring | out-file C:\temp\crmcred.pwd
$pwdFile = "C:\temp\crmcred.pwd"
$username = "myusername@mycompany.com"
$serverurl = "https://my-crm-instance.crm4.dynamics.com"
$fetchXmlFile = "c:\temp\fetch.xml"
$exportfile = "C:\temp\crm_export.csv"
$exportdelimiter = ";"
# =============================================
# ============ Login to MS CRM ============
$password = get-content $pwdFile | convertto-securestring
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username,$password
try
{
    # for on-prem use :
    # $connection = Connect-CrmOnPremDiscovery -Credential $cred -ServerUrl $serverurl
    $connection = Connect-CRMOnline -Credential $cred -ServerUrl $serverurl
    # you can also use interactive mode if you get e.g. problems with multi-factor authentication
    #$connection = Connect-CrmOnlineDiscovery -InteractiveMode -Credential $cred
}
catch
{
    Write-Host $_.Exception.Message
    exit
}
if($connection.IsReady -ne $True)
{
    $errorDescr = $connection.LastCrmError
    Write-Host "Connection not established: $errorDescr"
    exit
}
# ============ Fetch data ============
$fetchXml = [xml](Get-Content $fetchXmlFile)
$result = Get-CrmRecordsByFetch -conn $connection -Fetch $fetchXml.OuterXml
# ============ Write to file ============
# Obviously here, instead of writing to csv directly, you can loop and do whatever suits your needs, e.g. run a db query, call a web service etc etc
$result.CrmRecords | Select -Property lastname, firstname | Export-Csv -Encoding UTF8 -Path $exportfile -NoTypeInformation -Delimiter $exportdelimiter

When you use your own FetchXml, do remember to change the properties in the last line (lastname, firstname).

For a quick test, the example FetchXml I’m using is the following:

<fetch mapping="logical" version="1.0">
    <entity name="account">
        <attribute name="customertypecode" alias="customertypecode"/>
        <attribute name="name" alias="company_name"/>
        <attribute name="emailaddress1" alias="company_emailaddress1"/>
        <link-entity name="contact" from="accountid" to="accountid" link-type="inner">
            <attribute name="lastname" alias="lastname"/>
            <attribute name="firstname" alias="firstname"/>
        </link-entity>
    </entity>
</fetch>

Have fun coding!

8 thoughts on “Powershell & Microsoft Dynamics CRM: how to get results using a FetchXml

    • Hi Rishav

      The easiest and more efficient (reg. network and cpu usage) thing you can do is filter the records already on CRM. That means that the powershell code stays the same but the fetch xml changes. So if you are searching for empty last names, you can add something like:

              <link-entity name="contact" from="accountid" to="accountid" link-type="inner">
                  <attribute name="lastname" alias="lastname"/>
                  <attribute name="firstname" alias="firstname"/>
                  <filter>
                    <condition attribute="lastname" operator="eq" value="null" />
                  </filter>
              </link-entity>
      

      Note the filter expression, that’s what’s new here.

      Hope this helps,

      Jim

      • This looks really good… but I wanted to omit the whole record if the last name is missing and if all the records have missing last name then I should display a message saying “cannot export CSV as last name is missing”

      • I wanted to omit the whole record if the last name is missing

        Well, that’s exactly what the fetchxml filter does.

        and if all the records have missing last name then I should display a message

        Sure, you can do this by checking the $result.CrmRecords collection, like this:

        if ($result.CrmRecords.Count -eq 0)
        {
            Write-Host "Cannot export CSV, no records found"
        }
        

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s