Linking to Multiple Records
This topic discusses the best kind of field to use when creating a link to multiple records in another table, a one-to-many or many-to-many relationship, based on the particular circumstances and uses of the field.
This design decision applies to active links, where the items from the other table are manually selected by a user or by a rule. This is distinct from passive links, resulting from use of the related table or embedded search result data type, in which records are automatically displayed in a table within another record. Such passive links will not be discussed here.
Linking to Multiple Records
There are often situations in which you want to link to multiple records from another table. The records may just be displayed for informational purposes, but more often, establishing a link to those records is critical for automation.
You can create this kind of relationship in a few different ways, but there are two primary data types used to establish an active link: the Link to single field from other table or the Link to selected fields from other table.
Both data types can create either a link to a single record or a link to multiple records, but this article focuses only on links to multiple records, with MVE (Multiple Values Enabled) fields linked to multiple records.
Typical Use Cases
When might you need to link to multiple records in another table? Here are some typical examples that require a linked relationship:
- Identifying people to CC about a Support Case;
- Choosing Commodity Codes associated with a Vendor or a Contract;
- Selecting company Locations covered by a Contract;
- Choosing linked attachment records to send with an email;
- Selecting Products Purchased by a customer;
- Identifying the Assets covered by a support contract.
Enabling Multiple Values (MVE)
When creating a field of either data type, multiple values are enabled by a setting on the Mapping tab of the field wizard. Select the option Yes, fast search or Yes, save space. It is generally recommended to select Yes, fast search, which stores the data in the current table so it can be searched.
Note: This option can't be changed for an existing field after it has been set.
Properties of a Link to Single Field with MVE
The Link to single field with MVE displays a single field from the source table with an input tool for the user to select the records to be linked. Display options include checkboxes, drop-down lists, and two kinds of multi-value lookup:
If the list of valid items is relatively short, then checkboxes or multi-value with search pop-up are the most common display types. Here is the multi-value with pop-up selection:
Here is a link to single field displayed with checkboxes:
If there are a very large number of potential items to select, then the fourth option, Multi-value with search popup, may be the best. In this case, users click the magnifying glass icon to open the search pop-up, then select from the available records by searching. The view applied in the pop-up is chosen on the Display tab during field creation:
Benefits of a Link to single field with MVE
- A link to single field can be pulled in other tables as part of a linked set; values can easily be used in a filter or for automation in another table.
- The four display options provide flexibility for different contexts, and the pop-up search option shows more information (more fields) to help users select the right values. The display options for this data type are user-friendly.
- A link to single field can be updated by an update field action using a formula to append, remove, or replace specific values.
- A link to single field takes up less space in the record and table.
Drawbacks of a Link to single field with MVE
- Sometimes showing a single field from the source record does not provide enough information.
- Searches in the containing table can only find data in the single field's content.
- The field must generally be unique to provide meaningful content. For instance, if you use the Full Name field in a multi-record link, but several employees have the same Full Name, you will not know which person has been selected.
Properties of a Link to Selected Fields with MVE
The link to selected fields data type is most commonly used to show multiple fields from a single record in the source table. When multiple values are enabled, the linked fields are displayed as an embedded table showing the items linked to the current record.
Users must use the magnifying glass icon on the action bar to look up and find records to link to.
Because several fields are displayed at once, the link to selected fields with MVE data type shows more information about the related records than its single field counterpart.
Note that the fields shown in the embedded table's view are independent of the fields that were selected for inclusion while creating the linked field set. The fields selected during field creation define which fields from the linked records can be displayed as columns in the main table view, not the embedded table's view, or individually searched.
Benefits of a Link to selected fields with MVE
- Any field chosen when setting up the linked relationship can be searched from within the host table.
- Displaying more fields makes more information visible to users.
- Any field included in the linked set can be used in import or export actions.
- Any field included in the linked set can be shown in the host table view.
Drawbacks of a Link to selected fields with MVE
- A link to selected fields with MVE cannot be updated using a formula. Instead, you must use a more complex saved search update action in automation.
- A link to selected fields with MVE cannot be used in another table as part of a linked set, making its values impossible to use as a filter in another table.
- The embedded table display of the link to selected fields looks exactly like a related table, but there are subtle usability differences. When users link to records in a related table, the values are populated and saved automatically; the user doesn't have to save the containing record. However, the containing record must be saved to populate the embedded table in a link to selected fields with MVE. This can make this data type less user-friendly.
To determine which data type is best for your implementation, think about the detailed customer requirements.
In general, the Link to single field with MVE is an easier data type to work with. It has several usability advantages such as additional linking and automation options, a simpler display and user interface, and smaller size requirements in the table. All else being equal, a Link to single field is the strongly preferred option.
However, there are definitely cases where a single field to represent the linked records is just not enough, and a link to selected fields with MVE is necessary. This set of questions guides you toward choosing the best data type to implement.
Does a single field in the source table uniquely identify the linked records you want?
If so, a Link to single field with MVE is preferred. If not, can you create a unique single field that is a compound of two or more fields that provides enough information?
Remember that you can use a display for the single field that gives you a pop-up search window:
The view in the search results window can display more fields, so the question is not whether you can find the right record by looking at one field, but whether the single field shown in the record, once selected, is sufficient for its purposes.
If you need to see more information than can be shown in one, even compound, field in the record, then you need to use a Link to selected fields with MVE.
Do you need to use the linked values in another table as a filter?
For instance, suppose you want to filter the products a customer can select in a Support Case to the ones their company has purchased. The Company table has a link to Products Purchased, and you want to pull in the Products Purchased field with the company or user record to use it as a filter. A Link to single field with MVE handles this easily. If you must use a Link to selected fields for other reasons but also need to use the field as a filter, you will need to use a workaround - see When One Field Isn't Enough below.
Do you need to automatically update the linked set with rules or update actions?
A Link to single field with MVE can be updated by a formula. A new value can be appended, removed, or replaced in the field using a formula. A formula can't be used to update a Link to selected fields with MVE. To accomplish something similar, you can use an update action based on a saved search to apply a new set of values. This can be trickier to use and keep in sync.
Do you want to show information from this relationship in the table view?
Both data types can be included in a table view. With a link to selected fields, any field included in the set can be shown in a column of the table view.
For a link to single field, only that field can be included in a view. If you need to see more information than the single field can provide, think about using a link to selected fields with MVE instead.
Do you need to import, export, or sync the values in the linked field set?
It is possible to import/export with either field type, but remember that for a link to single field, only the field selected can be included in an import, export, or sync action. If you need to sync data held in a different field, you must use the link to selected fields with MVE instead.
Note: When importing, you cannot append values into an MVE field, you can only update/replace the contents of the entire field.
Do you need to save space in the table that will hold the linked field?
Although rare, some tables with a large number of fields can run into database space limitations. In that case, a Link to single field takes less space; a link to selected fields with the Yes, save space option selected on the Mapping tab can also cut down on field size.
Below are example implementations of each data type. These will hopefully give you a sense of their best uses and important differences.
One or the other data type will work for 95% of cases. However, there are occasionally situations in which you need to display multiple linked fields, but you also need to use the linked field as a filter in another table. In this edge case, we resort to a third option: one field of each type kept in sync with each other. This option is briefly covered in the When One Field Isn't Enough section.
Link to Single Field with MVE Examples
Below are some cases where the Link to Single Field with multiple linked record type was used.
A CCs field can be implemented with a link to single field with MVE based on the Full Name field, assuming that your Full Name field is likely to hold unique values. The selection list can be filtered to employees or specific teams within the company, and it is easy to select recipients and to use the field as the "to" value in an email template.
Groups and Teams in a User Record
Similarly, the Groups and Teams fields in the People table use a link to single field with MVE. The display option used is multiple value box with pop-up selection list.
Vendor Commodity Codes
In a contract management implementation, vendors and contracts might be associated with multiple Commodity Codes, listing the kinds of items or services provided by the vendor company. In one case, users wanted to see both the Commodity Title and the Commodity Code when selecting records to link. We created a compound field in the Commodity Codes table called Commodity Code and Title, to provide a link to single field with MVE and lookup. Using the pop-up search window, users can find the right records and can see both the code and the title once selected:
Locations Covered by a Contract
In the Contracts table, the Covered Locations field is a link to single field with MVE to the Locations table. Using the magnifying glass icon, users select from a filtered list of locations related to the company party to this contract. Note that while the Locations table might have thousands of addresses in it, since we are filtering to only those matching the company party, the list is typically short enough to select from the simple pop-up
Sending Contract Files with an Email
Also in the Contracts table, the Files to Send with Email field lets users choose from active linked Attachment records using the Attached File field to include with an email. Only active attachments are shown by filtering based on the attachment's Status. This field uses the multiple value drop-down list display option.
Link to Selected Fields with MVE Examples
Below are some cases where the Link to Selected Fields with multiple linked record type was used.
Print Templates and Associated Contract Types
The Print Templates table holds the template files used to generate contract documents. On the Template Usage tab, users can find and link to existing contract types under the Available for Contract Types heading. This field is a link to selected fields with MVE to the Contract Types table. By creating a link to a specific contract type, the print template shows up on the Print Templates and Workflows tab of any linked contract type records.
We used a link to selected fields with MVE in this situation because, in a complex contract implementation, different departments might have their own filtered sets of contract types with the same basic names but different print templates and content. The Contract Type field alone did not provide enough information to select the correct records to link to the print templates.
The Available for Contract Types linked field is used to filter which print templates are available for a particular contract in the Contracts table. On the Attachments tab of the contract record, when the Document Source is Standard or Modified Template, the Print Template to Generate field lists the available print templates that can be used to create standard contracts. This field is filtered to only those print templates that are linked to the Contract Type selected for this contract.
Linking Opportunities to People
The Opportunities table keeps track of potential sales at your organization. Opportunity records hold information about the main contact, and additional contacts are held in a related table on the Related Records tab.
External user records contain the link to selected fields with MVE to the Opportunities table. Here, sales staff can look up and link to one or more opportunities. Because it is a linked field, we can filter the available opportunities to those matching the user's company.
Using the link to selected fields data type also allows the salesperson to see the Status or Estimated Close Date, or even the expected value of the potential opportunities.
Removing Area Access Granted to Users
This example comes from a knowledgebase with a custom application to grant employees access to different site areas such as security panels, control rooms, and system relay rooms. The Area Accesses table holds a record for every employee/area access established in the system.
Employee access is managed with the Access Requests table, where staff can either request access to a particular site, or managers can remove access from employees who have been transferred or terminated. The request is then approved by another department.
To request site access removal, the manager creates a new Access Request and selects the employee's name and the type of request - Removal. With the type set to Removal, the field labeled Area(s) to Remove Access appears. This field is a link to selected fields with MVE to the Area Accesses table.
Staff then use the lookup icon to see a list of area accesses the employee currently has; the linked field is filtered to show only areas granted to this employee and is used to find and select the area access records to be removed. The manager selects site areas in the pop-up window to populate the linked field set.
When One Field isn't Enough
The following are very basic instructions for using the workaround to keep a link to single field and link to selected fields in sync with each other. Feel free to contact our professional services team for assistance with this kind of setup.
When setting this up:
- Create the link to single field with MVE using a field that uniquely identifies the linked records.
- Create the link to selected fields with MVE to include the informational fields that users need, and include the unique field used in the link to single field.
- Then, create an update fields action that sets the single field linked set using a formula and the field variable for the corresponding field in the selected fields linked set. Keep the values in sync by setting up the action to run every time the record is updated.
The result is two fields in the table holding the same information, and they are kept in sync with each other by the rule. The single field is hidden from users—it is typically used to run automation and filters as needed in other tables.
- An ID field cannot be used in the link to single field, because the linked field search does not allow the operator "is contained in" for the ID field in the other table. So, you need a different unique field to use.
- "Is contained in" is not a precise search – it will find 210 within 2100 or test within testing, so you must choose a field to use that will not find incorrect values.
- Updates to the link to selected fields set that happen silently, i.e. without the Date Updated field being updated in the record, will not trigger an update to the link to single field, so you may need to ensure that imports, syncs, or other rule updates cause an update to both fields.
- Linking to Multiple Records
- Key Questions
- Sample Scenarios