Multi-select Choice or Lookup fields used in Cascading Dropdowns

The Problem

Cascading multi-select fields. Input form has multiple selection for Types and Specialties for a contact. The desire is to have another form in another library lookup the Types and Specialties in dropdown lists, allowing one type selection and a related, filtered specialty which then filters the list of contacts meeting both the types and specialty selections. Add in a bit of a twist, if the Types match a specific criteria then the Specialties are pulled from a different multi-select column on the same source list.

Lots of potential issues here!

The Approach

Create a new list with Type and Specialty columns of type ‘single line of text’

On  the Contact list (with the two multiple select columns) add a workflow triggered onchange and on new.

Set a variable to the Types field values of type Lookup, comma delimited. Loop through the variable, looking for ‘,’ and pull the value from it to a substring variable, trim out the value and comma from the lookup variable. Enter a loop, use the substring to determine the cascade lookup field to use and set a variable to the values of the second tier lookup, pull out the value into substring2, enter a loop(2) and use substring and substring2 in a REST query to the created list – test for a match of both fields. If it exists, move to the next loop(2) value, if it does not match, add an entry to the list. This creates one entry for each type/specialty combination in the new list. At the end of the on-change workflow, set the value of another hidden text  field in the contacts list to the display name value of the current record.

On the form to consume the cascading dropdowns add three fields of type lookup – point to the new list Type field and specialty field (do not allow multiple selections!) and a third lookup to the contact list hidden text field for name.

Add a content editor web part to the edit form. Use this to link to a javascript file in site assets. Import the SPServices library and the jQuery library in the head of the linked javascriptfile. On the first field (Types) use the SPServices.SPFilterDropdown and directly after it add the following to remove duplicates from the dropdown results:

               var usedTypes = {};

               $("[title='Type'] option").each(function () {

                              if (usedTypes[this.text]) {

                                             $(this).remove();

                              } else {

                                             usedTypes[this.text] = this.text;

                              }

               });

Add a SPServices.SPCascadeDropdowns call for the Type->Specialty and another for the Specialty->Specialist cascades. Parent column and child column are from the form’s list, the relationship columns and field names from the lookup list (new list created above). In the specialist lookup use the contacts list / hidden name field as the lookup source.

The Result

List: Contact
Types (multiple select)
Specialty (Multiple Select)
Name - hidden text
- workflow that updates cascadelookups on new/change

List: cascadelookups
Type (single text)
Specialty (single text)

List: Forms
Type: Lookup to cascadelookups - cascades to:
Specialty:  Lookup to cascadelookups - cascades to:
Specialist: Lookup to Contact Name hidden text field
- javascript included to work the cascade magic.

Going Forward

The update of the lookup list is addition only – if the users remove a specialty or type from a contact and there are no longer any contacts which match them the type field and specialty fields will still show the choices. I haven’t found a graceful way of removing these entries but a brute force method to remove null entries is to delete everything in the lookup list and then go to the contacts list and bulk edit the records, deleting the hidden name field values – triggering the workflow which then adds entries for each unique type/specialty pair. I’m sure there is a better way but I’ve not needed it yet.

Hopefully this helps someone a bit in using multiple select fields in cascading lookups.