Managing Reference Data

Overview

Reference data is static data that is used to classify or categorize other data. Examples of reference data include diagnostic codes, state names, supplier industry codes, and product codes. Storing reference data in a lookup table allows you to design a more extensible data model.

Use reference data in instances where user input is restricted to a set of specific values (such as dropdowns). Reference data tables commonly include columns for ID, type, and value, but you can add more columns if needed for your use case.

For the fastest and easier experience, we recommend using a record type to create, update, and query for these reference data values. Learn more about data modeling with Appian Records.

Set up Reference Data

To set up your reference data, you’ll need a table to store the reference data and a corresponding record type to access and use the reference data in Appian.

You can automatically generate your reference table and record type when you use codeless data modeling to configure a record type. Alternatively, you can manually create your table and record type.

For example, every support case can have three different statuses: Open, In Progress, or Closed. The case status values should be stored as reference data to easily maintain these static values.

To set up this reference data, you’d first create a status table. It could look like this:

You’d then create a Case Status record type and use this table as the source. When you enable data sync on the Case Status record type, you can create record type relationships to connect the Case Status record type to the Case record type.

If the case status options ever need to change, having a separate record type will make the refactoring easier. You can create record actions that allow users to update your reference data and use record action security to ensure that only the right users can make changes to this data.

Use Reference Data

When your base record type has a relationship to your reference record types, you can record type relationship references to access the data—just like any other relationship.

For example, to query all cases and their status and priority, you can do a simple query using a!queryRecordType():

a!queryRecordType(
    recordType: recordType!Case,
    fields: {
        recordType!Case.fields.title,
        recordType!Case.relationships.status.fields.label,
        recordType!Case.relationships.priority.fields.label
    },
    pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 500)
).data

A sample return value could be: 

[SO23935, Open, Critical]

Since your reference data is stored in a record type, you can also leverage all records-powered components (like a read-only grid or chart) to seamlessly display your reference data.

Evolving a Single Reference Table into Multiple

Sometimes, data that was initially reference data for one application might require more information to be stored about it over time in other, new applications. 

For example, you might have a list of Departments in your reference data, but over time you need to add fields for the head of the department and which office a department is located in. In this case, the list of reference data for that particular type should be migrated over into its own table. Once this is done, you can switch the existing record type from pointing at the master reference table to the new, specific table and everything should continue to work as expected. 

When migrating reference data to a separate table:

• Use the same primary key IDs for the data as you had in the old table.
• Mark the old rows in the master reference table as inactive (this can be done using a new column called isActive).
• Update foreign keys in the database if they exist.
• Use the same field names for the columns that exist on the master reference table.
• Double check that the record type's security is valid for both existing and new use cases.