CRM 2016 – Import error – A validation error picklist is outside of the valid range

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.

Data import error 1

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

Data import 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

https://hoskcrm.crm4.dynamics.com//main.aspx?etn=opportunity&pagetype=entityrecord&id=8b16f2d8-46fa-e511-8157-c4346bac0f38#470

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

Data import error 2

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.

 

 

 

 

2 thoughts on “CRM 2016 – Import error – A validation error picklist is outside of the valid range

  1. ukcrmguru June 24, 2016 / 1:23 pm

    “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.”

    Then update them using an on-demand workflow instead, so you don’t corrupt your data.

    By the way the entity record stores an integer value (not text) and the labels come from the option set table.

    Another approach here would be to add a temporary value to the option set called “temp for import” or some such, using the faulty integer value. Then use a regular advanced find to find these and change (or clear) the option set using an on-demand workflow, then delete the option set option. Do this before export, or in the target system add the temp option before importing.

    Like

    • Hosk June 24, 2016 / 1:37 pm

      The problem I was having was I couldn’t find the records with a dodgy values because when I selected records with values, it only let me choose the existing OptionSets. Ahh you are saying I find the records using FetchXML and then create an workflow to update the records without having to make them active.

      I actually found a way to update the read only forms by using bookmarklet – god mode

      I did think about creating the missing the missing optionset values but there was only about 20 records, so I choose to update them. It’s a great suggestion which would have been a good choice if I had a lot of records to update.

      Cheers for the comment, some good alternative solutions for readers to consider

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s