Hosk the master of the CRM error blog post said no one ever, steam rolled into another frustrating error. Exporting and importing data rarely goes without problems because data always finds a way to get itself into a non importable format
The task
I was importing 300 opportunities from a CRM 2015 Online instance to a CRM 2016 online instance. There was much data and the customer didn’t want to pay so I was using free solutions and not Kingsway SSIS or Scribe
Instead I am using the excellent Lucas Alexander’s Dynamics CRM Configuration Data Mover v1.10. If you have used the CRM configuration data mover before it’s awesome and you should definitely check it out.
If you haven’t heard or have never used the Dynamics CRM Configuration Data Mover then read the introduction blog page below
Introducing the Alexander Development Dynamics CRM Configuration Data Mover
If you want to know about the guids or understand a bit more about my thoughts on the CRM Configuration data mover read the blog post The importance of keeping the same guids between CRM instances
Import error
I was important an opportunity record and I got this error message using Dynamics CRM Configuration Data Mover.
If you have never used the Dynamics CRM Configuration Data Mover read the introduction blog page below
Introducing the Alexander Development Dynamics CRM Configuration Data Mover
If you want to know about the guids and understand my thoughts on the CRM Configuration data mover read the blog post The importance of keeping the same guids between CRM instances
2016-06-22 23:52:06,459 RECORD ERROR: a4115b45-e37e-e511-812b-fc15b426e6e8, opportunity, OPERATION: CREATE, MESSAGE: A validation error occurred. The value of ‘crm_picklist’ on record of type ‘opportunity’ is outside the valid range.
As error messages go this is pretty informative, I looked at the field and found it was a picklist. Out of range errors usually occur when your code goes through a loop and tries to reference a value in an array with a number larger than data in the array.
I searched the Hosk CRM blog for the answer because I remember a similar issue with Status Reason.
I found this blog post, which was interesting but didn’t help
CRM 2015 – how to find Statecode value
Next was this one, it seemed
CRM 2013 – Understanding Status and Status Reason – think before deleting them
which pointed to this blog
CRM 2013 – What happens when you delete a status reason currently in use
I felt these were pushing me down the right path and the problem was probably linked to OptionSet items which were referenced on the records but which had been removed.
The way OptionSets work is they have a text value and in another database field they contain an integer. I was guessing those records which didn’t import had an integer number which didn’t exist in the new CRM system but how could I find them.
Looking at the opportunity records I could see this field was a required field but there were 80 records which were blank. The blank records means CRM didn’t know what to show but how could I tell what integer value was held?
My previous problem was investigated by using SQL queries (look at CRM database data but don’t change it – Why you shouldn’t put unsupported customizations in Microsoft Dynamics CRM)
I wondered if FetchXML would return the integer values.
I used the awesome XRMToolBox which has the great FetchXML Builder by Jonas Rapp in it.
You can download this in the Plugin store. I had some problems with the plugin store but found a solution – XRMToolBox – loadFromRemoteSources error
I created a fetchXML query to select the opportunity records and the field from the error
You can see the some of the records are bringing back text values which are no longer valid values for the global OptionSet. When I imported these records into a new CRM instance this invalid values caused an import error.
When you remove OptionSet values make sure you migrate the records with those values before you delete the value and you will avoid this problem.
A great feature of the FetchXML builder is you can run query and then click on the records which opens them in CRM.
The problem was many of the records were inactive (Won/Lost) which meant I had to reopen them to update this field. This then reset the won date which caused a bit of a headache. There was a field of last updated, so I used that to set the date of won/closed
There is a slight but in the FetchXML builder which when you open a record by double clicking the results
it puts a double forward slash before the main.aspx, this stopped me from reopening the record but if removed one of forward slashes then I could click the reopen button
so from
https://hoskcrm.crm4.dynamics.com//main.aspx?
to
https://hoskcrm.crm4.dynamics.com/main.aspx?
I could then edit the records and fix the records which weren’t importing.
Failed to Generate Excel Error
I was getting a bit concerned because I was only missing 50 records, I thought I could manually import these but when I tried to export the data from CRM to Excel I was getting this error
This was a bit of show stopper because it didn’t give me any details about the cause of the potential problem. I was trying to export all the fields from the opportunity and I was wondering if this was too much data but when I tried to remove the fields with no data I was still getting the error.
I could export the data using FetchXML but it didn’t allow me to copy the header and there was a lot of columns.
If you get this error this blog post has a potential solution
How to Solve the ‘Failed to Generate Excel’ Error When Exporting in Microsoft Dynamics® CRM
It thinks the error is caused by different columns having the same name but I haven’t checked this out yet.
Hosk's Dynamic Blog 