Microsoft Dynamics CRM has never had out of the box cascading Option Sets and we have seen a consistent need for this functionality. There have been some solutions put forward using XML and JS files to enable this functionality, but they can be a bit cumbersome. We have developed a solution using a single Web Resource and minimal configuration to add this functionality to any form.

In this post we will add a pair of cascading option sets, based on United State Divisions and State. Here are the defined Divisions and States for our example:

  • New England
    • Connecticut, Maine, Massachusetts, New Hampshire, Rhode Island, and Vermont
  • Mid-Atlantic
    • New Jersey, New York, and Pennsylvania
  • East North Central
    • Illinois, Indiana, Michigan, Ohio, and Wisconsin
  • West North Central
    • Iowa, Kansas, Minnesota, Missouri, Nebraska, North Dakota, and South Dakota
  • South Atlantic
    • Delaware, Florida, Georgia, Maryland, North Carolina, South Carolina, Virginia, Washington D.C., and West Virginia
  • East South Central
    • Alabama, Kentucky, Mississippi, and Tennessee
  • West South Central
    • Arkansas, Louisiana, Oklahoma, and Texas
  • Mountain
    • Arizona, Colorado, Idaho, Montana, Nevada, New Mexico, Utah, and Wyoming
  • Pacific
    • Alaska, California, Hawaii, Oregon, and Washington

Setup in our CRM are global options sets to match these two sets. The division option set is shown here:

1 - Division Option Set

On our CRM Account Form we have placed a section called Territory and on it there are two option set fields named “new_division” and “new_state”.

2 - CRM Account Form

Now that the base scenario is setup we will configure our web resource. The code for the web resource can be found here. The code is commented to explain what is going on and most of it is self-explanatory. You will need some experience with HTML, JSON and jQuery to get the full understanding, but only a couple things need to be changed so even a new developer should be able to follow this post to get it up and running. Download the HTML file from pastebin and follow along to make your edits.

The first change required is a find and replace on our fields to match your option sets. So replace “new_division” and “new_state” in the file to match your option sets respectively. Using a text editor and find and replace functionality is the easiest way to find all the instances and change only what is necessary.

The next change is a bit more daunting. In the code, you will notice a big scary block of text with numbers located on line 130. This text is in JSON format and it is located here in a nicer spaced format. I remove all the whitespace when I add it to the code in the web resource so there isn’t an issue with the code trying to makes sense of new lines and other whitespace characters.

This JSON string is what encapsulates the logic of the cascading option sets. You can read about JSON format to understand more about it at https://json.org. But for our purposes you only have to see the structure to know what to change for your own option set values, because our example is longer than most cascading lists there is a lot of data here to include the 51 states and their divisions. Let’s take the block from line 3 to line 13 in the nicely formatted JSON file. This one chunk represents the relationship of

  • New England
    • Connecticut, Maine, Massachusetts, New Hampshire, Rhode Island, and Vermont

The value on line 4 is the option set value for New England (100000000) and the values between the brackets on lines 5 to 12 are the option set values for Connecticut (100000006), Maine (100000018), Massachusetts (100000020), New Hampshire (100000028), Rhode Island (100000038), and Vermont (100000044). Essentially if New England is selected our web resource will load the second option set values to be only those specified in the JSON string.

When you are writing your own JSON string you can replace the chunks from line 3 to 98

After those changes have been made we can upload the web resource to our solution. Go to your desired solution in CRM and click on the Web Resources component and click the new button. Name your file as you choose, you can see my example below. Click the browse button and select the saved HTML file you created. Don’t forget to save.

3 - Web Resource

After it is in our solution we need to insert it on the form. Open the form you want, in our case it is the Account form. Select the section that contains the two original option sets, click the insert tab and click the Web Resource button. Select the web resource we created previously, name it accordingly and then click formatting. I recommend the following changes to the formatting tab: Number of Rows set to 4, Scrolling set to Never, Uncheck Display border. Click OK.

4 - Form Web Resource Settings

Save and publish your changes and you should have a cascading list. If there are any errors take a close look at your JSON string to ensure the format is correct and that there is no white-space and that your attribute names are spelled correctly.

When the web resource loads it hides the existing option sets and shows two option sets in the web resource that have the desired functionality. When the values of these option sets change in the web resource the changes are copied down to the CRM attributes and the form will operate as if these option sets were out of the box.

 

This Dynamics CRM Tip was written by:

Brad Aune

bradBrad Aune is a Systems Architect and Developer for Corporate Renaissance Group working within the Microsoft Dynamics CRM and Sharepoint Solutions sector of CRGroup’s business. Brad is a Microsoft Certified Technology Specialist with certifications in Dynamics CRM 2015 on the core modules and the application of CRM as well as the customization and configuration of CRM. Brad has had more than 5 years of experience with the Microsoft Dynamics Suite of applications scoping requirements and assessing business processes of clients as well as providing solutions and support.

Brad graduated first division and on the dean’s list from University of New Brunswick with a Bachelor of Computer Science.

If you have any questions or comments about this post, please contact Brad at baune@crgroup.com