29 Jul 2019

iSheets - Join column

Product Filter HighQ Collaborate
Product Area Filter iSheets

A join column creates a link to a filtered view of another iSheet based upon one or more matching values, as configured in the join column settings.

For example,

  • An iSheet is created to track matter information and includes columns for matter name, matter number, key contacts, etc.
  • Another iSheet is created to track invoices for each matter and includes columns for matter number, invoice amount, invoice date, etc.
  • Both the matter and invoice iSheets contain a column for the matter number, a common data value which can be used to create a join from the matter information iSheet to the matter invoices iSheet.
  • The join creates an automatic link from a matter information entry to all invoice entries for that matter. The join link is dynamic.
  • As new invoice entries are added for each matter, the join link on the matter information iSheet will direct users to an updated, filtered view of the invoice entries in the invoices iSheet for that matter.
  • Legal Tracker iSheets can join auto-increment columns to enable them to automatically update Matter intake iSheets from the Matter Management iSheet, based on the matter ID.

Join columns are particularly useful when there is a one-to-many relationship in your iSheet data (i.e. one matter entry for many invoice entries). 

Supported column types

The following column types may be used for Join conditions:

Join column configurations

Join columns have the following settings which must be configured:

Link name

Enter a link name to define the static text that will be used for the join link.

'List of sheet'

Select the iSheet that will be the join 'target', i.e., the iSheet that a user will view upon clicking the join link. Only one iSheet can be selected; typically an iSheet on the same site.

System admins can access all iSheets from all sites, though it is a best practice to configure joins with iSheets on the same site. 
Site admins can access all iSheets on the current site and any other site(s) to which they have Site admin access. Site admins will also see any iSheets on sites to which they have access at any level, which have both an Access type set to Public and Allow lookups enabled.
However, if these iSheets on other sites have permission restrictions which exclude the Site admin from access, they cannot be selected by the Site admin for the join configuration.

It is possible to join an iSheet to itself, which is how this example is configured. If a join is configured to use the same iSheet, every item that matches the join condition, including the item itself, will be listed upon clicking the join link.

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.

Display view

Designate a view of the join target iSheet that should be displayed when the user clicks a join link. This view should not have any filters that might interfere with the filtering applied via the join condition (or conditions) configured below.

As of March 2023, the list of views includes a search box - type part of the name of a view to filter the list to only show matching names.

Join condition

The join condition determines which rows of the joined iSheet will be displayed when the link is clicked. Select a column from the current sheet and the target sheet which should match for the join to display the desired values.

For example, the iSheet being joined may contain a list of clients and include a column to populate the US state where the client is located. The join link can provide a filtered link to the iSheet showing only clients that are located in the same US State, based upon matching values in the US State column (which is a choice column):

As of March 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.

Multiple join conditions can be included. For example, if there were an additional column in the iSheet for the client's Industry, we could add a join condition so that both the "US State" and "Industry" must match in order for a client to appear in the filtered list upon clicking the join link.

If the join condition is configured on a choice column where multiple selections are allowed (checkboxes), the match can work against any value selected, if more than one selection has been made.

All join conditions may be modified after a join column has been saved. Therefore, it is possible to change the joined iSheet and/or join conditions after the column is created and saved.

Choose to hide or display conditional fields

You may add conditions which can either hide or display a field (the column will default to the other option).

This allows you to only display text or a link if it is required, such as providing a link to a timesheet only if a task has been marked as complete.

Join columns in iSheet table and item views

Join columns, like calculation and auto-increment columns, require no user input. Once joins are created, they automatically create links to iSheets based upon the join conditions configured. As such, join links are accessible in iSheet views and item views, but not visible when users edit items.

Join columns, if made available in a view, will appear as follows in iSheet table views:

A join link will also appear when viewing an iSheet item view window: 

If a user clicks the join link in this example, they will see a list of clients located in the same US state in the joined iSheet (which in this example happens to be the same iSheet). Below, a join link was clicked on an item that has a US State value of "California". The join view will navigate to a display of all iSheet items that have a US State value of "California" in the same browser window:

As noted above, if the join column condition is on a choice column that allows multiple values, then a match can be on any of the selected values. Consider the following values in a client list iSheet:

If we select the join link for "MNO LLC", the join display will show all items that have selected either New Jersey or New York in the US State column:

Join views are dynamic, so any matching items that are deleted from the join iSheet will not be displayed the next time the link is clicked, nor will any items that were changed in such a way that the join condition is no longer satisfied.

Permissions

If a user clicks a join link and does not have permission to view certain joined items, such items will simply not appear in the join view. If a user does not have access to the joined iSheet at all, clicking on the join link will present the user with the following error message:

If a user does not have access to the join view configured, the same message will appear.

If a user does not have access to the column or columns configured in the join condition, the user will still see joined items upon clicking the link, but will not have access to those columns in either the table or item view.

 

Was this article helpful?