Crow Canyon NITRO activated sites in:
SharePoint Online and On-Premises 2013/2016/2019/SharePoint Server Subscription Edition
NITRO Forms have several features for lookup columns:
- Cascaded lookup: Filter items in a child lookup based on selection in parent
- Searching and sorting of items
- Delay load to speed up form load
- Cross-site lookup
Also, it supports filtering of items in lookup column based on the selected view. This article describes below use cases to sort and filter values in the lookup columns:
- Filter items in lookup dropdown by specifying the required view
- Show different sets of lookup items to end users (who use NITRO Portal site) and staff users (who use the NITRO forms in the main/backend site)
- Show different items in lookup column based on logged-in user
In this article, we have taken example of ‘Issue Type’ lookup column in Tickets list.
Use Case 1: Filter items in lookup dropdown by specifying the required view
It is likely that over time certain items in lookup list get deprecated and should no longer be selected as lookup values in new items. However, they should continue to be valid for existing items. In this case, we can create a new column in lookup list to indicate if the item is active or deprecated. Based on this column a view can be created to list only the active items. This view can then be used in NITRO Forms lookup column configurations to show only active items in the dropdown of the lookup column.
To filter lookup items in forms, first we can create a view with required filter conditions and then use the view in lookup column settings as shown below:
In below use case, Issue Type lookup column will show all the items that are in ‘Active Items’ view.
Use Case 2: Show different set of lookup items to end users and staff
NITRO applications typically have two sites. Backend site for the staff users and the simplified portal site for the end users. In some cases, it is required to show a limited set of Issue Types to end users in portal site. Staff users in backend sites should see all issue types. We can configure this by creating different views for Staff and End Users in the lookup list (In this case ‘Issue Type’ list).
- Create a column ‘Type of Users’ in Issue Type list (i.e. in lookup list)
- Column Internal Name: TypeofUsers
- Column Display Name: Type of Users
- Column Type: Choice (Multi-Checkbox)
- End Users
- Create filtered views for Staff and End Users based on ‘Type of Users’ column.
Below filtered view will show all the Issue Types where ‘Type of Users’ column value is equal to ‘Staff’.
Similarly, we can create other views.
- Configure filtered views in backend site and portal site.
Navigate to NITRO forms -> Edit Issue Type lookup column settings and configure ‘Staff’ view in lookup settings as shown below:
Go to Portal site -> Portal Settings -> Portal Forms -> Edit Tickets list forms and configure ‘End Users’ view in lookup settings as shown below:
Use Case 3: Show different items in lookup column based on logged-in user
In some cases, it is required to filter items in lookup dropdown based on logged in user. This can be done using SharePoint item level permissions. SharePoint list items can be permissioned individually. Based on logged-in user, lookup drop-down will show only those items that user has access to. For more on item level permissions, please refer SharePoint documentation.