ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

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

Updated on September 24, 2020
ClarkSteveB profile image

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.

Is SharePoint an RDBMS?

Unfortunately, the answer is no. It can be made to perform some of the tricks that an actual RDBMS does, but it is not one. For example, it doesn't have a usable query engine nor a Structured Query Language (SQL).

In the UI, lists can be sorted, filtered, and grouped, but if you need more flexibility than that, then you'll need to use SharePoint Designer(SPD), or use the REST api to write queries based on the ODATA language.

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

An entity relationship diagram can visually display how lists and fields are related.
An entity relationship diagram can visually display how lists and fields are related. | Source

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

List Name (Internal)
Fieldname (Internal)
Use
hb Variable
zlkpRoles
Role (Title)
Role Name
childExpandField
 
 
 
 
People
Fullname (Title)
Email, but is used as lookup from ContactsRoles.Contact
 
....
 
 
 
People and Roles (PeopleRoles)
 
 
childList
 
Person
Lookup to People.Fullname
parentFieldInChildList
 
Role
Lookup to zlkpRoles.Role
childLookupField
 
 
 
 
ProjectRoster
Assignment
Lookup to People.Fullname
 
 
Role
Lookup to zlkpRoles.Role
 

Use this table, and the Entity Relationship Diagram (ERD), as a guideline to create the lists needed for this example.

Infrastructure: Forms

Form Name
Control Label
Use
hb Variable
Newform | Editform.aspx
Person
Drop Down List to select a Person
parentFormField
 
Role
Drop Down List to select the Role
childFormField
 
 
 
 

Form setup for the code below.

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.

Which best describes you before reading this article?:

See results

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

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://maven.io/company/pages/privacy

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
    ClickscoThis is a data management platform studying reader behavior (Privacy Policy)