Rows in a reference data set can be either Enabled or Disabled. Disabled rows are skipped when the reference data set is used as a lookup. For example, in CountryCodes reference table this can be used to store country codes for countries that no longer exist.

The Enabled/Disabled status or a row is stored in a column called Enabled. This column is always present in the data set and cannot be removed.

When new rows are created, they always start as disabled and need to be enabled and approved to appear in the data when the reference data set is used.

Note that changing the Enabled status of the row is the same as changing any other column – the change must be approved before it is reflected in the data used during lookups.

Rows in reference data sets have quite simple lifecycle with stages centered around row enabled/disabled status and its change status. Unlike in transactional data sets, there is no single status column that would represent all this data.

The lifecycle follows essentially just two stages – a row is either approved or changed (not approved). Whenever any change is made to a row (i.e., any column is modified), copy of the whole row is created and the change is applied to the copy. This copy is called change row. There is always at maximum one change row per data set row – all changes to a row accumulate in the change row.

When performing lookups into the data set, change rows are ignored. This means that only approved version of each row can be returned by a lookup.

Whenever Approve action is called on the change row, the original row is replaced with the change row and the change row is removed. The change row therefore becomes the approved row. All changes are visible to queries into the data set from that point (audit log is updated as well).

When working with reference data sets, several actions are available in the data editor:

Following row actions are available:

To help users navigate reference data sets, four different view modes are available when working with reference data. View modes can be switched via view mode drop down in the top right corner of the screen:

View modes do not affect data in the reference data set but help users see the data and changes in a way that makes it easier to accomplish certain tasks. The following view modes are available:

Note that not all modes are available to all users – read-only users only see published data and therefore cannot use view modes that show changes or errors.

Reference data sets can be used as lookups when defining the layout of another data set using the Restrict to lookup functionality. For example, you can have Customers data set with addresses where country names can be a reference data set, or Invoices data set where currency codes are defined in CurrencyCodes reference data set and so on.

To be able to use a reference data set like this, it must satisfy the following conditions:

To use this data set, simply configure it in the target column via Restrict to lookup like this:

When configured like this, the values of the target column will be shown as a dropdown populated by the values from the reference data set.

Note that in the above example the data stored in the Customers data set in the Country column is the two-letter code. The Data Manager automatically translates the two-letter code to country name based on values in the CountryCodes reference data set. This makes it easier for the user to navigate reference data sets which have many values or where the “codes” are opaque (e.g., product id etc.).

Also note that the reference table used in this way can have more than two columns. The additional columns will be displayed alongside the code and label when the dropdown is shown in the data editor. Typically, these additional columns are used to provide more information to help the user pick the correct value when editing the data.

Reference data sets can also be used when working in CloverDX Designer. The Designer provides Lookup functionality that allows users to define different kinds of lookup tables depending on their exact requirements and usage patterns. One of the lookup types is Reference data lookup table.

When such a reference data lookup table is created, it is linked directly with a specific reference data set and will use its data when the job runs.

The Designer allows complex lookups besides just lookups with a single key column. The reference data sets designed to be used in Designer can therefore have compound keys that have multiple columns and can also have effective dates enabled.

To learn more about how this can be implemented in CloverDX Designer, please see Reference lookup table chapter in the Developer’s guide.

Note that regardless of how the data is used in the Designer, the maintenance of the data set remains the same and does not require any access to CloverDX Designer.

The first screen of the wizard allows you to configure basic settings for the data set like its name, description, and more.

The following settings can be configured on the Basic settings page:

Data layout specifies the structure of each row in the data set – the column names, types, and other properties.

All rows in the data set have the same layout. The layout can contain any number of user-defined columns and will also contain a set of system-defined columns that are always present and cannot be removed (but can be moved around to a different place within the row).

The layout of the data set is shown in a table which lists all the columns. The first column (at the top of the table) is the first column of the record. Columns can be reordered freely by using the drag handle on the left side of each column’s row in the layout table.

Columns in the data set can have one of the following data types:

When you create a new column, the following column settings are shown in a Create a new column side bar:

The following settings can be configured for the new column:

Unlike transactional data sets, reference data sets do not have column visibility settings (all columns are visible except for the system columns which start as hidden). There is also no editable setting since all columns in the reference data set are editable (except for system columns which are maintained by the system automatically).

Each reference data set must have a key. A key defines how to search for data in the data set when performing lookups. Key can be composed of any number of columns of different types and keys of all published (approved) rows must be unique.

To configure a key, use the Key columns page of the wizard. You can add any number of columns from the data set to the key and reorder them as needed by using the drag handles to the left of each column.

The order of the keys specified on this page of the wizard is important as it defines the order of the keys as used when lookups are performed in the reference data set.

Note that if you create a row which has a duplicate key, you will not be able to approve it. The rows with duplicate keys will be highlighted with the red marker. To see all conflicts, you can use the Show rows with errors only view mode in the data editor.

When working with data set which has effective dates enabled, an extra “column” is added to the data set key. This column called timestamp must be used whenever a lookup is performed to ensure that the correct version of the data set row is found. For example, consider a reference data set representing product catalog where each product can have different prices at different date. To find the product price, you have to supply the product code as well as the timestamp for when you want the price to be valid. The timestamp is then matched to effective dates intervals for rows that have the same product code as the code you are trying to find.

Since effective dates must not overlap for rows with the same keys, Data Manager will not allow you to approve rows where this condition is not satisfied. As with duplicate keys, the rows that overlap are highlighted with red markers in Valid from and Valid to columns.

Each data set has its own set of permissions – list of users and their roles with regards to the operations they can perform on the data set.

Roles are configured on a User roles page when creating a new data set:

Clicking on a dropdown for each role will give you a list of all users on the Server and you can select any number of users in each role.

The roles form a simple hierarchy – a higher role has more permissions than a lower role. This means that Admin can do everything that Data Approver can, and Data Approver can do everything that Data Editor can. This means that for each user it is enough to place them into the role with the highest permissions they will need for their work. See more details in Data Set Permissions section.

In reference data sets one more role is available – a role of a read-only user. This role allows users to see the published data in the data set, but they cannot make any changes to it. As such, this role is ideal for processes that do not need to modify the data set and only perform lookup in it or for users who need to be able to see the data but have no need to be able to make any changes to it.

When changing the layout, the following operations are allowed:

Note that it is not possible to change the column type in a data set that contains data. However, it is possible to change the type of a column in an empty data set.

Also note that if you delete a column, the data will be removed and cannot be recovered (the delete is hard delete without ability to undo).

The layout changes are only applied after you click on the Save button and all layout changes are applied at once.

Key columns cannot be changed if the data set contains any data. This is to ensure that there are no conflicts in the data caused by duplicate keys or overlapping effective dates intervals.

You can change permissions freely – add or remove users in various roles as needed. Note that you cannot remove yourself from the Admin list. This ensures that you cannot create a data set that cannot be changed because all permissions to it have been removed.