iSheet lookup source permissions
iSheet permissions applied on the lookup source iSheet will always be respected in lookup columns in primary iSheets. If group-based permissions are applied for a regular lookup source iSheet, such as an "External Users" group can only view and edit items created by their group, that will be respected when those users browse the available items for selection in the primary iSheet lookup column. If a file or folder metadata iSheet is the lookup source iSheet, then any file or folder permissions applied will be respected. Users will never have access to an item (or a document or folder in the case of file or folder metadata iSheets) in the source iSheet via a lookup if they do not have access to the item in the lookup source.
Consider how these item-level permissions affect the following scenario. A lookup source iSheet is configured so that members of the "External Users" group can only view and edit items created by members of their own group, but not items created by members of the "Internal Users" group. A primary iSheet lookup column references this lookup source iSheet and only a single value is permitted (Allow multiple values is not enabled). Members of both groups can add and edit all items in the primary iSheet. An Internal user adds an item in the primary iSheet and uses the lookup column to reference an item in the source iSheet created by another Internal user. If an External user views that item in the main iSheet, the value in the lookup column will appear blank in the grid view, as well as in view item windows.
A grid view with blank values due to permission restrictions:
A view item window with blank values due to permission restrictions:
If the External user edits the item, the lookup column value will display the number of items selected, in this case, 1 selected. If the External user clicks 1 selected, the lookup source browse window will open, displaying only those values the External user has access to, and not the item selected by the Internal user for which access is restricted.
In this example, where multiple selections are not allowed, selecting a value and clicking insert will present an error message: "Multiple values are not allowed within this column". The External user could remove the existing item by clicking the red x icon to remove the selection first, and then proceed to insert a new lookup value. If multiple selections were allowed, the External user could select and insert additional values. The External user can also remove multiple previously selected values for which access is restricted by clicking the red x icon.
If an External user edits an item with lookup values selected that include both values the External user does and does not have access to, the External user may remove all values (both restricted and not restricted) at once clicking the red x icon, or click the link to modify the values selected which are not restricted from the lookup browse window.
Restrictions
When a lookup column is created and references a lookup source iSheet, certain restrictions are imposed on the source iSheet as well as on the column(s) used in the lookup and the lookup view of the lookup source iSheet.
Restrictions on lookup source iSheets
As long as an iSheet is used as a lookup (is referenced) by another sheet, the following restrictions apply:
- The iSheet cannot be deleted.
- The Allow lookups setting cannot be disabled.
- The status of the iSheet may not be changed to Pending.
It is possible to disable the entire iSheets module on the site where the source iSheet exists, as well as to change the status of the site where the source iSheet exists to archived or preparation. Doing so, however, will prevent the values in the source iSheet from being viewed in the lookup column for end users.
Restrictions on lookup source columns
The following restrictions apply to columns in the lookup source iSheet that are included as lookup columns in a primary iSheet:
- These columns cannot be deleted from the lookup source iSheet. Such columns must first be removed from inclusion in the primary iSheet lookup column before they can be deleted.
- Permissions on these columns cannot be modified and must inherit the permissions of the lookup source iSheet as a whole.
Columns that have unique permissions will not appear in the list of columns available to be used in the lookup.
Restrictions on the lookup view
The view of the lookup source iSheet that is designated as the lookup view is subject to the following restrictions:
- The lookup view cannot be deleted from the lookup source iSheet.
- The permissions on the lookup view cannot be modified and must inherit the permissions of the lookup source iSheet as a whole.
A different view can be designated as the lookup view, and then the previously designated lookup view can be deleted. If a different view is selected, all existing items selected in the lookup column will still appear in the main iSheet, even if those items do not match the filter of the new view. For example, if the previously designated lookup view filtered for US clients, and the newly designated lookup view is filtered for UK clients, any US clients previously selected in the lookup column will still appear. Similarly, if the value of an item in the source iSheet is modified so that it no longer fits within the filter of the lookup view, it will still appear in the lookup column. In short, the item selected from the lookup source iSheet need only satisfy the filter in the lookup view at the time that its value is selected and populated into the lookup column, not later.
Restriction messages
As lookup source iSheets are subject to the restrictions outlined above, the following error messages will appear when restricted actions are attempted.
- If a Site Admin attempts to delete a lookup source iSheet:
- If a Site Admin attempts to disable Allow lookups on a lookup source iSheet:
- If a Site Admin attempts to change the status of a lookup source iSheet to Pending:
- If a Site Admin attempts to delete a column referenced via a lookup in another iSheet:
- If a Site Admin attempts to delete a view designated as the lookup view in another iSheet:
- If a Site Admin attempts to modify the permissions of a column referenced in another iSheet:
- If a Site Admin attempts to modify the permissions of a view designated as the lookup view in another iSheet:
- If a user attempts to delete an item referenced in another iSheet via a lookup:
The error messages do not reveal the source of the restriction, as the Site Admin or user may not have access to the primary iSheet where the lookup column exists. Site Admins typically have access to the primary iSheet if it is on the same site as the lookup source iSheet, but may not have access to the primary iSheet if it is located on another site.
In order to determine the primary iSheet lookup column that is the source of a restriction, the Site Admin would need to contact a System Admin to investigate. The iSheet references tool, located in System admin > iSheet Admin can be used to determine the iSheet source of the restriction. See the iSheet references article for more information.
Configuration changes to existing lookup columns
Once an iSheet lookup column is saved, all of its column configurations may be modified, such as the designated lookup view or the columns to be included, except for the lookup source iSheet, which cannot be changed after the initial column creation and save.
Limitations
Note the following limitations when considering the use of a lookup column:
- Lookup columns cannot be used as the basis for column conditions
- Lookup columns cannot be used to trigger email alerts.
- Lookup columns cannot be mandatory.
- Lookup column values cannot be imported via the excel bulk import process.
- It is not possible to rename lookup columns in the primary iSheet. They will either display as the lookup source column name, or, if display column name prefix is enabled, will appear as follows: [primary iSheet column name]:[lookup source column name].
- It is not possible to create a join on a lookup column. Therefore, it is not possible to provide a join link from lookup source iSheet, such as a matter list, to the primary iSheet, a matter invoice list, to view all invoice items where that matter item was selected.
- Only the following column types may be brought into a primary iSheet via lookup columns:
- single line text
- multiple line text
- choice
- number
- date and time
- calculation
- auto increment
- Several column types cannot be brought into a primary iSheet via lookup columns:
- user lookup
- hyperlink
- image
- attachment
- file link and folder link
- iSheet link
- lookup
- join
