Dependent Option Sets

Dependent options sets (where the user’s choice in one pick-list filters the options in the next pick-list, and so on) are not an out-of-the-box feature in Dynamics CRM.  However, there are a number of different ways of cracking this nut, and I thought I’d explore a few of these options, namely:

  1. A no-code approach, using basic config and workflow
  2. An ISV approach, using North52’s Formula Manager
  3. A code approach, using sample code in the SDK

No code

There are some no-code samples on the web, although they use filtered lookups rather than option sets.  A Silverlight tool exists for users still on CRM 2011.

Conclusion: “move along now, these are not the option sets you are looking for”.


ISV approach

North 52’s extensible platform for Dynamics CRM helpfully includes a sample formula for dependent option sets. However, it’s not free and having to install their BPA application seems like using a sledgehammer to crack a nut.

Conclusion: “that’s no moon, it’s a space station”.


SDK code

A full description of this approach is documented on MSDN here, and if you download the Dynamics CRM SDK you’ll even find sample code and a solution package:

SDKDependentOptionSets

For the non-developers among you, don’t worry it’s not as scary as it looks.  And it works well, here are a few things I learnt along the way:

  1. The sample shows three tiers of option sets, I successfully tried it with four, and I’ve no reason to suspect it couldn’t handle more.
  2. Start by putting your dependencies in Excel, it makes it a lot easier when updating the XML sheet that’ll form one of the web resources.SDKDependentOptionSetsExcel
  3. You’ll have to manually copy the values from the Excel to the XML sheet.  The sample XML sheet is a little confusing, the comments I’ve added in the version below should help you to figure out what goes where.
     
<!--https://msdn.microsoft.com/en-us/library/gg594433.aspx-->

<!--enter the entity name -->
<DependentOptionSetConfig entity="daniel_places" >
 
<!--FIRST and SECOND LEVEL -->
<!--enter the First Level's id (Name, not Schema Name) and label (Display Name) --> 
<ParentField id="daniel_continent" label="Continent">

<!--enter the Second Level's id (Name, not Schema Name) and label (Display Name) -->
 <DependentField id="daniel_country" label="Country" />

<!--enter the first value from the FIRST LEVEL -->
 <Option value="376310000" label="Europe">
<!--enter all the SECOND LEVEL values that nest under this FIRST LEVEL value --> 
 <ShowOption value="376310000" label="England" />
 <ShowOption value="376310001" label="Wales" />
 </Option>

<!--enter the second value from the FIRST LEVEL -->
 <Option value="376310001" label="North America">
<!--enter all the SECOND LEVEL values that nest under this FIRST LEVEL value -->
 <ShowOption value="376310002" label="Canada" />
 <ShowOption value="376310003" label="USA" />
 </Option>

</ParentField>

<!--SECOND and THIRD LEVEL -->
<!--enter the Second Level's id (Name, not Schema Name) and label (Display Name) --> 
<ParentField id="daniel_country" label="Country">

<!--enter the Third Level's id (Name, not Schema Name) and label (Display Name) --> 
 <DependentField id="daniel_countystate" label="County/State" />

<!--enter the first value from the SECOND LEVEL --> 
 <Option value="376310000" label="England">
<!--enter all the THIRD LEVEL values that nest under this SECOND LEVEL value --> 
 <ShowOption value="376310006" label="Dorset" />
 <ShowOption value="376310000" label="Somerset" />
 <ShowOption value="376310001" label="Devon" />
 </Option>

<!--enter the second value from the SECOND LEVEL --> 
 <Option value="376310001" label="Wales">
<!--enter all the THIRD LEVEL values that nest under this SECOND LEVEL value --> 
 <ShowOption value="376310002" label="Conwy" />
 <ShowOption value="376310003" label="Powys" />
 <ShowOption value="376310004" label="Gwynedd" />
 </Option>

<!--enter the third value from the SECOND LEVEL -->
 <Option value="376310002" label="Canada">
 <!--enter all the THIRD LEVEL values that nest under this SECOND LEVEL value -->
 <ShowOption value="376310007" label="Ontario" />
 <ShowOption value="376310008" label="Nova Scotia" />
 <ShowOption value="376310009" label="British Columbia" />
 </Option>

<!--enter the fourth value from the SECOND LEVEL -->
 <Option value="376310003" label="USA">
<!--enter all the THIRD LEVEL values that nest under this SECOND LEVEL value -->
 <ShowOption value="376310010" label="Florida" />
 <ShowOption value="376310011" label="New York" />
 <ShowOption value="376310012" label="California" />
 </Option>

</ParentField>

<!--THIRD and FOURTH LEVEL -->
 
<!--enter the Third Level's id (Name, not Schema Name) and label (Display Name) --> 
 <ParentField id="daniel_countystate" label="County/State">
<!--enter the Fourth Level's id (Name, not Schema Name) and label (Display Name) --> 
 <DependentField id="daniel_city" label="City" />

<!--enter the first value from the THIRD LEVEL --> 
 <Option value="376310006" label="Dorset">
 <!--enter all the FOURTH LEVEL values that nest under this THIRD LEVEL value --> 
 <ShowOption value="376310000" label="Weymouth" />
 <ShowOption value="376310001" label="Dorchester" />
 </Option>

<!--enter the second value from the THIRD LEVEL --> 
 <Option value="376310000" label="Somerset">
 <!--enter all the FOURTH LEVEL values that nest under this THIRD LEVEL value --> 
 <ShowOption value="376310002" label="Taunton" />
 <ShowOption value="376310003" label="Minehead" />
 </Option>

<!--enter the third value from the THIRD LEVEL --> 
 <Option value="376310001" label="Devon">
 <!--enter all the FOURTH LEVEL values that nest under this THIRD LEVEL value --> 
 <ShowOption value="376310004" label="Exeter" />
 <ShowOption value="376310005" label="Plymouth" />
 </Option>

<!--enter the fourth value from the THIRD LEVEL --> 
 <Option value="376310002" label="Conwy">
 <!--enter all the FOURTH LEVEL values that nest under this THIRD LEVEL value --> 
 <ShowOption value="376310006" label="Betws-y-Coed" />
 <ShowOption value="376310007" label="Llandudno" />
 </Option>

<!--enter the fifth value from the THIRD LEVEL --> 
 <Option value="376310003" label="Powys">
 <!--enter all the FOURTH LEVEL values that nest under this THIRD LEVEL value --> 
 <ShowOption value="376310008" label="Hay" />
 <ShowOption value="376310009" label="Brecon" />
 </Option>

<!--enter the sixth value from the THIRD LEVEL --> 
 <Option value="376310004" label="Gwynedd">
 <!--enter all the FOURTH LEVEL values that nest under this THIRD LEVEL value --> 
 <ShowOption value="376310010" label="Bangor" />
 <ShowOption value="376310011" label="Porthmadog" />
 </Option>

<!--enter the seventh value from the THIRD LEVEL --> 
 <Option value="376310007" label="Ontario">
 <!--enter all the FOURTH LEVEL values that nest under this THIRD LEVEL value --> 
 <ShowOption value="376310012" label="Toronto" />
 <ShowOption value="376310013" label="Niagara" />
 </Option>

<!--enter the eighth value from the THIRD LEVEL --> 
 <Option value="376310008" label="Nova Scotia">
 <!--enter all the FOURTH LEVEL values that nest under this THIRD LEVEL value --> 
 <ShowOption value="376310014" label="Halifax" />
 <ShowOption value="376310015" label="Cape Breton" />
 </Option>

<!--enter the ninth value from the THIRD LEVEL --> 
 <Option value="376310009" label="British Columbia">
 <!--enter all the FOURTH LEVEL values that nest under this THIRD LEVEL value --> 
 <ShowOption value="376310016" label="Cariboo" />
 <ShowOption value="376310017" label="Stikine" />
 </Option>

<!--enter the tenth value from the THIRD LEVEL --> 
 <Option value="376310010" label="Florida">
 <!--enter all the FOURTH LEVEL values that nest under this THIRD LEVEL value --> 
 <ShowOption value="376310018" label="Miami" />
 <ShowOption value="376310019" label="Fort Lauderdale" />
 </Option>

<!--enter the eleventh value from the THIRD LEVEL --> 
 <Option value="376310011" label="New York">
 <!--enter all the FOURTH LEVEL values that nest under this THIRD LEVEL value --> 
 <ShowOption value="376310020" label="Manhattan" />
 <ShowOption value="376310021" label="Brooklyn" />
 </Option>

<!--enter the twelth value from the THIRD LEVEL --> 
 <Option value="376310012" label="California">
 <!--enter all the FOURTH LEVEL values that nest under this THIRD LEVEL value --> 
 <ShowOption value="376310022" label="Los Angeles" />
 <ShowOption value="376310023" label="San Francisco" />
 </Option>
 
 </ParentField>

<!--repeat the above ParentField pattern for additional levels -->
</DependentOptionSetConfig>

4.  When updating the XML sheet, use the field ‘Name’ not the ‘Schema Name’
5.  Likewise, when adding the OnChange events to the form, use the field ‘Name’ not the ‘Schema Name’ in the parameters:

Parameters

6.  The MSDN article says:

The label values are included to make the document easier to edit but are not used in the script that filters the options.

However, after some testing I don’t think this is correct.  In my test, the solution only worked successfully when I had the correct label values in the XML sheet.  When I swapped the label values out for random numbers, it didn’t work.

Conclusion: “Do. Or do not. There is no try”.

 

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s