Lookup columns allow one or more items from one iSheet to be associated with an item in another iSheet.
For example, a site admin could create an iSheet that contains a list of the firm's clients (the "lookup source iSheet"), and another iSheet to track firm events (the "primary iSheet"). In the primary iSheet, a site admin creates a lookup column where users can select one or more clients from the lookup source iSheet that have been invited to the event. Another example would be to create a list of countries and related information in the lookup source iSheet, then reference that country data in one or more other primary iSheets. The country information would be viewable directly in those other primary iSheets.
Many primary iSheets can reference columns from the same lookup source iSheet.
Lookup columns function in a similar way to choice columns, allowing users to select one or more items from a defined list, but with more complexity than a choice column, in that multiple columns of values can be brought in from one iSheet into another. For this reason, lookup columns may be used as an alternative to a choice column type, as they provide similar functionality.
Configuring the lookup source iSheet
Before creating a lookup column in a primary iSheet, certain configuration settings must be applied to the lookup source iSheet that determine how the iSheet can be used as a lookup source for other iSheets.
The following settings must be configured as described to enable the iSheet to be used as a lookup source:
- Access type - Select Public or Private. A Public iSheet is available to be used for lookup columns in iSheets on any site. A Private iSheet is only available to be used for lookup columns in iSheets on the same site only, and not available for other sites.
System Administrators, can access and use Private iSheets iSheets on all sites.
- Allow lookups - This setting must be enabled for other iSheets to use this iSheet as the source of lookup columns. Any type of iSheet, including a file or folder metadata iSheets, can serve as a lookup source iSheet.
Another optional configuration to consider is Display in iSheet list. This setting is checked by default. However:
- If checked, the source iSheet will be listed like any other iSheet in the iSheets module, available for all users to access (if they have been given sufficient permissions).
- If unchecked, only Site Admins (and System Admins and Content Admins) will be able to access the lookup source iSheet from the iSheets module. The main purpose of this setting is to allow admins to hide iSheets that will only be used as lookup sources for general end-users.
For example, if the source iSheet is simply a list of countries, there is no reason to allow regular users to access that iSheet directly, as the list of countries is static. It only has relevance when referenced in a lookup column of another iSheet
Other than these settings a lookup source iSheet can be configured like any other iSheet, taking into consideration iSheet lookup restrictions.
Configuring a lookup column on the primary iSheet
Once a lookup source iSheet is created and configured as described above, a lookup column can be created on the primary iSheet. The following configurations must be set for lookup column types.
Column name (required)
To avoid confusion, give the Lookup column a unique name.
Sheet (Site) (required)
First, select the iSheet which will be used as the lookup source.
This drop-down menu will list every iSheet on your instance available to be used as a lookup source. This list includes all iSheets that have Allow lookups enabled, and which are either in the same site or in a different site but with an Access type set to Public.
As of January 2023, the list of iSheets includes a search box - type part of the name of an iSheet to filter the list to only show matching names.
System Admins will see all iSheets in the instance with Allow lookups enabled regardless of whether their Access type is Public or Private.
Site Admins will see all iSheets with Allow lookups enabled on the same site, as well as all iSheets on other sites that are marked Public with Allow lookups enabled.
However, Site Admins can only select and save as lookup sources iSheets that they have access to, on sites that they have access to.
The name of the site where the iSheet is located is displayed beside the iSheet name: Due Diligence (Project Fountain). In this example, 'Due Diligence' is the iSheet located within the 'Project Fountain' site.
A primary iSheet can reference itself in a lookup column. If your primary iSheet has Allow lookups enabled, it will also be listed in the drop down list.
Select columns (required)
Select one or more columns from the lookup source iSheet that you would like to appear in the primary iSheet.
Only certain column types are available to be included via a lookup column. See the iSheet lookup limitations article.
As of March 2023, the list of columns includes a search box - type part of the name of an column to filter the list to only show matching names.
Only columns that inherit permissions from the lookup source iSheet may be included in the primary iSheet via a lookup column. In other words, columns in the lookup source iSheet that have permission restrictions applied that are different from the permissions set in the lookup source iSheet as a whole will not be available for selection.
Ideally, columns that are selected to appear in the primary iSheet should be mandatory in the lookup source iSheet, though this is not a required configuration.
View (required)
Select a view to serve as the lookup view (the picklist) for users entering data into the iSheet.
As of March 2023, the list of views includes a search box - type part of the name of an view to filter the list to only show matching names.
The lookup view determines which items from the lookup source iSheet are available to be selected from the primary iSheet.
For example:
- The lookup source iSheet may contain a list of clients, some of which are no longer active, indicated by a choice column that can be marked 'Active' or 'Inactive'.
- There is a view called 'Active Clients' on the lookup source iSheet with a filter that only shows 'Active' clients.
- Another iSheet, the primary iSheet, tracks new matters for active clients and uses a lookup column to bring in key data points about the client.
- Since new matters can only be initiated by active clients, the lookup column is configured to use the 'Active Clients' view, so that users entering data are only presented with relevant active clients.
The columns selected to appear in the primary iSheet do NOT need to be included in the lookup view.
Only lookup source iSheet views that inherit permissions from the iSheet may be used. In other words, views in the lookup source iSheet that have permission restrictions applied that are different from the permissions set in the lookup source iSheet as a whole will not be available for selection.
Enforce relationship behaviour
Restrict delete is enabled by default for lookup columns. This setting prevents users from deleting an item from the lookup source iSheet if there is a reference to that item in another primary iSheet lookup column.
For example, if the lookup column includes a reference to an item in a "List of Countries" lookup source iSheet, for example, the entry for 'Canada', then the 'Canada' item cannot be deleted for as long as a lookup reference exists. If all references to 'Canada' in the primary iSheet are removed, it would be possible to delete the 'Canada' item.
Restrict delete does not prevent referenced items in the lookup source iSheet from being edited and updated.
If a user attempts to delete an item that is subject to a deletion restriction, the following message appears:
If Restrict delete is disabled, items in the lookup source iSheet can be deleted even if they are referenced in another iSheet.
Deleting items from a lookup source iSheet WILL REMOVE such values from the primary iSheet item where they were selected and referenced. For data integrity purposes, it is best practice to leave Restrict delete enabled.
Allow multiple values
Multiple values from the lookup source iSheet may be selected in the lookup column by enabling Allow multiple values.
If the setting changes from 'multiple' back to 'single' after the lookup column has been configured and saved, previously entered values will NOT be removed. But if an attempt is made to edit any of the records with multiple items, those edits cannot be saved until only one item is left selected in the lookup column.
Display column name prefix
By default, lookup columns display using the lookup source column name in table views and the add and edit form windows. However, if Display column name prefix is enabled the lookup column name in the primary iSheet appears as follows:
[primary iSheet column name]:[lookup source column name]
For example, a lookup source iSheet with a column called 'State' used as the lookup source and column for a lookup column called 'Location' in a primary iSheet would appear as follows:
Location:State
This setting is not enabled by default, but is useful for clarifying similarly named columns. For example, in an 'Organisational Structure' iSheet, we might want to list all employees and their manager using a lookup column. Without Display column name prefix enabled, the iSheet will appear as below. It is not clear which First Name and Last Name columns represent the employee or their manager:
With Display column name prefix enabled, the 'Manager' lookup column is clearly identified as such:
Using a lookup column
Once a lookup column has been configured, end-users will be able to browse and select from items in the lookup source iSheet to populate the lookup column.
In the following example, a primary iSheet called 'Clients' has a lookup column called 'Location' with the following configurations:
- The lookup source iSheet is called 'USA and CA Codes'. It contains two columns: 'Name' for the state or region name and 'Code' for a state or region code.
- The column selected from the source iSheet is 'Name'.
- The lookup view selected is 'States List', which displays the 'Name' column in alphabetical sort order.
- Restrict delete is enabled.
- Allow multiple values is not enabled, therefore only a single state may be selected from the lookup source iSheet.
- Display column name prefix is enabled.
Display in iSheet list is disabled for 'USA and CA Codes' as the list of US and Canada states and regions is fixed and does not require frequent updates. Site and Content admins can view and update the lookup source iSheet, but it will not be directly accessible via the iSheet drop down menu to end users.
A user adding an item to the 'Clients' iSheet would see the following:
The lookup column, 'Location', has a browse button which a user can click to select the appropriate value from the lookup source iSheet.
Users may use the Quick search box in the upper right corner to filter for a particular value in the lookup source iSheet. Once a selection is made by ticking the box next to the appropriate value, the user must click Insert.
The user will return to the iSheet item entry form to complete the rest of the columns and click Add to save the entry.
To change the lookup selection, a user can click the link which currently displays one selected.
If the lookup column is configured to allow multiple values, this link will display the number of items selected, i.e. 3 selected.
The lookup source iSheet will appear, and the user can untick a previous selection and tick the box for another value. Then the user can click Insert to commit the change and return to the iSheet item form to Add or Save the entry.
If a user attempts to select and insert more than one item in a lookup column not configured to allow multiple values, this message appears:
Lookup value selections may also be removed; click the red x icon:
After an item with a lookup column is saved, it appears in the iSheet grid view:
The lookup column is displayed as a link. Click on it to display the full item from the lookup source iSheet in a modal window:
iSheet display with multiple columns from a lookup source
If we change the lookup column configuration on the primary iSheet to include more columns from the lookup source iSheet (and also modify the iSheet view to include these additional columns), the iSheet grid view will appear as follows:
The lookup column value for all lookup columns will display as a link that when clicked will show the item from the lookup source in a modal window, as shown above.
In the primary iSheet, views can be configured to include any or all (or none) of the columns brought in via a lookup column.
iSheet display with multiple values from the lookup source
If multiple values are allowed and selected, the iSheet grid will appear as follows:
Multiple lookup value selections will be separated by a carriage return.
Lookup column value updates
Lookup column values will automatically reflect any changes made to such values in the lookup source iSheet. For example, if we changed the code value for the Québec item to CA-QB in the 'USA and CA Codes' iSheet, that change will be reflected in the 'Location:Code' column for all items in the "Clients" iSheet that reference the Québec item.
Exporting lookup columns
Lookup columns values may be included as part of iSheet exports by admins and end-users with iSheet view permissions to export.
Currently it is not possible to import lookup column values via the excel bulk import process. See lookup column limitations, and the article on Importing iSheet data for more information.
Sorting lookup columns in view and edit windows
You may wish to change the order in which the lookup column appears in the view and add/edit modal windows. To do so, navigate to the primary iSheet and select More actions > Manage columns > Sort columns.
If the iSheet has sections enabled, navigate to More actions > Manage columns and click the name of the section which contains the lookup to sort the columns.
The list of columns will appear and can be rearranged via drag and drop. If the iSheet includes lookup columns, at least two column listings will appear for each lookup column in the drag and drop window:
In this example, 'Location' is the lookup column configured in the primary iSheet. 'Location:Name' and 'Location:Code' are two columns brought into the primary iSheet via the lookup.
The primary iSheet column name, 'Location' in the above example, determines where the column will appear in the sort order in the add and edit item windows.
However, the lookup source column names, 'Location:Name' and 'Location:Code' in the above example, determine the order of the columns as they will appear in the view item window.
In this example, the columns are rearranged as follows:
In the add and edit item windows, the 'Location' lookup column appears beneath 'Name':
In view item windows, 'Location:Name' and 'Location:Code' appear above "Name", per the order designated above:
