Tip #1041: Make Dynamics 365 data import less rigid

Kevin Altman emailed jar@crmtipoftheday.com with the following question about data import in Dynamics 365:

Users want to perform updates to existing data in CRM but the export for update/re-import process is too rigid as data often changes between the export and re-import, blocking the import (I guess CRM forces you to have no changes to re-import). Are there any workarounds?

First, a few details about how the data import works. When you export to Excel, you will notice that columns A-C are hidden.

Unhide columns A-C, and you will see three columns where the header name begins with (Do not modify). If you have read tip of the day for long, you know that we like to dare greatly, so we are going to modify the do not modify columns (we also removed the tags from our mattresses).

So what are these hidden columns?

A: The GUID of the record:this is how CRM knows what record to update. If it used the record name, it would potentially hit duplicates and not know what to do. By using the unique identifier for the record, it precisely matches to the correct record. This is also how import utility and immersive Excel know when a line is an update vs. a new record to create–if there is no value in column A, it will create a new record. This is also a field that you can use to trick import to updating records that are not included in the export.

B: Row checksum: a hash that Dynamics 365 uses to see if the record has changed. That way it only updates the records that have changed, while ignoring the rest.

C: ModifiedOn: This is the important one for our example. This is the modified on date of the record in CRM. When importing, Dynamics 365 compares the date in column C with the modified on date of the record. If they are different, the import will fail for that row. This is there for a reason–if you export and wait 3 months to import, and users have subsequently made changes to the record in CRM, if it didn’t compare the dates, newer data could get overwritten by older data from the import file.

Let’s say you have a busy CRM environment where system processes and data integrations are frequently updating records. You want to do a data cleanup of your contact data. You export out your active contacts to Excel, and you have your worker bees start cleaning up the data.

If your environment is active, chances are that at least some of the records will fail to import because their modified on has changed in CRM before your data cleansers have finished their cleanup. Modified On changes if anything with the record changes, sometimes if someone saves a record without changing anything, or if someone walks in the vicinity of the record and sneezes. So even if none of the fields in the export file have changed, the record will not update because the modified on date of the record in CRM is different than the date in column C of the spreadsheet.

So how can we get around this?

Note–the following solution is not supported (as Microsoft has told you not to modify these columns) but it works and has been tested by the author of this post. He is not responsible if you mess your data up though).

So we exported our contact data out, cleaned it up, and now we want to reimport it. Here is one approach to avoid the date restriction

  1. Export a second copy of the data to be imported. In my example I called my second export export2.xlsx. Unhide columns A-C.
  2. To the right of the data table in the original spreadsheet,add a new column and format to the format that matches column C
  3. Click in row 2 of the new column and add a vlookup formula.

    =VLOOKUP(A2,export2.xlsx!Table1[#Data],3, FALSE)

    This is saying find the value in column A in the table in spreadsheet 2 and return the value from column C. This will get us the current modified on for the record. Copy this to every line of the table in the original spreadsheet (making sure the row number references are correct).

  4. Copy the values in the new column and paste (values only) to column C, overwriting the existing modified on date values.
  5. Remove the new column and hide columns A-C, then save.

You should now be able to import the file and update the records.

Photo by Sam Mgrdichian on Unsplash

Tip #1040: Why Can We Stack Values in Workflow?

Back in the days when George Doubinski used configuration, he talked about using constants in workflows.However, this is only the tip of the configuration iceberg. Just above our Default Value box we can stack values for insertion into our field.

What does it mean to stack the values and what happens when we do?

When we update a record in a workflow and select a field to update, we see a box like the one on the right. In this scenario, I have selected the Send Email step in the workflow and clicked into the To field on the Form.

By adding a series of values into my Set To box, I create a simple form of branching logic which goes like this:

If the Student/Customer field has a value use this Else
If the Reported By field has a value use this Else
If the Owning User field has a value use this Else
Send it to Leon Tribe

A lot simpler than trying to explicitly set up If-Then statements using Workflow steps.

Tip #1039: Give your customers access to e-learning

Microsoft Dynamics customers and partners are eligible to access E-Learning courses. It’s a reasonably well-known fact that Microsoft Dynamics partners are entitled to access Dynamics Learning Portal (DLP). Lesser known is the one that Microsoft Dynamics Customers are entitled to access E-Learning courses through the Microsoft Imagine Academy. Perhaps there is a small confusion caused by the fact that Imagine Academy pitches itself as provider of (highlight is mine):

industry-recognised technology skills, education and certifications students need to succeed.

Besides that, some discovered that they are unable to login into the Imagine Academy when going directly with O365 credentials, even when clearly entitled. If this is your scenario, try the instructions put together by our good friend Salim Adamon:

  • Ideally, start that with an InPrivate browser or clear your credentials cache
  • Go to Customer Source
  • Top Right => Sign In with Office 365 Organization account credentials
  • If asked, select the Customer Profile, not Partner
  • Click on Microsoft Dynamics CRM (left nav)
  • Click on Learning (left nav)
  • Click on E-Learning (left nav) => This takes you to Imagine Academy and lets you in.

Also, there is a specific help page for Dynamics channel – make sure you read it before reaching out for help.

Featured image by Ben White on Unsplash

Tip #1038: Keep frequent notifications to the business hours

Nagging is a very effective way to get urgent things done. For example, if signature is required, keep sending email reminders every hour until it’s acquired.

One of the customers successfully mastered the technique of a recurring self-calling workflow and has been successfully firing emails left and right reminding good people of the outstanding work to be done. The challenge, as it turned out, was dealing with nights and weekends. If a task cropped up on Friday at 4:59PM, for example, by Monday the inbox would have had 64 identical emails. In other words, the workflow should send emails hourly until a certain condition is met (the proverbial signature is acquired) but only during the business hours.

There were number of suggestions from my good community friends, all effectively suggesting somehow checking calendar in a custom workflow activity. Which meant dealing with calendar and calendarrules (shudder). I even got some sample code that allows counting business hours.

Then, of course, none other than Joel has appeared in his shiny Dynamics armor.

What about leveraging SLA? Business hours can be associated with SLA. Could that be used to send your reminders but not do it in business hours?

SLAs do exactly that: they keep the clock ticking, can have actions associated with the failure and success, and they can observe business hours! The only challenge is making the process recurring. SLAs are smart, as it turned out. If you have a field that denotes the starting point for your KPI, and that field is reset, the KPI clock is reset too, making the process recurrent!

The process in a nutshell:

  1. Enable target entity for SLAs, if it’s not already
  2. Create Start Timer On datetime field (needs to be updateable, e.g. createdon won’t do a good job)
  3. Create 1:N relationship with SLA KPI Instance entity – that will allow keep the timers and conditions going
  4. Create a field that can record the success, e.g. Signature acquired (boolean)
  5. Create business hours calendar and SLA for your process observing that calendar. Add details along these lines:
    • Warning on 30 minutes: send email
    • Failure at one hour. Action should include setting the Start Timer On field to the execution time of the process (now, that is). That will reset the SLA countdown – that’s the trick!
  6. Optionally add timer control, do the other good stuff… This article is a good start. Activate the SLA.
  7. Create a workflow to apply that SLA, e.g. changing the status reason to Signature required or similar. In the workflow set SLA and Start Time On fields on the record. The system will take care of the rest.

And now set the calendar to weekends only – that will show them how to leave unfinished urgent tasks on Friday night!

 

Tip #1037: Modify data source for Word templates like a boss

Word templates are not the most flexible templating solution for Dynamics 365 but for the simplistic scenarios it does work well, and is quite fast.

Built-in templates are used to be notoriously difficult to modify. Not anymore, thanks to the Document Template Manager from Jim Novak. Now that we can modify the content itself, how about refreshing the data source, e.g. when new fields were added to the entity? Good news is that it’s possible without the need to recreate the template:

  1. Download the template using XrmToolbox
  2. Change file extension from docx to zip. Yes, Word file in the new format is a zip file under the hood.
  3. Open zip using Explorer, and extract the customXml\item1.xml file.
  4. Open item1.xml using your favorite editor.
  5. Insert new fields at the beginning (or wherever) using the format <field_name>field_name</field_name>. In fact, element name can be anything, it’s only used to show the element in the list:
    Xml edit
  6. Add the fields as required:
    Word template
  7. Replace file in the archive (copy in Explorer, open zip file, navigate to customXml folder, paste, say yes to replace).
  8. Rename file back from zip to docx.
  9. Use XrmToolbox to upload the template back, replacing the existing one.
  10. And enjoy the results
    Org document

 

Tip #1036: Organization Insights Is The Administrator’s Best Friend

Dynamics now has AppSource where you can download trials and various other goodies for your Dynamics 365 instance. To access it, click on the chevron (down arrow) at the top level of your Dynamics 365 Ribbon, then click “Get more apps”.

One which is invaluable for any Dynamics instance is Organization Insights from Microsoft. This is not to be confused with the Organization Insights dashboard which comes standard with Dynamics 365 but rather a much more comprehensive set of administration tools available from the Settings area. Hit the ‘Get it now” button on the App and it will be installed into the instance of your choice. (Or just go directly – Tîpp Jäår)

The dashboards cover:

  • User management
  • System Job management
  • Plug-In management
  • API call management
  • Mailbox management
  • Storage management

If you are managing a Dynamics 365 instance and are wondering where your storage is going, why the system is slowing down, or who is and who is not using the system, the Organization Insights App is for you.

Tip #1035: HTML codes in Voice of the Customer

Today’s tip comes from Jeff Weresch. Got a tip? Send it to jar@crmtipoftheday.com.

Want to have more control over the formatting or spacing of your questions in Dynamics 365 Voice of the Customer? You can use html codes to control how the text will be rendered.

For example, we want “Highly Dissatisfied” to appear over the 1 in a NPS question and “Highly Satisfied” to appear over the 5. If you enter “Highly Dissatisfied 1” as the column header, the 1 will be in the same line as Dissatisfied and look unsightly.

By adding a <br> code, the question column header displays as desired.

Tip #1034: Set Yourself Up For Efficient Importing

Prepare for importImporting into Dynamics 365 can be concerning to even the most hardened of CRM consultants. Often, if we are importing into a production system, we have tight schedules to get in and out before the users log in.

If we are still importing data when the users come online, the system can be unacceptably slow and hinder business.

Here are some things to consider before setting the import free:

  • Workflows and plugins:In the case of synchronous jobs, if it fires on the import of every single record, it will slow down importing while the next record waits for the job to finish. In the case of asynchronous jobs, they can stack up and still be executing well after the last record comes into the system and, in the worst case scenario, a record will be viewed before all jobs have run on it.
  • Duplicate Detection: This is easy to turn on or off if you are using the Import Wizard but if you are using another import mechanism, do you need to adjust the settings so the records come in, as desired?
  • Alternate Keys: If you have these defined, make sure your new records are compliant with the rules.
  • Audit Logs: The creation of lots of records can man lots of audit entries and lots of additional storage consumed (which usually cannot be deleted for three months). Set the audit settings as you require.

By reviewing what will run, disabling processes and settings for the import where it makes sense, with the intention of re-enabling and potentially running them at the end of the import, we can save ourselves literally hours of import time and make for a much better experience for all stakeholders involved with the system.

Of course, if you want a rough indication of how long the import will take, you can always run a few test imports and vary the record totals to get an estimate.

Tip #1033: Use Fiddler to boost client-side development

Today’s tip is from Matt Beard – thank you! And, hey, you can be a guest tipster too – just send your tip to jar@crmtipoftheday.com.

As a CRM developer, I regularly run through this exact process step by step when writing WebResources such as HTML or Javascript and I’m sure others do too.

  1. Write your code in your IDE of choice
  2. Find/create the WebResource in CRM
  3. Choose your File
  4. Save & wait
  5. Publish & wait some more
  6. Navigate to the form & Refresh
  7. Find your made a tiny mistake meaning you have to repeat 1 through 6 again!

I’ve always disliked this long winded approach, until I found out about AutoResponder within Fiddler! AutoResponder lets you write a rule, either with an exact match or a regex match and allows you to return a local file to the browser rather than the original.

In this example:

Mapping web resource file in Fiddler

When we try to load the “mb_customwebresource” file from the Dynamics system, we would actually be given the one from my local hard drive meaning all I need to do during my development is make my code change, save the file locally and refresh the htm!

If I tried to actually calculate how much time this has saved me in the last month alone, my employer would be a very happy chap!

Tip #1032: Be careful with those function names

NamespacesToday’s tip is from Tom Pickles – thank you! And, hey, you can be a guest tipster too – just send your tip to jar@crmtipoftheday.com.

I fixed a curious issue today that I thought I’d share. Users had reported that when using the multi-edit form to update multiple cases, the form would freeze when they save, and never close. The records were updated, but you’d have to force refresh the browser window to continue.

After some digging, I found that this only happened when the script containing all our custom case form functions was loaded on the form. The curious thing was that the error occurred even when no functions were being called on the form, so it was purely the presence of the script that was causing the issue.

So what gives? A script sitting there doing nothing should cause no issues. It wasn’t until I started commenting out large chunks to narrow down the problem, that I hit upon the answer… a custom function named “closeWindow”.

As soon as I changed the name of this function, all started working as normal. I can only imagine that there is an internal function called “closeWindow”, which is called when closing the multi-edit form specifically. Instead of calling that, it was trying to run our one, which doesn’t work in that context.

So I guess the moral of the story is to be careful with those function names! Who knows what could be calling it…

Tîpp Jäår

Well, well, well… It sounds like a good case of spießrutenlaufen here! I truly hope that Tom either didn’t write those functions or that he did learn his lesson. Jason “I can make a kettle talk to CRM” Lattimer forewarned us about the importance of the namespaces in Javascript, like, uhm, 5 years ago but it’s never late to be reminded again!

Photo by Samuel Zeller on Unsplash