This topic discusses the design factors and decision points when you want to create a many-to-many relationship between records in one table and those in another table – or even the same table. This relationship is generally created by using a linked field on one side and a related table field on the other.
The following terms are used in this article:
Linked field | Linked field is an umbrella term for several data types that create a link from one table to another, most commonly the link to selected fields or the link to single field data types. |
Embedded table | Embedded table is an umbrella term for any data type that displays a list of records in table format. These include the related table and the link to selected fields with MVE data types, as well as the embedded search result and communications search result data types. |
Active link | An active linked field means that users are expected to interact with the linked field to find, select, and link to records. |
Passive link | A passive link means that the field is primarily informational and displays records that have already been linked by the system, such as by a rule, saved search, or other automation, as opposed to by users. |
MVE | Stands for multiple values enabled. When a link to single field or link to selected fields field holds multiple records, it is referred to as a linked field with MVE. |
There are often situations in which you want to create a many-to-many relationship between two tables. This is done by pairing either a link to single field with MVE or a link to selected fields with MVE field in one table and a related table field in the other. The challenging part of the design is deciding which table holds which field type.
This article will discuss how to decide which table in the relationship should contain the active linked field. Active in this case means the field in which the user is going to actually choose the records that should be linked. In contrast, the related table is a passive data type: it is automatically populated with the records that were linked to it from the other table.
The link to selected fields with MVE data type looks exactly like a related table, since they both present an embedded table on the screen, but they behave differently. It is important to put each data type in the appropriate table to accurately meet the business requirements.
The following discussion covers the differences in behavior between the linked field, either the link to single field or link to selected fields, and the related table. In particular, we will focus on the subtleties of the link to selected fields with MVE.
Before reading this document, please review Linking to Multiple Records, which covers the differences between a link to single field with MVE and a link to selected fields with MVE. That article will help you decide whether the active linked field will be a link to single field or a link to selected fields.
Here are some typical examples of a many-to-many relationship between tables
A linked field holds a reference to data from another record so that it can be used or viewed in the host table.
Records are actively linked. In a linked field, the items - records - are usually chosen by a user in response to a business process, such as selecting the Locations to which a contract applies. In other situations, linked fields are populated automatically with a saved search default, action, or rule.
Linked source records are not modified. When you link to a record in a linked field, nothing about the source record is changed. For instance, it will not be removed from another relationship, and no fields are edited.
Changes must be saved. When a user links to a record in a linked field, it will appear in the embedded table as if it is already linked, but the system does not save relationship until the containing record is saved. This behavior matches that of other types of linked fields: after choosing items from another table, you must Save the current record to confirm the selection.
Updates by rules and actions. You can apply a default value action that will populate a linked field based on a saved search. An update fields action run by a rule - such as an "All Edit Actions" rule - can also populate or update the contents of a linked field.
Searching considerations. Each field selected in a link to selected fields data type appears in the usual field list in the search block. In the advanced search wizard, these fields are moved to the bottom of the field list. For instance, if you create a Simple filter, you will see these fields in a section below the Related tables separator:
You can search on a linked field just as you would in any standard field. However, there is currently one limitation when searching: numeric fields - integer, floating point, currency, etc. in the source table cannot be searched numerically. As a result, you cannot use a search that asks whether a numeric field is greater or less than some value.
Individual fields can be included in the table view. Individual fields included in a link to selected fields can be added to the table view. Below, Sale Values is a linked field from Opportunities shown in a table view of People:
Charts and reports. Reports can use individual fields from a linked field set as the X-axis of a chart. They can also be used to group or filter charts and reports, just like fields native to the table.
Linked fields take up space and are size-limited. Unless the Yes, save space mapping option is used, linked fields take up bytes in the table, and it is unwise to set up a situation in which a linked field holds thousands of records. This can slow down the loading of record forms and otherwise negatively affect system performance.
A related table is the second half of a many-to-many relationship. While the linked field is considered active, related tables are generally passive. They rely on the relationship established by the linked field, and you must create the linked field in another table first before setting up a related table.
Once created, related tables automatically display all records that are linked to this record. For instance, if a contract includes a link to Locations Covered, we can add a related table in the Locations table showing all contracts that are linked to a particular location record.
Related tables are one of a few special data types based on the results of a saved search. They do not take up space in the table because they simply display the results of a search: they are "virtual" fields. Related tables hold any number of records with ease because they do not store any data directly. When record size or available database space in the table is an issue, related tables are a useful tool.
Source records are modified and automatically saved. When you add or remove records from a related table, you are actually resetting the linked field in those records to add or remove the value of the record you are currently editing. If you look up and "import" a single-value linked field into a related table, you are removing any value it previously had that linked it to another record. For an MVE link, you are appending the current record's value into the linked set in the other record. In order to perform these functions, a user must also have permission to edit the underlying linked field in the other table.
For instance, suppose the Contract table has a Locations Covered linked field and a related table in the Locations table showing contracts. When you use the related table's lookup icon to find and link to a contract which is not already associated with the location, what you are really editing is the Covered Locations field in that contract by adding the current location's name. Or, when you select a record and click Unlink, the system edits the contract record and removes the current value from the Covered Locations field.
Acting on records in a related table affects the source records, not the containing record. Therefore, the linked relationship is established in the related record and it is unnecessary to save the current record to confirm the change.
Limited filtering. You can filter the records that appear when users click the magnifying glass to add links, but the filters are limited to the current table and global user variables. For example, if you have a related table in the Company table for a link to the Contract table, you can't filter the related table to show contracts whose Contract Company matches the current Company. If you need to filter based on fields in the other table, you need to use a linked field.
Searching considerations. The related table field and its underlying data cannot be included in normal, dynamic searches on the table where it resides. To search based on a related table, you must use the advanced search wizard, and you are limited to using saved searches already in the related table's source table.
For instance, suppose the People table contains a related table of Opportunities and you want to search for all people with opportunities valued at more than $100,000. To do this, first create a saved search in the Opportunities table to find "Values greater than $100,000." You will then be able to create a new saved search on the People table that uses that related table filter.
Reporting. Related tables can't be included in a report. If a related table contains data relevant for reporting, the report is typically run from the source table itself. For instance, suppose you want to show all People for each opportunity. If the Opportunities table contains a related table of People, you can run the report from the People table and group by opportunity.
Related tables cannot be shown in a table view. A related table does not have specifically defined source fields that can be added to a table view.
Records can be filtered to limit the records that appear in the table and in search results when users add new links, but related tables have limitations in how they can filter.
Think about the following questions to help determine which data type should be used in which table.
This section will review a few examples and discuss how the design factors influenced a practical decision.
In the standard knowledgebase, there is a many-to-many relationship between People and Opportunities, a table used to track potential sales opportunities at an organization. Opportunities are associated with a primary contact, but sales staff can add other relevant contacts. Of course, contacts can also be linked to multiple opportunities.
Below are the answers to the questions in the previous section for this use case:
In this case, many of the factors are neutral. The requirement to filter People by opportunity and the fact that new People are created more often led us to put the link to selected fields with MVE in the People table and a related table in Opportunities.
Below is a sample Person record showing the Opportunities link to selected fields with MVE. Note that you should usually put an input instruction above the link to selected fields to tell the user how to find and link to records.
With this structure, the available opportunities can be filtered when power users add a new person and want to link in existing opportunities. The linked set in People is filtered to a match the person's Company to the opportunity's Company.
Because the linked field is in the People table, it's also possible to construct searches based on whether a customer's linked opportunities contain certain values, such as "Status=Closed/Won" or "Status=Lost."
To complete the many-to-many relationship, the Opportunities table contains a related table called Additional Contacts.
This next example also comes from the standard knowledgebase. Some types of contract are associated with assets, such as a support contract with an external provider to maintain hardware belonging to your organization. A single contract can apply to several assets, and assets might be covered by several contracts.
Below are the answers to the questions in the previous section for this use case:
The strongest factor in this case was the automation required to link assets from a previous contract to its renewal record. This led to the decision to build a link to selected fields with MVE in the Contracts table called Assets in this Contract, and the Asset table contains a related table of All Contracts. Based on the requirement to show the most recent contract in an asset record, as well as the automation needs, we also include a single-value link to selected fields in the Asset table to hold the Title, ID and other information about the current contract.
In the Contract table, the Assets in this Contract linked field set is only visible when the Asset Involvement field has a value of "For One or More Assets". An input instruction tells users how to find assets and link them to the existing contract. The action bar above the embedded table can also be used to create a New asset and automatically link it to the contract.
Below is a sample asset record showing the related table of All Contracts and the Current Contract Information section. Power users can link to existing contracts in the All Contracts related table, if necessary, or use the New button on the action bar to create a new contract covering the asset.
In this simple situation, a contract can be linked to multiple company locations that it covers, and you want to see all contracts for a given location. In this use case:
This was an easy decision, as nearly all the factors pointed toward the same solution: a link to single field with MVE in the Contract table called Covered Locations, and a related table in the Location table of Contracts Covering this Location.
Related articles |