Cascading dropdowns on a SharePoint form

Why the old answers aged so badly

Search this and you land in 2013. The results are jQuery snippets against the Lists web service, SPServices, a CEWP holding a script somebody's contractor wrote, or an InfoPath form with a secondary data connection and a filter on it. Every one of those was a reasonable answer at the time. None of them survives a tenant that has moved to modern pages, and the InfoPath route stopped being an option entirely when Microsoft retired it.

The other answer is to buy a special column type that does the filtering for you. That works, and it also means your form now depends on a column that only exists while the licence does. Neither is a good place to be when the requirement is this ordinary.

First, check whether you actually need a cascade

A surprising number of these requests are not really about filtering a list. They are about not showing somebody a question that does not apply to them. If the second dropdown has a handful of options per parent value, you do not need anything dynamic at all. Build one choice field per branch and let the rules engine show the right one.

So: a Category field, then a Subcategory field for hardware, another for software, and so on down the list, each shown only when its parent value is selected. Several fields where you imagined one, and no code anywhere. Add a rule that blanks a hidden field's value, so a half-finished answer does not travel with the record. The conditional logic guide covers how the show, hide and require rules are put together.

This is the right call more often than people expect. It falls over at scale, though. Forty product lines is not forty hidden dropdowns.

Filtering a lookup against the parent

The proper version of a cascade points the child field at a real SharePoint list and narrows what it offers based on what the parent field holds. Two lists, Countries and Cities, with a column on Cities saying which country each belongs to. The form has a lookup for the city, and when the country changes, the lookup is told to only offer rows that match.

On an internal list form, Forms365 Workspace exposes the form to author JavaScript through its f365 API, and a lookup field can be constrained at runtime. You hook the parent's change event, hand the lookup an OData filter, and reload it.

f365.field('Country').on('change', async () => {
  const country = f365.field('Country').value;
  const city = f365.field('City').asLookup();
  if (!city) return;

  // The old pick is probably wrong now. Clear it before refiltering.
  f365.field('City').value = null;

  if (country) {
    city.filter("Country eq '" + country + "'");
  } else {
    city.resetFilter();
  }
  await city.reload();
});

That is the whole pattern. The clearing line in the middle earns its place: without it a user picks Australia, picks Brisbane, changes their mind to New Zealand, and submits a form that says Brisbane, New Zealand. The filter narrowed the future options and left the past answer sitting there.

The filter is an OData clause against the target list, so the same technique handles more than a straight parent match. Active rows only, or rows dated this financial year. It is the same lever, pointed at a different column.

Three levels deep

Region, then country, then city is the version that catches people out, because clearing one level has to clear the levels under it. Handle the parent's change event, clear and refilter the child, and clear the grandchild as well rather than leaving it holding a value that no longer belongs to anything above it. Write it once as a small function that takes the level you changed, and call it from each field, instead of copying the same block three times with different names in it.

If you find yourself four levels down, stop and look at the data. Deep cascades are usually a sign that a hierarchy is being retyped into a form when it should be one lookup into a list that already carries the whole path.

Index the column you filter on

Cascades usually turn up attached to large reference lists, because a short list would not have needed narrowing in the first place. That is where SharePoint's own limits start to show. A list view in SharePoint Online stops at 5,000 items and the number is fixed, and Microsoft's advice for working past it is to index the column you filter on and let the query cut the list down before anything else happens.

Apply the same thinking to a cascade. The column on the child list that names its parent is the one every filtered query touches, so give it an index in list settings. A list can hold twenty of them, and this is a good use of one. Without it, a picker over a big list feels like it is thinking, and users decide the form is slow long before they decide the data is wrong.

It is also worth testing the cascade against the real list rather than the twelve rows you seeded while building it. Filtering behaves the same either way. Waiting does not.

Keeping the reference lists honest

A cascade is only as good as the lists behind it. A city row with no country against it will never appear under any parent, and the person who needs it will assume the form is broken rather than that the data is. It is worth making that column required on the reference list and checking for blanks before you go live.

The other thing worth deciding early is what happens when the value somebody needs is not there yet. A lookup can allow a quick add so the user creates the missing row from inside the form. Convenient, and it is also how a reference list ends up with Brisbane, brisbane and BNE in it. Turn it on where the list is genuinely open-ended, leave it off where the list is controlled and a missing value should be a conversation with whoever owns it.

On public web forms

If the form is a Forms365 Web form that anyone can fill in without a Microsoft account, the same instinct applies with different tools. Conditional logic decides which questions appear based on earlier answers, so the branching version of a cascade is available without anything custom. Where the child options come from a SharePoint list rather than from a fixed set, look at the lookup and choice controls that appear on SharePoint-backed forms and design the reference lists the same way you would internally.

What this buys you

Not elegance. Shorter dropdowns are the visible part, but the reason to do it is the data that comes out the other end. A user given six plausible cost centres picks the right one. A user given three hundred picks whichever looks close, and the finance team finds out six weeks later. Filtering the list is cheaper than correcting the records.