Page tree
Skip to end of metadata
Go to start of metadata

Representing a List of Values

This topic discusses the options for representing a list of values within a field in a record. In Agiloft, a list of values can be implemented with a choice field or with a background table whose records are displayed as a list in a linked field in the main table. Often, both options might be possible solutions for holding a set of values, and we must weigh the pros and cons of each to make the best decision.

Typical Use Cases

There are many situations where users need to choose values from a list:

  • Selecting a Contract Type within a contract record.
  • Choosing the Department for which a contract is being created.
  • Choosing an Attachment Type to specify what kind of document is attached.
  • Selecting the Company Roles for a company.
  • Choosing the Type of Problem to classify a helpdesk case.

Properties of Choice and Multi-Choice Fields

The simplest way to create a list of choices is to use a choice field.

A choice field is created by setting up a new field and choosing either the Choice or Multi-Choice data type. A multi-choice field lets users select more than one value from the choice list.

Display options. Choices can be displayed in a drop-down list or as a set of radio buttons.

Choice field displayed as a dropdown list

Multi-choice fields can be displayed as a multi-choice dropdown list, a set of checkboxes listing each option, or an input box with a lookup icon.

Multi-choice field displayed as a status box with lookup

Setting visibility dependence. The visibility of other fields can depend on the specific selection(s) in a choice field. For instance, a field such as Contract End Date can be made visible if a choice field called Renewal Type has a value of Auto-Renew or Notify staff to renew, but not if it has a value of Evergreen:

On the Options tab of the field wizard, fields can be made visibility dependent based on the selected values in a choice or multi-choice field

Conditional requirement. Other fields in the table can be conditionally required based on a value held in a choice or multi-choice field in the current table.

On the Options tab of the field wizard you can make a field conditionally required based on a choice field

Hierarchical dependence. Choice (and multi-choice) fields have a built-in hierarchical dependence wizard, which allows you to set up a parent-child relationship between two choice fields so that the choices available in the child field depend on the choice(s) selected in the parent field. For instance, when a user selects a value in the Problem Type field in a support ticket, the choices in the Problem Subtype field can be dynamically filtered to the relevant choices for that Problem Type.

The built-in hierarchical dependence wizard also makes it possible to set a different default value in a child choice field depending on the selected value in a parent choice field.

Data import. During data import, an option is available to add missing choice values to a choice field. This can be helpful when doing automated data imports from external systems where new choice values may have been added.

Localization. Choice values are stored as integers in the database, and the value a user sees is based on the session's language. This means that the same internal database value can appear in different languages for different users. This is convenient if your users speak more than one language.

Searching considerations. When searching, choice values are shown as a drop-down list, making it easy to search. They are indexed in descending order, enabling users to search for values less than or greater than a particular choice. For instance, a search for more than "Signed" will find all items above Signed on the choice list:

Saved searches based on choice fields actually store the integer value of the choice, so if you edit the choice list to change the wording of a choice, any searches using that choice are updated seamlessly and continue to function.

Advantages of Choice and Multi-Choice Fields

  • It takes very little time to implement a choice field.
  • Choice fields are simple to use.
  • Choice fields can be hierarchically related to other choice fields using the built-in hierarchical dependence wizard.
  • Other fields can be visibility dependent or conditionally required based on the values in choice fields.
  • Field permissions are simple to maintain, since they are just set for the field.
  • Searching is simple and reliable, even if choice values are modified.
  • If the option to add missing choice values is selected, scheduled imports can easily handle additions to the choice values in other systems.
  • In a multilingual business environment, choice fields are easy to use and maintain since the value in the database is displayed to users in their session's language.

Disadvantages of Choice or Multi-Choice Fields

  • Choice lists can only be edited by an admin user through the Setup [Table] menu. They cannot be changed by other power users.
  • Managing hierarchical dependencies can only be done by an admin user, and can be a bit tricky to set up and maintain.
  • Although visibility dependence is built-in, other dependent data (such as other default field values based on the selection) is harder to handle.
  • If automation depends on specific choices, it must be handled in individual rules and actions for each choice value.
  • Very long choice lists can become difficult to maintain.
  • Permissions are all-or-nothing. If users can see the field, they can see all choices in the list—it is difficult to show different choices to different users.

Properties of a Background Table with Linked Field

Instead of a local choice or multi-choice field, a new table can be made to hold records that represent each of the list values. The background table holds individual records for each value, and they are displayed in a linked field as a drop-down list in the main process table.

One example of this arrangement is the field Contract Type in our Demo KB Contracts table. The field links to the Contract Types background table. For more details on how to set this up, refer to the Implementing a List of Values with a Background Table section which walks through the specifics using the example Contract Types/Contracts. We'll refer to this example in the discussion below.

Power users can edit lists. Since each list value is actually a record, power users can be given permission to create, edit, and delete the records, and therefore the list values. For example, in the Contracts/Contract Types scenario, contract managers or members of the legal team can change the existing contract types without asking an administrator to do this for them.

Filtered lists. Creating a value list with records in a background table makes it possible to filter the choices each user sees based on group permissions. For instance, users in different departments might see different choices for Contract Type, by linking contract types to specific departments and showing users only contract types linked to their own department. This can't be done with a regular choice or multi-choice field.

Linked relationships enable sophisticated automation. Using a background table creates a link between the process record and the background record. Establishing a linked relationship allows the system to run automation rules and actions based on a chain of relationships. Having a linked relationship also provides the ability to create a related table in the background table, for instance to show all the Contract records linked to a specific contract type.

In addition to the primary field used to make a selection, it is common to add other fields to the background table that are pulled into the main record along with the "selection" field to control automation or other processing. For instance, in the Contract Type example, we can add fields to the background table to set the default workflow, the print template used to generate the contract, the default Internal Signer, and to bring in any other data associated with a particular contract type. The additional fields can be pulled in as part of the linked set when the user chooses the contract type or accessed by rules through the linked relationship, and the linked information can then be used to simplify rules and automation.

For example, instead of needing a rule with several conditions to say "If contract type=x, set the internal signer to John Smith, Else If contract type=y, set the internal signer to Margery Woodson," we can simply pull in the Internal Signer field with the Contract Type linked set. Then, only one rule is needed to copy the value from the Internal Signer field for that contract type into the Contract table's Internal Signer field.

Note: You do not even need to include the Internal Signer field in the linked set. You can use an update fields action to copy the Internal Signer field value into the contract using the linked relationship to the Contract Types table, with a variable chain such as $contract_type.internal_signer, which goes to the linked Contract Type record and retrieves the Internal Signer value to insert into a field in the Contract record.

The ability to bring in other information and thereby reduce automation complexity is a major advantage of using a background table.

Setting visibility dependence. When other fields depend on the selection field's value, we usually handle this by adding a multi-choice field in the background table and bringing it into the main table as part of the linked set. The multi-choice linked field can then be used as the parent of any visibility, hierarchical or requirement dependencies. More details are provided on this method in the Setting Up Visibility Dependence When Using a Background Table section.

Unless created as a choice field in the background table, the linked field, e.g., Contract Type, cannot be used like a choice field to control the visibility, conditional requirement, or hierarchical choices of other fields in the main table. If the field must control the visibility of several other fields, we occasionally use a choice field in addition to a short text field in the background table, but this is not usually the best solution - see the Another Option for Setting Visibility Dependency with a Table section.

Parent-child list values. When a hierarchical parent-child relationship is needed between two sets of values, there are two main approaches used:

  • Create the parent field as a choice field in the process table, and create the child values in a background table; or
  • Create the parent and child values in two separate background tables.

In either case, the child field's background table would have a field holding the value(s) of the parent field for which it applies. After making a selection in the parent field in the process table, the child field's values would be filtered by a match on that parent field in the current record.

Although these approaches take more setup time than two choice fields, they are easier to maintain and can be managed by business users as well as admin users.

Advantages of a Background Table with Linked Field

  • It is easy to add any number of records; each record in the background table is a possible value in the choice list.
  • Non-admin users can be granted permission to add new records or modify existing records, thereby customizing and/or maintaining the list of available choices. Business users can manage workflows and automation directly.
  • You can set more granular permissions. Different sets of users can be shown different records, and therefore list values, based on permission filters.
  • Hierarchical lists, where the values in a parent list determine the available values in a child list, can easily be maintained and viewed by non-admin users.
  • Additional fields in the background table can hold other important information or data related to that choice value. That information is pulled into a process table along with the record chosen, simplifying automation.
  • Using a background table creates a link between the process record and the background record. Establishing a linked relationship allows automation to run based on a chain of relationships through records in other tables.

Disadvantages of a Background Table with Linked Field

  • It takes longer to implement a table and the corresponding linked field than a simple choice or multi-choice field.
  • A background table adds a layer of complexity to group permissions. In order to see and interact with the values in the process table, users must be granted appropriate access to the background table, its records, and its fields in addition to the linked field displayed in the process table.
  • Linked text fields cannot be the parent of a visibility dependence or conditional requirement without some additional setup to implement these dependencies.
  • Searching is less intuitive – no value list is displayed and the user has to type in the search criteria. Also, if a saved search is based on a hard-coded value of a linked text field, such as Contract Type=NDA, and someone edits the underlying contract type record and changes the wording of that field to "Mutual NDA," the search will not be updated and will no longer find the records it found previously.

Key Questions

To determine which method is better for your implementation, think about the detailed requirements.
This set of questions guides you toward choosing the best field type to implement.

  1. Who maintains the data? If power or end users maintain the data, a table is the only option. Choice lists cannot be modified by business users. If only administrators modify the choice list values, then either option may work.
  2. Is there any other data and/or automation based on this choice selection? For example, should selecting the item result in a different set of default field values, a different assignee, different print templates or workflows applied? If so, a background table is definitely the best option, as it allows cleaner and simpler automation that can be differentiated and maintained by non-admin users.
  3. Do all users see the same value list? Or, should the list be filtered in any way based on who is viewing it? If you need to filter the list for different user groups, a linked field with a background table is the best option.
  4. Is the visibility of several other fields dependent on the value selected? If the visibility dependence is very simple and not likely to change, a choice field is easiest. But you can set this up with a background table, which can then be maintained by non-admin users. If the dependent relationships are dynamic, that can be an advantage over choice fields.
  5. Will you be syncing the list values with a backend system through an automated import? Choice fields have the advantage of an import option to add missing choices automatically. If the data may change in the backend system and you are using a background table, you need to do a separate import/update of the background table records first before importing into the process table.
  6. Does your system use multiple languages? If so, a choice field is simpler to use. Choice fields can be translated while still maintaining their unique underlying values in the database. Record data can't be translated or localized. So if you use a background table in a multilingual KB, you will need a record for each language translation of a value, a field identifying the language, and language-based filters in the process table to show the right records. Further, a record selected by a Spanish-language user is not the same record selected by an English-language user, so uniqueness of the list value is eliminated. Typically in this case we also include a "code" field that is shared by the different translations of a particular value. You can then report on the "code" field rather than the value displayed to the user to get an accurate picture of the data.

Sample Scenarios

After deciding which option makes more sense for your implementation, keep in mind the pros and cons of that method. No matter what decision you make, it impacts the rest of the design and build-out process. For a summary of the factors to consider when determining the benefits and drawbacks of each option, see Criteria for Evaluating Design.

Choice Field Examples

Let's look at some examples where a choice field was implemented:

Company Roles in Companies

In the Companies table, the field Company Roles is a multi-choice field. In this situation we used a multi-choice field because:

  • There is no additional data or automation associated with a company's Company Roles;
  • The number of values for Company Roles is not very large;
  • The Company Roles values can be easily made hierarchically dependent on Primary Role as a choice field using built-in settings in the field wizard.
  • It is easier to search for companies with specific roles using a choice field.

The default value and the options shown in Company Roles are dependent on the Primary Role chosen

Type of Problem in Helpdesk Cases

The default field Type of Problem in a helpdesk case is a choice field. In this situation we used a choice field because:

  • There is no default automation associated with the Type of Problem.
  • The list of problem types is relatively short.
  • The available Subtype of Problem values are hierarchically dependent on the Type of Problem chosen, a simple use of the built-in dependence wizard.
  • These values are easy to search, and people will often want to search by this field.
  • Any changes to the values or dependencies will only be made by an admin user.

Subtype of Problem is a choice field that is hierarchically dependent on the Type of Problem choice field

Note that while this out-of-the-box setup is used in our BPM template KB as a simple setup that is easy to manage, it is generally not adequate for a more sophisticated implementation. In our standard ITIL KB, we use the more robust table-based solution for the Service Requests table - the table corresponding to the Helpdesk Cases table.

The Service Requests table uses a choice field for the Service Category (the parent field) and then a background table called Services that defines the child choices for particular services. The selected Service defines the default assigned team for new service requests, the additional fields to show in the service request based on the service selected, and the default SLA (Service-Level Agreement) for the service request, as well as a description of the service and any special instructions to be displayed to the user for that service.

See the Figure below - when the user selects the Service, they see the Description of Service and Special Instructions field defined for that Service as well as any visibility dependent fields.

 In the ITIL Service Request table, the Service dropdown is a link to the Services background table

A subset of the records in the Services table in ITIL. Services available within a Service Request (Figure 8) are shown if they are available for the Service Category chosen

Linked Field from Background Table Examples

Let's look at some additional examples where a background table and linked field were implemented:

Contract Type in Contracts

The field to specify Contract Type within a contract record is a link to the Contract Types background table. A background table is the better option because:

  • Information specific to each contract type can be pulled in when the Contract Type is selected. This includes the default approval workflow and the default print template, if any. These values are then used by the automation to generate approvals and the correct document.
  • The list of contract types can be filtered for a user based on a match of the user's department, his permissions, the Party Type selected, or any other desired criteria. Often is desirable for different users to see different values.
  • Power users such as contract managers or the legal team need to be able to create or edit existing contract types. If Contract Type were a simple choice field, contract managers would not be able to edit or add to the list of options unless given admin privileges.

The list of Contract Types is filtered based on the Contract Party Type chosen. The Contract Type selected determines the default Workflow Title, as well as the visibility of the fields Contract Start Date and Contract End Date (not shown)

Department in Contracts

Within a contract, the For Department field is a link to the Departments table. This is the best option because:

  • By making Departments a background table we can link users to departments and identify a department head who is pulled into the contract automatically, making it easy to generate an approval for the relevant department head.
  • Thanks to the linked relationship, we can create a related table of Contracts in the Departments table to show all contracts for a particular department. We can also show the total value of their vendor contracts and compare it to a departmental budget.
  • We can also track other information about the department, such as a default account code for billing, specific contract types available for that department, and so on.

In a contract, choosing the department pulls in the main contact for the selected department, the Department Head and we often use that field to generate an approval record:

Based on the selection in the For Department field, the Department Head is shown in the contract record

Attachment Type in Attachments

An Attachment Type is selected from a list in an Attachment record (attachments are shown in a table within a contract record). In this situation:

  • There are a relatively small number of values, and they are relatively static.
  • Selecting different attachment types results in different fields being shown in the Attachment record. For instance, some attachment types need to display an Expiration Date field while others do not.

This decision could have gone either way. A choice field would have handled the visibility dependencies more easily, but we wanted non-admin users to be able to modify the attachment types, which is only possible with a table. We therefore chose to implement Attachment Types as a background table, with our standard "Extra Fields to Show" setup used to handle visibility dependence - see The "Extra Fields to Show" Solution below.

Implementation Details

Below are some examples of implementation solutions for creating a list of values.

Implementing a List of Values with a Background Table

This example implementation describes the method used to create a list of Contract Types to choose from in the Contracts table. You can follow along and generalize this process to any pair of process and background tables to suit your needs.

  1. First create a table called Contract Types. This is now the background table.
  2. Add a short text field called Contract Type (typically you will choose the options to make this field the Summary Field, required, and unique on the Options tab).
  3. Add any other fields you want to associate with a contract type, such as default workflow, print template, etc.
  4. Once the background table is saved, create a record for each Contract Type that will appear in the list. For example, create five records with these values in the Contract Type field: Service Contract, Non-Disclosure Agreement, Software License, Subscription Service, Product Support and Upgrade.
  5. The next step is to create a new field in the original table (the Contracts table) that links to the Contract Type field in the Contract Types table.
    1. For a single value choice list (users can only select one contract type), use a Link to selected fields from other table field type. Include the Contract Type field and any other fields from the background table that you want. To display the values as a drop-down list, choose List of values for the primary field on the Display tab.
    2. For a multi-value list, use a Link to single field from other table and select either Yes, fast search or Yes, save space on the Mapping tab of the field wizard. This parallels the basic function of a Multi-choice field and allows you to display the contract types as checkboxes or as a multi-value lookup.

Setting Up Visibility Dependence When Using a Background Table

Below are two solutions for visibility dependence with background tables. 

The Extra Fields to Show Solution

Use Case: You are implementing a value list using a linked background table (as in the contract types example above). There are several fields in the process table you want to make visibility-dependent on the value selected in the selection field, but you can't set up visibility dependencies based on a linked short text field.

Solution: To accomplish this is, add a multi-choice field to the background table whose only purpose is to control the visibility of other fields, and call it Extra Fields to Show or something similar (this article uses Fields to Show and Extra Fields to Show interchangeably).

Visibility dependence can be based not just on a local choice field, but also on a linked choice or multi-choice field from another table.
By adding an Extra Fields to Show field in the background table and pulling it into the process table along with the selection field, we can establish visibility dependence just as we would have done with a local choice field in the process table.

As an example, our Demo KB uses this solution for the Attachment Type field in the Attachments table. The Attachment Types background table contains the short text field, Attachment Type, and the multi-choice field called Fields to Show.

In the Attachments table, Fields to Show is part of the Attachment Type linked set and renamed to Attachment Type Fields to Show. In the Attachment table, fields such as Coverage Amount and Expiration Date are visibility dependent on the Attachment Type Fields to Show, and only appear when that field contains a certain value.

In the Attachment Type table, Fields to Show is a multi-choice field used to control visibility of other fields

Setting up Multi-Choice Values in the Extra Fields to Show

The simplest method to define necessary choice values in Extra Fields to Show is to add a choice value for every field in the process table that depends on the Attachment Type. In other words, set up the choice list so it contains the names of all visibility dependent fields.

Multiple records in the Attachment Types table can include the same field in Extra Fields to Show. For example, the Coverage Amount field appears for both "Performance Bond" and "Insurance Certificate" attachment types. When setting the visibility dependence of the Coverage Amount field in the Attachments table, we make it visible if the value contains "Coverage Amount" and the field will appear for all the relevant attachment types - those in which Coverage Amount is selected in the Fields to Show.

The advantage of adding a choice value for each visibility dependent field in the process table is system transparency for admins and users. It makes it clear to someone who manages Attachment Type records exactly which fields will appear if they select a particular value.

One choice value per dependent field also simplifies setting up visibility dependence for those fields – the admin just selects the field's own name as the value to match.

Process Summary

The steps below summarize how to implement an "Extra Fields to Show" setup:

  1. Create a background table and short text field to act as the value list in the process table.
  2. In the background table, create a multi-choice field called Extra Fields to Show.
    1. Create the choice list for Extra fields to Show and include a value for each visibility-controlled field in the process table..
  3. Edit each relevant record in the background table. Select the dependent field names from the Extra Fields to Show list that are appropriate for that record.
  1. In the process table, create the linked field set to include the short text field and Extra Fields to Show.
  2. Still on the process table, edit each dependent field and set the visibility dependence: when Extra Fields to Show contains the value of "this field's" name. For instance, Coverage Amount is visibility dependent on the value "Coverage Amount" in the Attachment Type Fields to Show.

Adding new dependent fields

If a new dependent field is added to the process table, complete the following steps:

  1. In the background table, edit the "Extra Fields to Show" choice list and add the new field's name to the list of values.
  2. Edit any applicable records in the background table and add the new field to the selections in Extra Fields to Show.
  3. Create the new field in the process table.
  4. In the process table, edit the field you just created and set the visibility dependence based on a match in Extra Fields to Show.

Another Option for Setting Visibility Dependency with a Table

On some occasions, the best solution is to create a background table as above but to use a choice field whose choices match the list values to handle the visibility dependence, instead of the multi-choice field.
For instance, in the example above, we could create two fields in the Attachment Types table: the short text field called Attachment Type, and a choice field called Attachment Type Choices, instead of the multi-value Fields to Show field.

This approach makes sense when:

  • A background table is needed to pull in other field values and to control automation.
  • There are a limited number of choices that will change rarely, whereas there may be more significant ongoing changes in the process table in terms of adding new visibility dependent fields.
  • Only admin users need to add new values.
  • There are several visibility or conditional requirement dependencies in the process table.

Here's how to set this up:

  1. Create the background table.
  2. Create the short text field as the selection field and a matching choice field with the list of values you want to display.
  3. Add any other fields necessary to the background table and save it.
  4. With the background table in place, add a new record for each value in the choice list.
    1. In record 1, choose the first value from the choice list. Then, set the value of the short text field to match that value.
    2. In record 2, choose the second value from the choice list. Again, set the value of the short text field to match that value.
    3. Repeat the step above until all records are added.
  5. In the process table, create a linked set which includes the short text and choice fields. Add the text field to the layout for users to select a value; the choice field is used in the background for automation.
  6. When setting up the visibility dependent fields, use the choice field to define the visibility.

To add new choices to an existing system with this setup, an admin will first edit the choice list, then add a new record and set the field values to match.