When optionset values need to change in Dynamics 365 it isn't uncommon for people to delete whatever values are there and recreate the values they need. This can cause problems in the system, as the optionset values still exist within CRM database.

We recently did a migration from an on-premises Dynamics 365 to Online, where we kept encountering these invalid optionsets. Although we had recreated the optionsets with the same values as their on-premises Dynamics 365, because we had these invalid fragments we kept getting quite a few invalid optionset value errors when migrating records.

After some querying, we came up with the following method to identify these invalid values, so we could fix them in our ETL package before we attempt to create the record Online.

Start by using this query:

with cte as(

select

e.Name

,sm.AttributeName

,sm.AttributeValue

,'select distinct '''+ sm.AttributeName + ''' as AttributeName, ' + sm.AttributeName + ' from ' + e.name + '(nolock) where ' + sm.attributename + ' not in ' as [SQL]

from dbo.StringMap sm

join dbo.Entity e on sm.ObjectTypeCode = e.ObjectTypeCode

where sm.LangId = 1033

and e.Name = 'account'

)

select

cte.Name

,cte.AttributeName

,sql_in = cte.SQL + '(' + stuff((select distinct ',' + cast(p2.AttributeValue as nvarchar(20))

                from cte p2

                where p2.AttributeName = cte.AttributeName

                and p2.Name = cte.Name

                for xml path('')),1,1,'') + ') union all'

from cte

group by cte.Name, cte.AttributeName, cte.SQL

This will output a query in the field [sql_in] that can be copied and pasted into another query. The resultset looks something like this:

From here copy and paste [sql_in] into another query. Before running the query, be sure to delete the last union all at the end of the query. In this case it would look like this:

select distinct 'accountcategorycode' as AttributeName, accountcategorycode from Account where accountcategorycode not in (1,2) union all

select distinct 'accountclassificationcode' as AttributeName, accountclassificationcode from Account where accountclassificationcode not in (1,2,3,4,5,6,7) union all

select distinct 'accountratingcode' as AttributeName, accountratingcode from Account where accountratingcode not in (1) union all

select distinct 'address1_addresstypecode' as AttributeName, address1_addresstypecode from Account where address1_addresstypecode not in (1,2,3,4,5) union all

select distinct 'address1_freighttermscode' as AttributeName, address1_freighttermscode from Account where address1_freighttermscode not in (1,2) union all

select distinct 'address1_shippingmethodcode' as AttributeName, address1_shippingmethodcode from Account where address1_shippingmethodcode not in (1,2,3,4,5,6,7) union all

select distinct 'address2_addresstypecode' as AttributeName, address2_addresstypecode from Account where address2_addresstypecode not in (1) union all

select distinct 'address2_freighttermscode' as AttributeName, address2_freighttermscode from Account where address2_freighttermscode not in (1) union all

select distinct 'address2_shippingmethodcode' as AttributeName, address2_shippingmethodcode from Account where address2_shippingmethodcode not in (1) union all

select distinct 'businesstypecode' as AttributeName, businesstypecode from Account where businesstypecode not in (1,2,3,4) union all

select distinct 'creditonhold' as AttributeName, creditonhold from Account where creditonhold not in (0,1) union all

select distinct 'customersizecode' as AttributeName, customersizecode from Account where customersizecode not in (1) union all

select distinct 'customertypecode' as AttributeName, customertypecode from Account where customertypecode not in (1,10,11,12,2,3,4,5,6,7,8,9) union all

select distinct 'donotbulkemail' as AttributeName, donotbulkemail from Account where donotbulkemail not in (0,1) union all

select distinct 'donotbulkpostalmail' as AttributeName, donotbulkpostalmail from Account where donotbulkpostalmail not in (0,1) union all

select distinct 'donotemail' as AttributeName, donotemail from Account where donotemail not in (0,1) union all

select distinct 'donotfax' as AttributeName, donotfax from Account where donotfax not in (0,1) union all

select distinct 'donotphone' as AttributeName, donotphone from Account where donotphone not in (0,1) union all

select distinct 'donotpostalmail' as AttributeName, donotpostalmail from Account where donotpostalmail not in (0,1) union all

select distinct 'donotsendmm' as AttributeName, donotsendmm from Account where donotsendmm not in (0,1) union all

select distinct 'industrycode' as AttributeName, industrycode from Account where industrycode not in (1,10,11,12,13,14,15,16,17,18,19,2,3,34,35,36,37,38,39,4,40,5,6,7,8,9) union all

select distinct 'isprivate' as AttributeName, isprivate from Account where isprivate not in (0,1) union all

select distinct 'merged' as AttributeName, merged from Account where merged not in (0,1) union all

select distinct 'ownershipcode' as AttributeName, ownershipcode from Account where ownershipcode not in (1,2,3,4) union all

select distinct 'participatesinworkflow' as AttributeName, participatesinworkflow from Account where participatesinworkflow not in (0,1) union all

select distinct 'paymenttermscode' as AttributeName, paymenttermscode from Account where paymenttermscode not in (1,2,3) union all

select distinct 'preferredappointmentdaycode' as AttributeName, preferredappointmentdaycode from Account where preferredappointmentdaycode not in (0,1,2,3,4,5,6) union all

select distinct 'preferredappointmenttimecode' as AttributeName, preferredappointmenttimecode from Account where preferredappointmenttimecode not in (1,2,3) union all

select distinct 'preferredcontactmethodcode' as AttributeName, preferredcontactmethodcode from Account where preferredcontactmethodcode not in (1,2,3,4,5) union all

select distinct 'shippingmethodcode' as AttributeName, shippingmethodcode from Account where shippingmethodcode not in (1) union all

select distinct 'statecode' as AttributeName, statecode from Account where statecode not in (0,1) union all

select distinct 'statuscode' as AttributeName, statuscode from Account where statuscode not in (1,2,3,4,5,6) union all

select distinct 'territorycode' as AttributeName, territorycode from Account where territorycode not in (1)

Execute this query, and you should have a list of all Attribute Values in the database that don't have a valid value within the String Map. Our output looked like this:

Now that we had this list handy, we could address these invalid values within our ETL package before trying to create the record online. We had to do the same process across multiple entities, which can be done by simply altering the syntax on the first query to e.Name = 'whatever entity you need'.

Subscribe to our blog to receive more helpful Dynamics 365 tips!

Happy Dynamics 365'ing!