How to use Javascript to create dynamic cascading picklists in Microsoft CRM 2011

Posted by on June 16, 2011  |  commentsComments (14)

Today’s guest blogger is Chris Labadie, technical sales specialist at Sonoma Partners.

Microsoft CRM 2011 includes some great new improvements to option sets (also referred to as “picklists”). Some of these improvements include:

  • The ability to create global option sets (where you can use the same set of picklist values in mulitple fields – perfect for things like country and state lists!)
  • The ability to filter the option set values based off configured criteria (classic example here is only show contacts related to the account when you’re setting the primary contact of the account)

Unfortunately, one popular request still can’t be accomplished web-based configuration tools in Microsoft CRM 2011 = dynamic cascading picklists. When we say dynamic cascading picklists, we’re referring to a picklist where the options change based on selections made in another picklist. In this blog post, we’ll show you how you can quickly and easily configure your own dynamic cascading picklists. There is a little code involved, but if you can search/replace then you can use these examples for yourself even if you’re not a programming guru.

Let’s consider an example where we are using the Opportunity entity to sell health insurance plans and there are two picklists- health plan type (HMO, PPO, or POS) and health plan policy (HMO 5K, HMO 7K, PPO 500, PPO 750, POS Silver, and POS Gold). If the Opportunity owner chooses PPO as the plan type, we want to update the health plan policy picklist to only display PPO policies. It should automatically hide the HMO options.

One way to accomplish this is to create two custom entities (Health Plan Type and Health Insurance Plan) and use the option set filter option configured on the form. Health Plan Type is just a basic entity with a 1:N relationship to Health Insurance Plan. When I add a Lookup field for each entity onto the Opportunity form, I can set the Health Insurance Plan lookup to be a filtered lookup. So, in the field options I can set the lookup for Health Insurance Plan to only display records that have a matching Health Plan Type as the Health Plan Type field selection on the Opportunity.

FilteredLookup

The down side to this approach is that both fields must be of a type lookup, so you have to create entities instead of picklists. There are many situations where users would prefer picklists over entity lookups and for data that is fairly static, entities might be overkill.

For those scenarios where you don’t want the overhead of creating custom entities, you can leverage Javascript to accomplish the same goal of dynamic cascading picklists. Here’s the steps on how to do this:

  • Create your picklists fields in CRM and note the options you have added to each picklist. Add the picklists to a form. For this example, I created “sonoma_healthplantype” and “sonoma_healthinsuranceplan” and added them both to the Opportunity form.
  • Copy the following code sample into notepad (or your favorite code editing program).
    • Replace “sonoma_healthplantype” with the name of your first picklist.
    • Replace “sonoma_healthinsuranceplan” with the name of your dynamic picklist.
    • In the line “if(picklistOneSelectedValue == "HMO")” replace HMO with your first selection.
    • In the line “if ( picklistTwo.Options[i].Text=="PPO 500" || picklistTwo.Options[i].Text=="PPO 750" || picklistTwo.Options[i].Text=="POS Silver" || picklistTwo.Options[i].Text=="POS Gold")” replace each value (PPO 500, PPO750, etc.) with the values you want to remove on your first selection.
    • You will have to repeat the steps above for each value you want to filter on. The “//” lines show where each filter block begin and end. You can copy/paste to add more for more values or remove blocks if you have fewer values. Make sure you add or remove the entire code block from beginning to end.
function PicklistOneOnchange() {

    var picklistOneName = "sonoma_healthplantype"; //name of the first picklist
    var picklistTwoName = "sonoma_healthinsuranceplan";  //name of the picklist with dynamic values
	
	var picklistOne = Xrm.Page.getControl(picklistOneName);
	var picklistOneAttribute = picklistOne.getAttribute();
	
	var picklistTwo = Xrm.Page.getControl(picklistTwoName);
	var picklistTwoAttribute = picklistTwo.getAttribute();
		
   	var picklistOneSelectedOption = picklistOneAttribute.getSelectedOption();
	
	var picklistOneSelectedText = "";	
	if (picklistOneSelectedOption != null)
	{
		picklistOneSelectedText = picklistOneSelectedOption.text;
	}

	//This "if" statement stores the original values from the dynamic picklist.
	//Very important if the user hasn't made a selection on the first picklist or if the selection changes
    if (picklistTwo.flag == true) 
	{
		picklistTwo.clearOptions();
		var origOptions = picklistTwo.originalPicklistValues;
		
		for (var i = origOptions.length - 1; i >= 0; i--) 
		{ 
			if(origOptions[i].text != "")
			{
				picklistTwo.addOption(origOptions[i]);
			}
		}		
    }
    else 
	{		
        picklistTwo.originalPicklistValues = picklistTwoAttribute.getOptions();
        picklistTwo.flag = true; 
    }

	if (picklistOneSelectedText != null && picklistOneSelectedText != "") 
    {		
		var picklistTwoOptions = picklistTwoAttribute.getOptions();
        for (var i = picklistTwoOptions.length - 1; i >= 0; i--) {  
			
            if (picklistTwoOptions[i].value != null && picklistTwoOptions[i].value != "") {
				var optionText = picklistTwoOptions[i].text;
				var optionValue = picklistTwoOptions[i].value;
				
				//BEGIN: If the picklist is set to HMO
                if(picklistOneSelectedText == "HMO")
				{							
					//Remove these values
					if (optionText == "PPO 500" || optionText == "PPO 750" || optionText == "POS Silver" || optionText == "POS Gold")
					{
						picklistTwo.removeOption(optionValue);
					}

				}
				//END: HMO Selection
				
				//BEGIN: If the picklist is set to PPO
				if(picklistOneSelectedText == "PPO")
				{
					//Remove these values
					if (optionText == "HMO 5K" || optionText == "HMO 7K" || optionText == "POS Silver" || optionText == "POS Gold")
					{
						picklistTwo.removeOption(optionValue);
					}

				}			
				//END: PPO Selection
				
				//BEGIN: If the picklist is set to POS
				if(picklistOneSelectedText == "POS")
				{
					//Remove these values
					if (optionText == "HMO 5K" || optionText == "HMO 7K" || optionText == "PPO 500" || optionText == "PPO 750")
					{
						picklistTwo.removeOption(optionValue);
					}

				}		
				//END: POS Selection
            }
        }
    }
	
}


  • Once the code sample is ready, save it to a file on your computer.
  • We need to create a new Web Resource for the code. Go into the Settings section-> Customizations. If you are familiar with Solutions and wish to use a Solution other than the default Solution, feel free to open that solution. Otherwise, click on “Customize the System” to open the Default Solution.
  • You will get a popup window listing all of the components in your Solution. Click on the “Web Resources” section to list the existing Web Resources. Press the New button to create a new Web Resource.
  • You will see a popup window; give your new Web Resource a name like DynamicPicklistDemo (no spaces). For Display Name, you can call it Dynamic Picklist Demo. Under Type, select “Script (JScript)”. Click the Browse button and open the code file you saved earlier.

DynamicPicklist2

  • Click the “Save and Close” button in the Ribbon to save your new Web Resource.
  • Back on the Solution window, expand the Entities section on the left hand side, expand Opportunity, and choose Forms. Double click the Form named Information to modify it.
  • On the form design, double click the picklist that filters the dynamic picklist. On the field popup window, select the Events tab. Then expand the “Form Libraries” section. Click the Add button and choose the Web Resource you created.
  • Below, in the Event Handlers section click Add and when the Handler Properties form pops up, enter PicklistOneOnchange in the Function field and press Ok to Save.

DynamicPicklist3

  • Press Ok to save the Field Settings, at the top of the form design click “Form Properties”. Repeat the step above to insert the Web Resource. Press Ok to close the Form Properties window.
  • Press “Save and Close” to save the form changes. On the Solution window, press the “Publish All Customizations” button in the top left to publish our changes.

Now let’s see this code in action! First go ahead and open the opportunity and you’ll see that ALL of the Health Insurance Plan picklist options appear. The provided code samples are a simple example to accomplish this, feel free to tailor it to your own needs.

DynamicPicklist4

Now if you select PPO for the plan type, the insurance plan picklist values update automatically!

DynamicPicklist5

The provided code samples are just one example of how to accomplish this, but hopefully you can use this example to update the code to meet your own needs! 

Comments

  1. See the 'Create Dependent OptionSets (Picklists)' sample in the CRM SDK for a generic form scripting solution that can be configured without editing the JavaScript code. Configuration data is in an XML Web resource.

    http://msdn.microsoft.com/en-us/library/gg594433.aspx

    Posted by: Jim Daly  |  Jun 20, 2011 9:47:15 AM

  2. Great blog article!

    I am currently configuring CRM for a client that has the need for cascading drop-downs.

    I have chosen to do it thru multiple entities with the new related records filtering option in CRM 2011. The reason I chose this method was because I wanted to create entities where the power users of the system could update the drop-downs (i.e., the entities) themselves. I did not want to have the users to have to go to a system customizer who would update picklists on the table thru administration.

    A consideration for choosing the second option (i.e., jscripting off the picklists) would be if you are implementing for a system with multiple languages. It is much easier to maintain the system with picklists because you export/import translations of the picklist values. You would have to handle this thru a different method with the multiple entities option.

    Keep on blogging!

    Posted by: Ron De Giusti  |  Jun 24, 2011 2:05:30 PM

  3. Great article! It helped me a lot. Thanks.

    Posted by: Harini Srivathsan  |  Aug 15, 2011 3:30:47 PM

  4. hi,

    thanks for this article. it help me a lot. one amend i have done is that instead of hard coding descriptions into the script, i use the values of the parents to filter out the children. so for example if i have 3 items in picklist 1 - Ford, BMW, Mercedes - i give these the following values "100,000,000" , "200,000,000" and "300,000,000". then when i set up the picklist2 items i ensure that the subvalues for picklist 1 are in the range 100,000,000-199,999,999, the subvalues for picklist 1 are in the range 200,000,000-299,999,999 and so on. then in the script i just filter out the values i dont need based on the selected item.

    Posted by: tariq  |  Oct 11, 2011 4:40:30 AM

  5. I could not find any property called "originalPicklistValues" so wondering how could you get your code working for this line!

    var origOptions = picklistTwo.originalPicklistValues;

    Posted by: Pranav Shah  |  Apr 17, 2012 1:21:44 PM

  6. picklistTwo.originalPicklistValues is defined in the else condition a few lines below. Since picklistTwo.flag will not initially be true, the else condition will run and set the picklistTwo.originalPicklistValues property.

    Posted by: Sonoma Partners  |  Apr 17, 2012 2:45:55 PM

  7. The code doesn't work for me - I get the following error, please advise:

    There was an error with this fields customized event.
    Field: new_plan
    Event: On Change
    Error: 'null' is null or not an object

    new_plan is the equivalent of your healthplantype

    Posted by: Mike  |  May 17, 2012 7:44:47 AM

  8. Hello, I can't get this to run. I am getting the error "Object Expected". Any ideas?

    Posted by: Shelley  |  May 19, 2012 7:55:56 PM

  9. I got it. Missing some brackets. Thanks so much!! Great and easy to follow.

    Posted by: Shelley  |  May 19, 2012 7:59:22 PM

  10. I'm new to this but can't get this to work. I don't see a line of code that says:
    “if ( picklistTwo.Options[i].Text=="PPO 500" || picklistTwo.Options[i].Text=="PPO 750" || picklistTwo.Options[i].Text=="POS Silver" || picklistTwo.Options[i].Text=="POS Gold")”

    Is this suppose to be the same thing:
    if(optionText == "PPO 500" || optionText == "PPO 750" || optionText == "POS Silver" || optionText == "POS Gold")

    Posted by: mary  |  Sep 4, 2012 2:21:18 PM

  11. Great post. Very helpful and useful.

    I'm a bit of a newbie to this, but was wondering how you'd go about creating a third dynamic picklist that is based on the selection from the second picklist. That is, each selection filters from a major grouping to a specific item.

    Thanks much.

    Brian

    Posted by: Brian Covey  |  Sep 24, 2012 11:46:14 AM

  12. This is great solution, I used it for my ORG
    Is this supported in the dynamic CRM 2013?
    What modification required to do to be compatible for upgrade to 2013

    Posted by: Pankaj Deharia  |  Sep 20, 2013 12:24:20 AM

  13. Pankaj- Great question! Because the script in the blog uses the XRM Namespace, it is fully supported in CRM 2013. I tested it on our internal CRM 2013 test server and it works great! No modifications required.

    Posted by: Chris LaBadie - Sonoma Partners  |  Sep 23, 2013 1:20:07 PM

  14. In order to use any mobile device that is supported in Microsoft Dynamics CRM 2013 (app for ios tablet, etc.), i'm wondering that this solution works.
    have already tested this?

    thanks in advance

    Posted by: Silvia  |  Feb 21, 2014 7:54:44 AM

Post a Comment

  • *Required

Contact Us for a Quote, or Personalized Demonstrationof Microsoft Dynamics CRM for Your Business.

Contact Us