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

Openbuilds WorkBee CNC machine.

I built a CNC machine in my garage. I sent the below email to some people wanting to know about my build and then thought I’d post it here for future reference or for anyone googling similar. Probably the links to AliExpress below will break over time but here you go…

To create a shape for cutting on your new CNC machine first you draw this shape up in a computer program that creates G-Code. There are many of these about and some very specialised but I choose to use Autodesk’s Fusion 360 because it’s a very high end product that is free to use for non-commercial use. I watched loads and loads of YouTube videos on how to use it and then create the toolpaths in the ‘Manufacturing’ module that you then export G-Code from.

Once you have some valid G-Code you need to open it up in an interface to send it to your CNC controller. Again there are loads of programs to do this and loads of users swear by purchasing something like Mach3 (or Mach 4) but I was choosing to go as cheap as possible and am using OpenBuilds CONTROL Software which does the trick.

Initially I was getting weird errors when running my G-Code and found that there was a much better G-Code post-processor which can you install in Fusion 360 to send more compatible G-Code for the ‘GRBL’ Controller I use. More info and download here – https://github.com/Strooom/GRBL-Post-Processor

OpenBuilds CONTROL Software communicates with your CNC Controller – it was here that I deviated from a standard OpenBuilds CNC kit as I already had an Arduino UNO lying about, but they are super cheap anyway

I also got a prototyping screw board to sit on top of it to allow easy access to screw connect wires to the Arduino.

The software (firmware) that gets installed on the Arduino is called GRBL – it takes the G-Code you send it and converts it into electrical signals for the motor controllers. You can find out all about it and download it from https://github.com/gnea/grbl/wiki

You flash it to your Arduino and you are good to have a play – having only spent about $8 at this point.

Once you are ready to commit to the machine you need to spend some real money.

The machine I have created is based upon an OpenBuilds WorkBee, of which you can find full details of here – https://openbuilds.com/builds/openbuilds-workbee-1510.7189/. Even within this machine design there are many options along the way including size, how it’s driven (belt or screw), and things like the controller, motor drivers etc.

Mine is a 750×100 Screw driven unit from these guys https://www.aliexpress.com/store/3881013 who I found to be very good. It included the instructions from these guys in the UK who sell a whole kit but it’s friggin expensive to consider getting it to NZ from them. The assembly manual can be found here https://ooznest.co.uk/wp-content/uploads/2019/02/WorkBee-Screw-Assembly-Manual-01-02-2019.pdf which will give you a really good idea of what’s involved.

Rather than purchase a full kit I opted for what they call an upgrade kit as it came with free shipping. While the exact link for mine has gone I believe it to be this.

A lot of my purchasing decisions were based around the cost of the freight as (for instance) purchasing the whole kit came to more than purchasing individual items with free freight and it gave me the flexibility to use my own controller and motor drivers for a much more powerful machine.

The Arduino UNO (CNC Controller) gets connected to the motor controllers of which you’ll need four – 1 for the Z axis, 1 for the X axis and 2 for the Y axis as you drive both sides.

For the motor controllers I elected to go with much higher power units than are generally used in the kit meaning that my motors will have much higher torque etc – I purchased 4 of these.

You need a power supply to supply voltage to the motors via the motor controllers and I got a 36V 10A one from here.

To connect things up I got a few other items such as…

To cut something like wood you’ll need a router and I purchased a Makita from Mitre10 – https://www.makita.co.nz/products/model/RT0700C

To mount your router or whatever you are going to use to cut with you’ll need a z-axis Router mount of the correct diameter for your router – my Makita is 65mm for instance – Obviously mounting the Plasma Cutter on there will be quite different but I’ve seen people do it with cable-ties etc. Google is your friend here.

To tidy up all the cabling I purchased a 2 Piece pack of these later on. It’s not necessary to run it but it does make things tidier

Also the build kit doesn’t come with limit switches so I ordered these – I didn’t use them initially but they make a significant difference so just do it.

I think that’s about it. Let me know if you have any questions.

Box of bits