This article discusses the best kind of field to use when creating a link to multiple records in another table, thereby creating a one-to-many or many-to-many relationship. This design decision applies to active links, where the items from the other table are manually selected by a user or a rule. This is distinct from passive links that result from using the Related Table or Embedded Search Result data types, both of which display records automatically in another table. Such passive links are not discussed here.

Linking to multiple records in another table is very common. In some cases the records might be displayed only for informational purposes, but more often the linked relationship serves a critical role for automation.

You can create these linked relationships to multiple records in a few different ways, but two primary data types are used to establish active links: Link to Single Field from Other Table and 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. However, this article focuses only on links to multiple records, meaning that the linked field must have multiple values enabled.

For more information on how to work with the relationships discussed in this article, see Creating Many to Many Relationships.

Typical Use Cases

Consider these typical examples of linked relationships that link to multiple records:

• Identifying people to CC about a support case
• Choosing commodity codes associated with a supplier 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

In all of these cases—whether you're selecting people, company locations, assets, or something else—the values you select in the record are actually stored in another table. That's what makes them linked relationships.

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. Here, you have two options for enabling multiple values: Yes, fast search or Yes, save space. It's usually better to choose Yes, fast search because it stores the data in the current table so that it can be searched and included in table views.

The Yes, save space option is only used when you want to display many fields from the source records but don't need to search on them, or when you're running out of space in the host table. It only links to the data in the source table and doesn't actually copy it to the host table, which can help reduce the size of records.

Enable multiple values on the Mapping tab of the field wizard

Terminology

The following terms are used in this article:

Properties of a Link to a Single Field

If you're using a Link to Single Field from Other Table data type with MVE (hereafter Link to Single Field with MVE), you need to consider a handful of factors to determine if it's the right choice.

Display Type

One of the factors to consider is display type. A Link to Single Field supports four display options. You can choose checkboxes, a drop-down list, and two kinds of multi-value lookup:

If the number of potential linked records is relatively short, then checkboxes, a drop-down list, or multi-value with pop-up selection are the most common display types.

If the number of potential linked records is relatively large, then multi-value with search pop-up is usually the better choice. In this case, users click the magnifying glass icon to open the search pop-up and then select from the available records by searching. The view applied in the pop-up is chosen on the Display tab of the Field wizard when creating the field.

Advantages and Disadvantages

A Link to Single Field with MVE also has a variety of advantages and disadvantages that you need to consider when deciding whether to use this data type.

Advantages

• It can be pulled into other tables as part of a linked set.
• The four display options are user-friendly and provide flexibility for different contexts.
• It can be updated by an Update Fields action, using a formula to append, remove, or replace specific values.
• It uses a limited amount of space in the record and table.

Disadvantages

• Showing a single field from the source record might not provide the user with enough information.
• Searches in the containing table can only find data in the single field's content.
• The chosen field must be unique enough to provide meaningful content. For instance, the First Name field is often a poor choice because several employees might have the same first name.
• If you later need to link to another field in the target table, you need to create a new linked field rather than adding to this one.

Properties of a Link to Selected Fields

If you're using a Link to Selected Fields from Other Table data type with MVE (hereafter Link to Selected Fields with MVE), the linked fields are displayed as an embedded table that shows the items linked to the current record. Users can then click the magnifying glass on the action bar to look up and link records. 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. 

The fields shown in the embedded table's view are independent of the fields that were selected for inclusion when creating the linked field set. The selected fields determine what information is available for searches and table views in the host table. Independent of those fields, you choose a view for the embedded table on the Display tab of the Linked Field wizard.

Advantages and Disadvantages

A Link to Selected Fields with MVE has several advantages and disadvantages that you need to keep in mind when considering this data type.

Advantages of a Link to Selected Fields with MVE

• Any field included in the linked set 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 table view of the host table.

Disadvantages of a Link to Selected Fields with MVE

• It can't be updated using a formula. Instead, you must use a more complex saved search with an Update Fields action when using automation.
• It can't be used in another table as part of a linked set.
• The containing record must be saved to save changes made to the linked records displayed in the embedded table.

Key Questions

To decide which data type will work best for an implementation, let's examine a few key questions that help make the choice easier. These questions help you consider the advantages and disadvantages discussed above.

In general, a Link to Single Field with MVE is an easier data type to use and the preferred option. 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. However, when a single field is not enough to represent the linked records, a Link to Selected Fields with MVE is the necessary choice.

1. Does a single field in the source table uniquely identify the linked records I want? If so, a Link to Single Field with MVE is preferred. If not, consider creating a Compound field of two or more fields and then linking to the Compound field. For example, Full Name is a compound of First Name and Last Name. If you need to see more information than can be shown in one field in the record, even if it's a Compound field, then you need to use a Link to Selected Fields with MVE.
   It can be tempting to use a multi-value box with search pop-up to address the uniqueness requirement issue , since the search results window shows additional fields that make it easier to select the correct linked record. However, once that record is selected, only the single field being linked to is shown in the record. Make sure the data shown in the actual record is sufficiently unique and clear without the additional context of the search results window.
   

   Example

   if you're using the Asset Type field, you might be able to find the correct asset through the search pop-up, but the Asset Type field alone is not unique enough for a user to identify a specific asset when looking at the record. For instance, the Asset Type field might say Software, but this is meaningless to a user looking at the record because they're probably interested in knowing the software's name, version, and other relevant information.

2. Do I need pull the linked values into another table to use them as a filter? Suppose you want to filter the assets a customer can select in a support case to the ones their company has purchased. If the Company table has a Link to Single Field with MVE that links to the Assets table, this is handled easily. You can pull in this Linked Assets field to the support case as part of the company linked set and use it as a filter. When you create another linked field from the Support Cases table to the Assets table, simply use the Linked Assets field to filter the values by creating a search on the Options tab of the Linked Field wizard.
   If you must use a Link to Selected Fields with MVE for other reasons but also need to filter the records, you need to use a workaround that involves using both data types. For more information, see  When One Field Isn't Enough .
3. Do I 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, which can append, remove, or replace a value in the field. A Link to Selected Fields with MVE cannot be updated by a formula. To accomplish something similar, you would need to use an Update Fields action based on a saved search to apply a new set of values. This can be trickier to use and keep in sync.
4. Do I want to show information from this relationship in the table view ?  Both data types can be included in the table view of the host table. When using a Link to Selected Fields with MVE, any field included in the set can be shown in a column of the table view. When using a Link to Single Field with MVE, only that field can be included in the table view.
   If you need to see more information in the table view than a single field can provide, consider using a Link to Selected Fields with MVE.
5. Do I need to import , export , or sync the values in the linked field set? It's possible to import, export, and sync with either data type, but for a Link to Single Field with MVE, only the chosen field can be included in an Import, Export, or Sync action. If you need to sync data held in a different field, you must use a Link to Selected Fields with MVE instead. Keep in mind that when importing, you cannot append values into an MVE field; you can only update the contents of the entire field.
6. Do I 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 with MVE takes up less space. If you need a Link to Selected Fields with MVE, the Yes, save space option on the Mapping tab of the Linked Field wizard can also cut down on field size.

When One Field Isn't Enough

If you need to use a Link to Selected Fields with MVE and also need to use the linked values in another table as a filter, you need to use a workaround. The following are very basic instructions for using the workaround to keep a Link to Single Field with MVE and Link to Selected Fields with MVE in sync with each other:

1. Create the Link to Single Field with MVE using a field that uniquely identifies the linked records.
2. Create the Link to Selected Fields with MVE that includes the informational fields that users need, and include the unique field used in the Link to Single Field.
3. Then, create an Update Fields action that sets the Link to Single Field linked field using a formula with the field variable for the corresponding field in the Link to 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, kept in sync with each other by the rule. The Link to Single Field is hidden from users and typically used for automation and filters in other tables.

Sample Scenarios

Below are example implementations of each data type to give you a sense of their best uses and important differences.

Link to Single Field with MVE Examples

Take a look at the following examples to get a better idea of how a Link to Single Field with MVE is used.

CCing Individual Users

Fields that CC individual users are often linked to the Full Name field in the People table, assuming that the Full Name field is likely to hold unique values. The selection list can be filtered to employees or specific teams within the company. It's also easy to use a similar configuration to select recipients and use the field as the "to" value in an email template.

Assigning a User to a Group or Team

The Group and Teams fields in the People table use a Link to Single Field with MVE. They use a multi-value box with pop-up selection for their display option.

Selecting Supplier Commodity Codes

Suppliers and contracts may be associated with multiple commodity codes, and each commodity code can refer to items or services provided by the supplier company. To show both the commodity title and code in a record, a Link to Single Field with MVE can link to a Commodity Code and Title compound field in a Commodity Codes table.

Using the pop-up search window, users can find the correct records and also see additional information about the commodity, such as its status. After making their selection, users can see both the commodity code and title in the display box.

Choosing 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 company locations to which the contract applies. Although the Locations table might have thousands of addresses in it, we are filtering to only internal companies, so the list is typically short enough to select from the simple pop-up.

Attaching Contract Files to Send with an Email

Also in the Contracts table, the Files to Send with Email field lets users choose from linked Attachment records 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

To get a better understanding of how a Link to Selected Fields with MVE is used, examine the examples below.

Associating a Print Template with a Contract Type

The Print Templates table holds template files for generating contract documents. On the Clauses and Contract Types tab, users can link to existing contract types under the Available for Contract Types heading. This is a Link to Selected Fields with MVE to the Contract Types table. This link is used to create a multi-to-multi relationship between contract types and print templates. In a Contract Type record, a Related Table displays any print templates that have been linked to it. See Creating Many to Many Relationships for more details on configuring such a relationship.

A Link to Selected Fields with MVE works best in this situation because the contract type name might not be unique. For instance, departments may have their own lists of contract types, each with the same basic names but different print templates and content. The Contract Type field alone does not provide enough information to select the correct records to link to the print templates.

The Available for Contract Types linked field is then used to filter which print templates are available for a given contract. The Print Template to Generate field in a Contract record lists the available print templates for creating standard contracts. This field is filtered to only those print templates that are linked to the Contract Type selected for this contract.

Linking People to Opportunity Records

The Opportunities table keeps track of potential sales at an organization. Opportunity records hold information about the value of the opportunity, probability of sale, company, main contact, and other details.

On the Related Records tab, a Related Table displays additional contacts related to the opportunity. This table is populated when users are linked to the opportunity from the Link to Selected Fields with MVE in the People table.

The Link to Selected Fields with MVE is located in the External Users subtable of the People table. Here, sales staff can look up people and link them to one or more opportunities. Because it is a linked field, available opportunities can be filtered to those matching the user's company.

Using a Link to Selected Fields with MVE also allows the salesperson to view other details related to the opportunity, either in the embedded table or the table view.

Managing Area Access for Employees

In another application, managers grant employees access to different site areas, such as security panels, control rooms, and system relay rooms. The Area Access table holds a record for every employee that has been given some area access. Employee access is managed with the Access Requests table, where staff can either request access to a site, or managers can choose to remove access from employees. 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, which in this case is 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 Access table.

The linked field is not populated until a user finds and selects records

Managers use the lookup icon to see a list of the employee's area accesses, which is filtered to show only areas granted to the current employee. The manager selects site areas in the pop-up window to populate the linked field set.

In this case, a Link to Selected Fields with MVE works much better than a Link to Single Field with MVE because the manager and approver likely want to know more information about the area accesses than just their name.