Use Hillbilly to Create a Sharepoint Cascading Dropdown From a Many-to-Many List Source

I have a computer science degree, and enjoy motorcycles, whiskey, and poker. (And not necessarily in that order.)

Can Hillbilly Cascade use a many-to-many list source?

Yes! But, by design, it can only use a one-to-many list as the data source.

With a little code modification, it can be made to use a many-to-many list source.

View Mark Rackley's "New & Improved Cascading Dropdowns for SharePoint Classic Forms" page.

_{(Disclaimer: The author is NOT affiliated, just an appreciative consumer of his wares.)}

Multilevel Cascading Dropdowns for SharePoint 2013, 2016, and O365 Classic forms

One- vs Many-to-Many list sources

What is a one-to-many list source?

Imagine if you had a products list, where each product was assigned to a single category. The creator of Hillbilly Cascade, Mark Rackley, uses an example of Cities -> Counties -> States. And although this is good enough for a quick example, we all know that almost every state as a city named Springfield, and numerous states have counties named after presidents. (Washington, Lincoln, Jefferson, etc.)

What is a many-to-many list source?

Drawing off the one-to-many example, imagine a lookup list for States, County, and City. And, in each of those list would be a single instance of each unique name. States would have each of the 52 States and how ever many territories. Counties would have one entry for every county name, but only a single entry. So, only one Washington entry, even though it's in 31 states. Same for Springfield in the City list.
When there is a need to associate a County to a State, then there would be a junction table with the ability to store the State ID and the County ID. Because Ohio has 88 counties, there would be 88 entries, in this junction table, for Ohio.

Article Objective

The objective of this article is to implement a drop down list (DDL), that is "filtered" by the values selected in a different DDL, known as the "Parent". The filtered DDL is known as the "Child".

So far, in the examples discussed in the introductions above were:

• Products in Categories
• Cities in Counties and Counties in States

Those were used to give your mind a little exercise in understand relational data models. In this article, the goal is to implement a Project Roster, meaning who are the people assigned to a Project, and what role will they play. In this day and age, teams are as lean as possible, and people can play different roles on different projects. For example, a developer should never QA their own code, so one developer may be the actual developer for one project, but may be ask to QA another developer's code on another project. Similarly, maybe a PM for one project is asked to be the Business Analyst on another project.

With that, this article will have entities (aka Lists) for:

• People - List of all People (i.e. Employees)
• Roles - List of all Roles that people can be. (QA, Dev, PM, BA, etc)
• Project - List of Projects
• People and Roles - Junction of People and the Roles they play.
• Project Roster - Assignment to a project of the people and roles.

(See the image below that displays the Entity Relationship Diagram.)

Entity Relationship Diagram

Define the family members

Objective reminder:

When creating or editing an item in the ProjectRoster list, after the user selects a Person, the Role drop down list is restricted to only the Roles that are assigned to that person. (Note: The assignment of People to Roles is stored in the PeopleRoles list.)

We are Family:

Because the Role is filtered AFTER the Person is selected, the Person is considered the “Parent” field, and the Role is considered the "Child" field. In just a one-to-many schema, it would stand to reason that the People list would be the parent, and the Roles list would be the child. But, because this is a many-to-many implementation, the PeopleRoles list is now considered the "Child list".

Infrastructure: Lists

Infrastructure: Forms

Construction

Hillbilly Cascade uses two javascript files to implement the Cascading Drop Down List functionality. The reason for this is that for every pair or set of DDLs that you wish to cascade, then you'll need a separate CEWP file for each. So, suppose you have cascading DDLs for people and roles, but had another for Project and Customers. These would be drawing from different list sources, so they would need their own.

The core functionality code file would be used in both situations, so it does not need to be duplicated.

Adding Hillbilly Code to a SharePoint Page

As with most javascript, it is easiest to save the .js file to a site assets library, and then reference through the use of a Content Editor Web Part (CEWP) via the file link. There are plenty of tutorials to cover this technique, so it will not be covered here.

Content Editor Web Part (CEWP) Code

The following code is needed for each set of Cascading Drop Downs. Refer to the infrastructure table and entity relationship diagram for clarification.

CEWP Code

		cascadeArray.push({
			LookupType: "Complex", // Simple (one to many) | Complex (many to many) 
			parentFormField: "Person", //Display name on form of field from parent list
			childFormField: "Role", //Display name, on form, of the child field
			childListURL: "", //If list is not in the current site, put it here.
			childList: "People and Roles", //List name of child list
			childLookupField: "Role", //When the "Parent" value is selected, what is the Internal field name, in Child List, used in lookup
			childExpandField: "Title", //Internal field name, in Child list the lookup field specified 
			parentFieldInChildList: "Person", //Internal field name, in Child List, of the parent field
			firstOptionText: "(Select a Person)"
		});

Code Modifications - CEWP

Line 2: LookupType - Parameter added to denote list source type.

Line 5: childListURL - Parameter added to specify list location, if not in current site

Line 8: childExpandField - Parameter added to specify the ODATA $expand

Updated Hillbilly Cascade code to handle many-to-many list sources

$.fn.HillbillyCascade= function (optionsArray)
{
    var Cascades = new Array();
    var url = '';
	
    var NewForm = getParameterByName("ID") == null; //is this a new form?
	
    $.fn.HillbillyCascade.Cascade = function(parent,cascadeIndex)
    {
        if (cascadeIndex!= null && cascadeIndex+1 > Cascades.length)
        {
            return;
        } else if(cascadeIndex== null) {
        	cascadeIndex= $(parent).attr("HillbillyCascadeIndex");
        }
        
        var params = Cascades[cascadeIndex];
        var parentID = $(parent).val();
        if (parent == null)
        {	
            parentID = $("select[Title='"+params.parentFormField+"'], select[Title='"+
                params.parentFormField+" Required Field']").val();
        }
        if (parentID == undefined)
        {
        	parentID = 0;
        }
        
        var child = $("select[Title='"+params.childFormField+"'], select[Title='"+
		    params.childFormField+" Required Field']," +
		    "select[Title='"+params.childFormField+" possible values']");
		
		//Handle a lookup list that exists in a different site
		var url = _spPageContextInfo.webAbsoluteUrl;
		if (params.childListURL.length > 0) {
			url = params.childListURL;
			};
		
        var currentVal = params.currentValue;
        Cascades[cascadeIndex].currentValue = 0;
       
		
		if (params.LookupType == "Simple") {
			// Handles a one-to-many lookup
			var call = $.ajax({
				url: url + "/_api/Web/Lists/GetByTitle('"+params.childList+
					"')/items?$select=Id,"+params.childLookupField+","+params.parentFieldInChildList+
					"/Id&$expand="+params.parentFieldInChildList+"/Id&$filter="+params.parentFieldInChildList+
					"/Id eq "+ parentID+"&$orderby=" + params.childLookupField,
				type: "GET",
				dataType: "json",
				headers: {
					Accept: "application/json;odata=verbose"
				}
			});
		}else{
			// COMPLEX LookupType	
			// Handles a many-to-many lookup
			
			url = _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/GetByTitle('"+params.childList+
					"')/items?$select=Id,"+params.childLookupField+"/Id,"+params.childLookupField+"/"+params.childExpandField+","+
					params.parentFieldInChildList+"Id&$expand="+params.childLookupField+"&$filter="+params.parentFieldInChildList+
					"/Id eq "+parentID+"&$orderby="+params.childLookupField+"/"+params.childExpandField;
			console.log("url="+url);
			
			var call = $.ajax({
				url: url,
				type: "GET",
				dataType: "json",
				headers: {
					Accept: "application/json;odata=verbose"
				}	
			});
		}; 
		
        call.done(function (data,textStatus, jqXHR){
			//Initialize the empty Child DDL        
			$(child).empty();
			if (data.d.results.length == 0){
				//Add default firstOption if no rows have been returned
				var options = "<option value='0'>"+params.firstOptionText+"</option>";
			} 
			
			for (index in data.d.results){
				//Simple is the default
				var id = data.d.results[index].Id;
				var result = data.d.results[index][params.childLookupField];
				
				//Handle many-to-many "Complex" lists
				if (params.LookupType == "Complex"){					
					id = data.d.results[index][params.childLookupField].Id;
					result = data.d.results[index][params.childLookupField][params.childExpandField];
					};
			
				options += "<option value='"+ id +"'>"+
                    result+"</option>";
            };
			
            $(child).append(options);
            if(!NewForm)$(child).val(currentVal);
            $().HillbillyCascade.Cascade(null,Number(cascadeIndex)+1);
        });
        call.fail(function (jqXHR,textStatus,errorThrown){
            alert("Error retrieving information from list: " + params.childList + jqXHR.responseText);
            $(child).append(options);
        });
    }
    
    for (index in optionsArray)
    {
        var thisCascade = optionsArray[index];
		
        if(thisCascade.parentFormField != null)
        {
            var parent = $("select[Title='"+thisCascade.parentFormField+"'], select[Title='"+
                thisCascade.parentFormField+" Required Field']");
            
            $(parent).attr("HillbillyCascadeIndex",index);
            
            $(parent).change(function(){
                $().HillbillyCascade.Cascade(this,null);        
            });            
        } 
        thisCascade.currentValue = $("select[Title='"+thisCascade.childFormField+"'], select[Title='"+
	        thisCascade.childFormField+" Required Field']," +
	        "select[Title='"+thisCascade.childFormField+" possible values']").val();
	        
        Cascades.push(thisCascade);
    }
    
    $().HillbillyCascade.Cascade(null,0);        

	function getParameterByName(key) {
			key = key.replace(/[*+?^$.\[\]{}()|\\\/]/g, "\\$&"); // escape RegEx meta chars
			var match = location.search.match(new RegExp("[?&]"+key+"=([^&]+)(&|$)"));
			return match && decodeURIComponent(match[1].replace(/\+/g, " "));
		}
}

Code Modifications - Core

Line 33-37: Handles the ability to retrieve lookup items from a different site. (For example, when there was a site column used as a lookup.)

Line 56-74: Perform an ODATA based REST call using $expand. This is needed to lookup to the lookup. (PersonRole only has IDs, thus needs to lookup to Roles to get the Role name.)

To learn more about the $Expand parameter, visit:
"Using the $expand OData Query Option in SharePoint 2013"

Summary

SharePoint deficiencies are sometimes easy to overcome, and this is just one example. Thanks to all of the pioneers that take the time to produce this functionality, and I hope this small addition is beneficial for you.

At the time of writing, the ability to cascade multiple DDLs using any combination of 1-M with M-M, or vice versa, has not been tested. Use with caution.

Thank you for reading. Leave comments below.

Other techniques for implementing cascading drop down lists in SharePoint

Hitendra Patel: "SharePoint 2013: Cascade Dropdowns using SPServices"

Gregory Zelfond: "3 alternatives to building cascading drop-downs in SharePoint"

Mikael Svenson: "A no-nonsense solution to implement cascading dropdowns using PowerApps as a custom form in SharePoint"

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2020 Steve Clark