Column types

Lookup column

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.
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.
  • 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) can 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
.
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.
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.
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.
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.
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.
If a user attempts to delete an item that is subject to a deletion restriction, a message appears saying that they don't have the permissions to delete the item.
If
Restrict delete
 is disabled, items in the lookup source iSheet can be deleted even if they are referenced in another iSheet.
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 record with multiple items, those edits cannot be saved until only one item is 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:
Screenshot of Organisational Structure without the Display column name prefix.
With
Display column name prefix
 enabled, the 'Manager' lookup column is identified:
Screenshot of Organisational Structure with Display column name prefix enabled.

Using a lookup column

Once a lookup column has been configured, end-users can 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.
A user adding an item to the 'Clients' iSheet would see the following:
Screenshot of the Add record screen.
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 select
Insert
.
The user will return to the iSheet item entry form to complete the rest of the columns and select
Add
 to save the entry.
To change the lookup selection, a user can click the link which currently displays one 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 select
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, a message appears stating that multiple values aren't allowed.
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:
Screenshot of the iSheet grid view.
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.
iSheet display with multiple values from the lookup source
If multiple values are allowed and selected, the iSheet grid will appear as follows:
hoScreenshot of the iSheet grid.
Multiple lookup value selections are separated by a line 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 Quebec 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 Quebec 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.

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
.
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:
Screenshot of iSheet columns.
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.

This article applies to:

  • Product: HighQ
  • Subject: iSheets, Setup

Related content