Cascaded lookup is a special kind of filtered lookup. Cascaded lookup fields are usually a requirement for many people working in enterprise environments, especially for those who regularly categorize things like “Product Line ->Product ->Orders”.
You can build single or multiple cascading parent-child relationships by setting up dependencies between columns in the current list and the target list. For example, you can implement typical cascading fields, such as “Region ->Country ->City”, in the same list or library, so that data entered in one field filters the options presented in another. This cuts down the number of lookup items as you don’t want to see every country or city on the planet each time you add a new item; you want it filtered.
Assume you want to create a list “Customers” to store your customer data. You can record their location details in cascading fields “Region ->Country ->City” as follows:
Note: This scenario can be extended to more dropdowns, for example, when using these fields in an Employee list.
Filtered Lookup
You can apply list view filters or specify a set of column filters to reduce the number of possible items for a lookup column. To speed up the efficiency to retrieve lookup items, two options are used; one is to display an empty field when no items matches the filter criteria and the second is to display only one instance of duplicated items.
Filtered by View
You can filter lookup items by view. For example, a lookup field can display different values to different employees according to view (e.g., each salesperson can see only clients or sales regions that are assigned to him/her), without having to embed an entire data set on a page.
Filtered by Column(s)
You can use a combination of filter parameters from the target list to filter items ensuring that only applicable choices are presented in the drop-down list. For example, you can combine three columns: Region, Industry and Size to filter companies.
For multiple selections, Cascaded Lookup supports not just the “Left <->right box”, but also “Table view”. Working with a large number of lookup items with “Left <-> right box”, however, might not be so convenient, that’s where the “Table view” comes in.
Splitting long list into several pages, the “Table view” gives you a good CRM-Like picture of lookup items and additional data. For example, an Order list with a Customer lookup column can include Account NO., Contact Person and Email in the table view. Besides filtering, it also allows paging (default: 15 items per page) and sorting to help you organize and quickly locate the data you want.
By creating an automatic reverse lookup column, you can build a bi-directional connection between lists, thus allowing you to view, maintain and navigate to connected items from both sides of the relationship.
This is especially useful if you have an order entry system within SharePoint. If you create a “Customer” Cascaded lookup field in the Orders list, then you will be able to connect each order to a customer. Wouldn’t it be nice if you could see all the orders from a customer on the customer’s View item form, as well as all the relevant columns such as Order Date and Amount? That’s exactly the purpose of the reverse lookup capability – to enable a two-way connection between two lists.
For example, you can create a two-way relational lookup between an Order field in the Order Center List and a Customer field in the Customer Data. You can then conveniently view a customer's order history from the Customer Data List.