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.
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
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".
|List Name (Internal)||Fieldname (Internal)||Use||hb Variable|
Email, but is used as lookup from ContactsRoles.Contact
People and Roles (PeopleRoles)
Lookup to People.Fullname
Lookup to zlkpRoles.Role
Lookup to People.Fullname
Lookup to zlkpRoles.Role
|Form Name||Control Label||Use||hb Variable|
Newform | Editform.aspx
Drop Down List to select a Person
Drop Down List to select the Role
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
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.
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
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"
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"
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