POWEROBJECTS

718 Washington Ave. N. Suite #101
Minneapolis, MN 55401
View map and all Locations

Send us a message

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:

NameAttributeNamesql_in
Accountaccountcategorycodeselect distinct ‘accountcategorycode’ as AttributeName, accountcategorycode from Account(nolock) where accountcategorycode not in (1,2) union all
Accountaccountclassificationcodeselect distinct ‘accountclassificationcode’ as AttributeName, accountclassificationcode from Account(nolock) where accountclassificationcode not in (1,2,3,4,5,6,7) union all
Accountaccountratingcodeselect distinct ‘accountratingcode’ as AttributeName, accountratingcode from Account(nolock) where accountratingcode not in (1) union all
Accountaddress1_addresstypecodeselect distinct ‘address1_addresstypecode’ as AttributeName, address1_addresstypecode from Account(nolock) where address1_addresstypecode not in (1,2,3,4,5) union all
Accountaddress1_freighttermscodeselect distinct ‘address1_freighttermscode’ as AttributeName, address1_freighttermscode from Account(nolock) where address1_freighttermscode not in (1,2) union all
Accountaddress1_shippingmethodcodeselect distinct ‘address1_shippingmethodcode’ as AttributeName, address1_shippingmethodcode from Account(nolock) where address1_shippingmethodcode not in (1,2,3,4,5,6,7) union all
Accountaddress2_addresstypecodeselect distinct ‘address2_addresstypecode’ as AttributeName, address2_addresstypecode from Account(nolock) where address2_addresstypecode not in (1) union all
Accountaddress2_freighttermscodeselect distinct ‘address2_freighttermscode’ as AttributeName, address2_freighttermscode from Account(nolock) where address2_freighttermscode not in (1) union all
Accountaddress2_shippingmethodcodeselect distinct ‘address2_shippingmethodcode’ as AttributeName, address2_shippingmethodcode from Account(nolock) where address2_shippingmethodcode not in (1) union all
Accountbusinesstypecodeselect distinct ‘businesstypecode’ as AttributeName, businesstypecode from Account(nolock) where businesstypecode not in (1,2,3,4) union all
Accountcreditonholdselect distinct ‘creditonhold’ as AttributeName, creditonhold from Account(nolock) where creditonhold not in (0,1) union all
Accountcustomersizecodeselect distinct ‘customersizecode’ as AttributeName, customersizecode from Account(nolock) where customersizecode not in (1) union all
Accountcustomertypecodeselect 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
Accountdonotbulkemailselect distinct ‘donotbulkemail’ as AttributeName, donotbulkemail from Account(nolock) where donotbulkemail not in (0,1) union all
Accountdonotbulkpostalmailselect distinct ‘donotbulkpostalmail’ as AttributeName, donotbulkpostalmail from Account(nolock) where donotbulkpostalmail not in (0,1) union all
Accountdonotemailselect distinct ‘donotemail’ as AttributeName, donotemail from Account(nolock) where donotemail not in (0,1) union all
Accountdonotfaxselect distinct ‘donotfax’ as AttributeName, donotfax from Account(nolock) where donotfax not in (0,1) union all
Accountdonotphoneselect distinct ‘donotphone’ as AttributeName, donotphone from Account(nolock) where donotphone not in (0,1) union all
Accountdonotpostalmailselect distinct ‘donotpostalmail’ as AttributeName, donotpostalmail from Account(nolock) where donotpostalmail not in (0,1) union all
Accountdonotsendmmselect distinct ‘donotsendmm’ as AttributeName, donotsendmm from Account(nolock) where donotsendmm not in (0,1) union all
Accountindustrycodeselect 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
Accountisprivateselect distinct ‘isprivate’ as AttributeName, isprivate from Account(nolock) where isprivate not in (0,1) union all
Accountmergedselect distinct ‘merged’ as AttributeName, merged from Account(nolock) where merged not in (0,1) union all
Accountownershipcodeselect distinct ‘ownershipcode’ as AttributeName, ownershipcode from Account(nolock) where ownershipcode not in (1,2,3,4) union all
Accountparticipatesinworkflowselect distinct ‘participatesinworkflow’ as AttributeName, participatesinworkflow from Account(nolock) where participatesinworkflow not in (0,1) union all
Accountpaymenttermscodeselect distinct ‘paymenttermscode’ as AttributeName, paymenttermscode from Account(nolock) where paymenttermscode not in (1,2,3) union all
Accountpreferredappointmentdaycodeselect distinct ‘preferredappointmentdaycode’ as AttributeName, preferredappointmentdaycode from Account(nolock) where preferredappointmentdaycode not in (0,1,2,3,4,5,6) union all
Accountpreferredappointmenttimecodeselect distinct ‘preferredappointmenttimecode’ as AttributeName, preferredappointmenttimecode from Account(nolock) where preferredappointmenttimecode not in (1,2,3) union all
Accountpreferredcontactmethodcodeselect distinct ‘preferredcontactmethodcode’ as AttributeName, preferredcontactmethodcode from Account(nolock) where preferredcontactmethodcode not in (1,2,3,4,5) union all
Accountshippingmethodcodeselect distinct ‘shippingmethodcode’ as AttributeName, shippingmethodcode from Account(nolock) where shippingmethodcode not in (1) union all
Accountstatecodeselect distinct ‘statecode’ as AttributeName, statecode from Account(nolock) where statecode not in (0,1) union all
Accountstatuscodeselect distinct ‘statuscode’ as AttributeName, statuscode from Account(nolock) where statuscode not in (1,2,3,4,5,6) union all
Accountterritorycodeselect 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:

AttributeNameaccountcategorycode
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.