Dynamics 365 with Azure Blob Storage

Edge cases galore…
We use images in Dynamics 365 for storing customer passport photos.
We then run reports that include these images on the report.
Learning how to do this was a mission in itself but there is various documentation out there.

This was great until we started to near our disk storage quota and (at the time) additional storage was ridiculously expensive so while investigating alternative solutions I found Attachment Management by Microsoft Labs.

This solution for Dynamics 365 allows you to offload all your images and other stored attachments such as PDFs in Notes etc to Azure Blob Storage. It’s a bit of a pain in the arse to install and get running well but once it is, it becomes seamless and invisible to end-users. Right up until you run one of your fancy custom reports that pulls images…

After ~3 months of going around in circles with Microsoft Support (yeah, yeah, there was Christmas in the middle of things) it turns that that there are several things that have to happen when images are in a different location to what Dynamics 365 Reports expect them to be and there is *no* documentation from Microsoft about how it might work.

First up; when the Attachment Management Solution uploads content to Azure Blob storage it sets the ContentType or MIME type to “application/octet-stream” which means that a web browser (or report) has no way of knowing how the content is to be processed.

Azure Blob Content Type

You can change this ContentType field manually in Azure Storage Explorer 😏 or run a Powershell script to do it automatically. After much research, trial & error and gnashing of teeth I came up with the following… using information from here.

# Install-Module -Name AzureRM
$StorageAccountName = "<YourAzureStorageAccountName>"
$StorageAccountKey = "<YourStorageAccountKey>"
$Context = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey
$ContainerName = "<BlobContainerWithImages>"

$Blobs = Get-AzureStorageBlob -Context $Context -Container $ContainerName #| Where-Object $_.LastModified -gt [datetime]"2020/01/30" --Uncomment to run again and only select objects created this year.
foreach ($Blob in $Blobs){
    if ($Blob.ContentType -eq "application/octet-stream") {
        $Extn = [IO.Path]::GetExtension($Blob.Name)
        $ContentType = ""
    
        switch ($Extn) {
            ".jpg" { $ContentType = "image/jpeg" }
            ".png" { $ContentType = "image/png" }
            ".tif" { $ContentType = "image/tiff" }
            ".jpeg" { $ContentType = "image/jpeg" }
            Default { $ContentType = "" }
        }
        if ($ContentType -ne "") {
            $CloudBlockBlob = [Microsoft.WindowsAzure.Storage.Blob.CloudBlockBlob] $Blob.ICloudBlob
            $CloudBlockBlob.Properties.ContentType = $ContentType
            $CloudBlockBlob.SetProperties() 
        }
    }
}

It uses the module AzureRM which is deprecated but seems to work.

In creating the SSRS report we need to get the GUID of the file and its name using…

<fetch version="1.0" output-format="xml-platform" mapping="logical" >
  <entity name="account" >
    <attribute name="name" />
    <link-entity name="annotation" from="objectid" to="accountid" visible="false" link-type="inner" alias="ab" >
      <attribute name="documentbody" />
      <attribute name="annotationid" />
      <attribute name="filename" />
      <filter type="and" >
        <condition attribute="subject" operator="eq" value="Passport Photo" />
      </filter>
    </link-entity>
  </entity>
</fetch>

*Note – our solution relies on users adding passport photos to the Account notes with the subject “Passport Photo” and you can see that is what we search on above.

Then add an image to the report with the properties of Image Source = “External” and the expression to calculate the location of it is Storage Blob Access URL + Container + GUID + “_” + Filename as per…

="https://<URLtoYourStorageBlob>/notes/" + CStr(Fields!ab_annotationid.Value) + "_" + CStr(Fields!ab_filename.Value)

Your Storage Blob URL can be found by going into the Azure Storage Explorer, selecting a file and clicking “Copy URL”

Azure Blob File URL

The only downside of all this now is that my Powershell script needs to be run regularly to update the ContentType of any newly uploaded images. While I have a request in with the Attachment Management solution developers to set the correct MIME type on upload I’m not holding my breath for it to happen any time soon.

Microsoft Dynamics + Javascript Phone Numbers

We run Microsoft Dynamics 365 CRM at work and I’m trying to get our 3CX phone system to do phone number lookup against it. Unfortunately users are putting all sorts in the 3 phone number fields we run, including cutting & pasting from other sources.

I wanted some way of just making it easy to end up with the number in E.164 format as the input mask options offered on the forms don’t give this ability.

Since I haven’t written any Javascript inside of Dynamics I really struggled to find any good basic tutorials to get it up and running.

The trick appears to be to create a new solution and add your JavaScript to that as a Web Resource. Then “Add Existing” entities; in my case I added the Account and the forms I would be checking the OnChange event of a field.

In the form properties, under Events, add the new piece of script as a Form Library and in the properties of the field under Events, Event Handlers add the library to the OnChange Event.

One of the best bits of documentation I referenced is here as it showed how to check off the “pass execution context as first parameter” as shown.

function formatPhoneNumber(context) {
    //Establish which of the 3 phone numbers changed and get its name.
    var phone = context.getEventSource().getName(); 
    var phoneNumber = Xrm.Page.getAttribute(phone).getValue();
    if (phoneNumber == null)
        return true;
    //strip everything out apart from digits. Regex FTW.
    var fixedNumber = phoneNumber.replace(/[^0-9]/g, ''); 
    //check for NZ number
    var regex = /^0([2-9]\d{7,9})/g;  
    if (fixedNumber.search(regex) >= 0 ){
        fixedNumber = fixedNumber.replace(regex, '64$1');
    }
    // Then add the plus symbol back for E.164 compliance.
    fixedNumber = "+" + fixedNumber;
    // Set the field to the newly formatted and fixed number
    Xrm.Page.getAttribute(phone).setValue(fixedNumber);
    return true;
}