In this webinar, our experts showcase a variety of demo use cases of how different components of the...
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!