Calculated Columns for Cascading Drop-Downs in SharePoint Lists and Libraries

SharePoint list or library users need a simple way to enter data in one column that depends on what was entered in another, but out-of-the-box SharePoint 2007 or 2010 does not support direct cascading functionality.

Using jQuery for SharePoint Web Services might be an option for cascading drop-downs, but you are not allowed to use SharePoint Designer to modify the EditForm, and besides, you want the behavior to work inside the list/library itself in datasheet view and not just in the EditForm.

Lookup columns could do the trick, but let’s say you’re already at the farm-wide threshold setting of eight per list/library. Managed Metadata in the Term Store perhaps? But, the terms are not universal and getting a few simple entries requires more than a few clicks.

Opportunity
If there are only a few fields that relate to one another, one straightforward option might be to create a Choice column that allows users to select a delimited text string that contains all of the relationships in a single line, and then use Calculated Columns to parse the string out into separate columns.

Actions
For example, a Choice column called “Section – Category” could delimit a drop-down menu of sections and categories with a semi-colon character, making it pretty easy for people to spot the combo they’re looking for:

1 – Pre-Visit; 1 – Charting
1 – Pre-Visit; 2 – Phone Calls / Messaging
1 – Pre-Visit; 3 – Registration
1 – Pre-Visit; 4 – Scheduling
2 – Patient Visit; 5 – Check-in
2 – Patient Visit; 6 – Chronic Care / Education
2 – Patient Visit; 7 – MyChart
2 – Patient Visit; 8 – Orders / Charting
2 – Patient Visit; 9 – Rooming
3 – Post-Visit; 10 – Charting
3 – Post-Visit; 11 – Orders
3 – Post-Visit; 12 – Phone Calls / Messaging
4 – Bars; 13 – Between Visits
4 – Bars; 14 – Daily Work & General

One calculated column called “Section” and another called “Category” could contain formulas that split the string into left and right contents. But let’s take it up a notch and turn the string into four columns – “SectionID”, SectionTitle”, CategoryID”, and “CategoryTitle.” See where this is going?

SectionID – the formula is pretty basic, but it’s not just getting the first character from the left in the string, because the number might end up with more than one character. It’s about getting everything to the left of the first space ” ” character. Now, note this dash has spaces around it. This is important visually for users, but it also serves to make it different than any hyphens used in the words. So, after finding the ” – ” character, the space needs to be subtracted with a “-1” thusly:

=LEFT([Section - Category],FIND(" ",[Section - Category])-1)

SectionTitle – here the formula starts getting a bit more complex. We want the words to the left of the “;” and to the right of the ” – “. Note the use of “-1” here too to remove spaces around the dash, and also note that there is a space on the right of the dash in “- “:

=RIGHT(LEFT([Section - Category],FIND(";",[Section - Category])-1),LEN(LEFT([Section - Category],FIND(";",[Section - Category])-1))-FIND("- ",LEFT([Section - Category],FIND(";",[Section - Category])-1))-1)

CategoryID – same fun, but now wanting all the numbers from the string on the right of the “;” that are left of the ” – ” on the right hand string:

=LEFT(RIGHT([Section - Category],LEN([Section - Category])-FIND(";",[Section - Category])-1),FIND(" -",RIGHT([Section - Category],LEN([Section - Category])-FIND(";",[Section - Category])-1))-1)

CategoryTitle – not as simple as it looks, this task is to get all the words from the right side of the “;” and the right side of the ” – ” that’s in the right side string;

=RIGHT(RIGHT([Section - Category],LEN([Section - Category])-FIND(";",[Section - Category])),LEN(RIGHT([Section - Category],LEN([Section - Category])-FIND(";",[Section - Category])))-FIND("- ",RIGHT([Section - Category],LEN([Section - Category])-FIND(";",[Section - Category])))-1)

Results

Selecting a single item in the Choice column like “1 – Pre-Visit; 1 – Charting” yields:

<table class="wp-block-table is-style-stripes" style="font-size:0.7rem;">
  <tbody>
    <tr>
      <td><strong>S_Id</strong></td>
      <td><strong>S_Title</strong></td>
      <td><strong>C_Id</strong></td>
      <td><strong>C_Title</strong></td>
    </tr>
    <tr>
      <td>1</td>
      <td>Pre-Visit</td>
      <td>1</td>
      <td>Charting</td>
    </tr>
  </tbody>
</table>

This method of creating cascading drop-down columns can work well as long as the choices are simple – meaning limited to two to three columns. The formulas can get as complex as your brain can comprehend, and calculation speed does not seem to be an issue.