Adding Or Configuring Lookups

Lookups are one of the most flexible datatypes in the application, easily configurable if you know how they work. Lookups are good for storing pre-determined, selectable lists of information. This ensures that the data is stored consistently, as opposed to a straight text field which leaves room for errors in spelling and formatting. This document describes the Lookup datatype, and how you can utilize it to get the most out of your ClientSpace system.

Step-by-step guide

Exercise extreme caution when administering Lookup Groups and Lookup values. Some of these values (such as CSC_Status) are utilized by the system in triggering business logic, and changing or deleting these values can result in System Issues, which may require programming to resolve. If you are unsure about changing or deleting a value, consult your application specialist, or log a support case. It is far better to de-activate a value, rather than delete it, but it is even better to err on the side of caution and ask first.

Lookups consist of a Lookup Group and Lookup values, which contain at a minimum a Code and Decode value. For new lookups, first create the lookup group.

To add or configure a lookup:

Go to System Admin > Lookups > Manage Groups.
The Lookup Groups list is displayed. From here you can manage or add lookup Groups, or edit the values of an existing group.
To add a new Lookup Group, click Add.
The Lookup Group Details form opens.
Complete the Group Name field and add a Description if you have many similar groups, to avoid confusion when using lookups.
If the lookup group has an associated metadata dataform, select the appropriate form using the Metadata Dataform field.*
Click Save.

You are returned to the Lookup Groups list.

To add values to the new group:

Go to System Admin > Lookups.
Locate and open the new group.
Before adding lookup values to a group, it is a good idea to first Search for the Group in question, and review the existing Lookup Values (Code and Decode) to make sure you do not create duplicates. Remember to select More > Show All when searching, as Archived values are hidden from the list by default.
Click Add.
The Lookup Details form opens.
Select the Group you would like to add lookup values to and complete the Code and Decode fields.
The Code value of a Lookup is the actual data value that is stored in the database when a record is saved.
1. It is a best practice to not change the Code value of a lookup included with the system once set as there may be logic which includes this code value in the application.
2. It is a best practice to remove any extraneous spaces and special characters from the Code value when creating a new lookup record and to use PascalCase formatting - that is the first letter of each individual word is capitalized. This is different from CamelCase formatting as seen in the Field Name on the following screenshot, as CamelCase may begin with lowercase lettering.
The Decode value of a Lookup is what is displayed on the dataform page when you view ClientSpace in your browser. Code and Decode are often the same but are not required to be.
The Filter Value field on the Lookup can be used to further refine Lookups, or utilize one Lookup Group more flexibly. This Filter Value field works in conjunction with the Filter Value field on the dataform detail form, where it is located just below the Related Field that you use to associate a Lookup with a dataform field.

Let's say for example that you have a Colors lookup that is a simple list of colors from Infra-Red to Ultra-Violet. You want to have the entire list available, but you would also like to use the same lookup to just display the Visible spectrum of colors (ROYGBIV).
You can add all of the colors to the lookup, including Infra-Red and Ultra-Violet, but then add a Filter Value of VISIBLE to just those colors in the visible spectrum and a Filter Value of INVISIBLE to Infra-Red and Ultra-Violet.
When you use the hierarchical lookup filtering (see below) to filter a dataform field, you can add a Filter Value of VISIBLE to the filter source field definition, so that Infra-Red and Ultra-Violet are not displayed.
Display Order assigns how the lookup values will be displayed in the list.
Add a Description if the Decode of the Lookup does not provide a logical explanation of its purpose.
Lookup Metadata* - A lookup can have associated metadata. The metadata form contains fields that can be used throughout the application where the lookup is used. For more information about metadata, see below.
ImportID (in the Administrative fieldset) is used by our import engine to translate values from externally produced files to a Lookup Code value that can be stored in the system.***

Best Practices - Show Lookups

You can also use the Show Lookups option from the dataform to manage the lookups used on that dataform. From the dataform in question, go to the Action Center and use the drop down to select Show Lookups. You will be presented with the Lookup Administration module, but filtered for only those lookups that are used on that dataform.

*Advanced lookup configuration

If you want to determine what Lookup Group is being used by a dataform field, check the Related Fields section of the Field configuration. This will store the Lookup Group name associated with the field in question.

Some lookups can have Metadata associated with them. This will be noted in the Metadata column of the lookup list. Metadata is literally data about data and provides even more flexibility for Lookup use and allows you to have information that is directly associated with a particular lookup selection. Metadata is configured by attaching a special form containing the metadata fields to the lookup group.

Two examples of lookups with metadata are:

Contract Type - The contract type lookup in ClientSpace PEO has metadata associated that can determine

What Template the contract type can be used with
What Legal Entity / SUTA defaults are used when that contract type is selected.
Default Workers' Comp Configuration for a workspace.

Pay Frequency - The pay frequency in ClientSpace PEO has metadata associated that can determine

How many pay cycles per year there are for that frequency (i.e., Weekly = 52)
Minimum per check charge - used to ensure a certain amount of admin is charged per year.

Before attempting to configure lookup metadata, contact your application specialist.

*** The ImportID field is used for translation during Imports that are configured with the Lookup datatype. This field can either hold a single value such as W to match what is exported from your external system or can hold a pipe-delimited list of optional values that will translate to the lookup such as |W|w|Weekly|WEEKLY|WEEK|Week|. When passing multiple values, each value is evaluated and this check is case sensitive, so W is a different value than w. When stringing multiple values together like this, make sure that the last character in the string is the pipe delimiter |.

Hierarchical Lookup Filtering

The application allows lookup field filtering based on another dataform field, allowing you to configure filtered field functionality on data forms.

The filter values are defined using the Filter Value field, on the Lookup Detail. So, using Status/Category as an example, given the following configuration, if you pick Category1, then the status field will be filtered to show only Status1, Status2, and Status3:

Status Lookup Item	Filter Value
Status1	Category1
Status2	Category1
Status3	Category1
Status4	Category2
Status5	Category2

This filtering also allows for comma separated values (do not leave spaces between your values) in the Filter Value to provide an OR mechanism so that, in this case, the Status can live in multiple categories. So, given the following configuration, Status1 would show up when both Category1 and Category2 are selected:

Status Lookup Item	Filter Value
Status1	Category1,Category2
Status2	Category1
Status3	Category1
Status4	Category2
Status5	Category2

Dataform Configuration

The dataform configuration for lookup fields points to another field on the form as a Source for its filter. There is a Filter Source field, under the Filter Value field, when configuring a lookup field on a dataform. You put the ID of the field that you want to be the Source for our filter. When that field's value changes, our field will be filtered with that value.

So, using the example configuration above, if we had an luCategory and luStatus field on our dataform: in the field properties for luStatus we would enter the value luCategory in the Filter Source field. luStatus is filtered as luCategory is changed. If there is a value in both the Filter Value and Filter Source fields, the system uses Filter Source.

Watch the video about how to configure this here.

KnowledgeSpace

Adding Or Configuring Lookups

Step-by-step guide

Related articles