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:

Name AttributeName sql_in
Account accountcategorycode select distinct ‘accountcategorycode’ as AttributeName, accountcategorycode from Account(nolock) where accountcategorycode not in (1,2) union all
Account accountclassificationcode select distinct ‘accountclassificationcode’ as AttributeName, accountclassificationcode from Account(nolock) where accountclassificationcode not in (1,2,3,4,5,6,7) union all
Account accountratingcode select distinct ‘accountratingcode’ as AttributeName, accountratingcode from Account(nolock) where accountratingcode not in (1) union all
Account address1_addresstypecode select distinct ‘address1_addresstypecode’ as AttributeName, address1_addresstypecode from Account(nolock) where address1_addresstypecode not in (1,2,3,4,5) union all
Account address1_freighttermscode select distinct ‘address1_freighttermscode’ as AttributeName, address1_freighttermscode from Account(nolock) where address1_freighttermscode not in (1,2) union all
Account address1_shippingmethodcode select distinct ‘address1_shippingmethodcode’ as AttributeName, address1_shippingmethodcode from Account(nolock) where address1_shippingmethodcode not in (1,2,3,4,5,6,7) union all
Account address2_addresstypecode select distinct ‘address2_addresstypecode’ as AttributeName, address2_addresstypecode from Account(nolock) where address2_addresstypecode not in (1) union all
Account address2_freighttermscode select distinct ‘address2_freighttermscode’ as AttributeName, address2_freighttermscode from Account(nolock) where address2_freighttermscode not in (1) union all
Account address2_shippingmethodcode select distinct ‘address2_shippingmethodcode’ as AttributeName, address2_shippingmethodcode from Account(nolock) where address2_shippingmethodcode not in (1) union all
Account businesstypecode select distinct ‘businesstypecode’ as AttributeName, businesstypecode from Account(nolock) where businesstypecode not in (1,2,3,4) union all
Account creditonhold select distinct ‘creditonhold’ as AttributeName, creditonhold from Account(nolock) where creditonhold not in (0,1) union all
Account customersizecode select distinct ‘customersizecode’ as AttributeName, customersizecode from Account(nolock) where customersizecode not in (1) union all
Account customertypecode select distinct ‘customertypecode’ as AttributeName, customertypecode from Account(nolock) where customertypecode not in (1,10,11,12,2,3,4,5,6,7,8,9) union all
Account donotbulkemail select distinct ‘donotbulkemail’ as AttributeName, donotbulkemail from Account(nolock) where donotbulkemail not in (0,1) union all
Account donotbulkpostalmail select distinct ‘donotbulkpostalmail’ as AttributeName, donotbulkpostalmail from Account(nolock) where donotbulkpostalmail not in (0,1) union all
Account donotemail select distinct ‘donotemail’ as AttributeName, donotemail from Account(nolock) where donotemail not in (0,1) union all
Account donotfax select distinct ‘donotfax’ as AttributeName, donotfax from Account(nolock) where donotfax not in (0,1) union all
Account donotphone select distinct ‘donotphone’ as AttributeName, donotphone from Account(nolock) where donotphone not in (0,1) union all
Account donotpostalmail select distinct ‘donotpostalmail’ as AttributeName, donotpostalmail from Account(nolock) where donotpostalmail not in (0,1) union all
Account donotsendmm select distinct ‘donotsendmm’ as AttributeName, donotsendmm from Account(nolock) where donotsendmm not in (0,1) union all
Account industrycode select distinct ‘industrycode’ as AttributeName, industrycode from Account(nolock) 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
Account isprivate select distinct ‘isprivate’ as AttributeName, isprivate from Account(nolock) where isprivate not in (0,1) union all
Account merged select distinct ‘merged’ as AttributeName, merged from Account(nolock) where merged not in (0,1) union all
Account ownershipcode select distinct ‘ownershipcode’ as AttributeName, ownershipcode from Account(nolock) where ownershipcode not in (1,2,3,4) union all
Account participatesinworkflow select distinct ‘participatesinworkflow’ as AttributeName, participatesinworkflow from Account(nolock) where participatesinworkflow not in (0,1) union all
Account paymenttermscode select distinct ‘paymenttermscode’ as AttributeName, paymenttermscode from Account(nolock) where paymenttermscode not in (1,2,3) union all
Account preferredappointmentdaycode select distinct ‘preferredappointmentdaycode’ as AttributeName, preferredappointmentdaycode from Account(nolock) where preferredappointmentdaycode not in (0,1,2,3,4,5,6) union all
Account preferredappointmenttimecode select distinct ‘preferredappointmenttimecode’ as AttributeName, preferredappointmenttimecode from Account(nolock) where preferredappointmenttimecode not in (1,2,3) union all
Account preferredcontactmethodcode select distinct ‘preferredcontactmethodcode’ as AttributeName, preferredcontactmethodcode from Account(nolock) where preferredcontactmethodcode not in (1,2,3,4,5) union all
Account shippingmethodcode select distinct ‘shippingmethodcode’ as AttributeName, shippingmethodcode from Account(nolock) where shippingmethodcode not in (1) union all
Account statecode select distinct ‘statecode’ as AttributeName, statecode from Account(nolock) where statecode not in (0,1) union all
Account statuscode select distinct ‘statuscode’ as AttributeName, statuscode from Account(nolock) where statuscode not in (1,2,3,4,5,6) union all
Account territorycode select distinct ‘territorycode’ as AttributeName, territorycode from Account(nolock) where territorycode not in (1) union all

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:

AttributeName accountcategorycode
accountclassificationcode

100000000

address2_addresstypecode

4

industrycode

41

industrycode

42

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!

Avatar for Joe D365

Joe D365

Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.