Excel Template Gotcha in Microsoft Dynamics 365

Roz Millar, 21 October 2017

I recently needed to create an Excel template for a customer.  My scenario:

• The template was to be based on appointments that were ‘Regarding’ a specific record type .
• It needs to group the time spent on each of the appointments based on a value from the regarding record.
• Customer has Microsoft Dynamics CRM 2016 on premise.

Easy…or so I thought… 

So, off I went – from an Appointment view I used the Create Excel Template function to add the columns I needed and Downloaded my file. In Excel, I added my column and used an If Statement to create my grouping based on the value from the Regarding record and then used a pivot table to summarise – done!!!!

But that wasn’t quite it – once I uploaded my new template and tested it against another record - it wasn’t working - the data exported no longer had the value from the related record. The column I had added to the template was there but it had no data…Ok so maybe a momentary glitch.. but no… I tried again – this time created a new personal view with the data instead of using Edit Columns function.

But still no luck – the data is there when you Download the File but not when you generate the Template after Uploading it.
Luckily, I was able to get around this limitation with the Excel Templates as the Name of the Regarding Record contained the value I needed to group the appointments by.  But instead of a simple ‘If’ statement I now needed to find the value in the middle of the name and set a value.

Done -  Finally!    This only seems to be a limitation in CRM 2016 but it would be nice if it was resolved.