In this video, we explore how to create cascading lookups in SharePoint lists using Lightning Forms from Lightning Tools.
In the below screenshot example, we have two columns:
- Vendor Type
- Vendor
The Vendor Type is a choice column containing choices such as Computer Hardware, Computer Software, Electrical, and Stationary. The second column is a lookup column to a SharePoint List which contains a category column and a vendor name column. Ideally, when selecting a Vendor Type, we would like to pass that Vendor Type value chosen as a filter when returning vendors within that category.
Using Lightning Forms, you can pass through a filter by using an Expression, Static Value, or a Value from another field. To apply a filter to a lookup column, follow these steps:
- Click the Field Configuration Icon for the lookup column (Vendor Name) in this example shown below:
- Add a lookup filter by clicking the filter icon for the Lookup Filter ‘Enter CAML here’ property.
- Configure a filter to filter the Category in the lookup list by the Vendor Type in the Purchase Order Form.
- Click Save.
Your Vendor field will now only show the vendors for the selected category.
Enhanced Lookup
Lightning Forms Lookup properties also enable you to change to an enhanced lookup. This option can overcome a list view threshold in large lists that you may be looking up from.
Remove Duplicate Values
Checking ‘Remove Duplicate Values’ will ensure that the lookup column only shows each option once. e.g. Microsoft could appear as a vendor for Computer Hardware and Computer Software. Removing Duplicate Values would show Microsoft just once in the lookup column.