Requirements and Considerations for Reference Tables

Anyone in your organization can create a reference table. Users with full Reporting Rights can use the Accolade Office Extensions add-in and online reports to create the table automatically with the correct placement in a worksheet. You can also create tables manually. After a table is created, Administrators and Process Designers add the table definition to Accolade and upload the file.

Accolade supports reference tables as spreadsheet, CSV, and XML files through the Reference Tables page, and through an auto-loader service. The information and examples provided are in spreadsheet format, contact Sopheon Customer Support for example files in additional formats.

Reference Table Requirements

A table within a workbook must meet the following requirements to be used as a reference table in Accolade:

Component Requirements and Considerations

Size and Location
  • Have at least one column and two rows.
  • Must start in cell A1 in the worksheet.
  • The first blank cell in the top row determines the right edge of the table.
  • The first blank cell in the left most column determines the bottom of the table.

Column Headings
  • Must be in the first row in the worksheet; therefore, the first column heading is in cell A1.
  • Can be named anything, and the name is used as the column's display name within Accolade. Any characters other than letters, numbers, and underscore, and any numbers before the first letter are removed to create the column's system name.
  • Must be unique within the table, and are case insensitive.

Data
  • Starts in the second row in the worksheet, directly under the column headings.
  • Is either a number, date, or string. Accolade recognizes three types of data in table columns: number, date, and string.

Accolade determines the data type of each column using the initial contents of the column when the table is uploaded for the first time. To force a column containing only numbers or only dates to be defined as string type, add a letter in at least one row of the column in the initial upload. You can delete the letter from the next version, but the data type remains "string".

  • All dates are in mm/dd/yyyy format.
  • The decimal separator in numbers must be a period (.).

Accolade Field Codes

To make the table's values display using Accolade field codes, for example in documents or calculated metrics, every value in the table's leftmost column must meet the following criteria:

  • Be unique (case insensitive) in the column.
  • Begin with a letter.
  • Contain only letters and numbers.
  • Contain no more than 16 characters.

In addition, the Unique values in first column check box must be selected on the Reference Table page when the table is added in Accolade.

Reference Table Limitations

Reference tables contain the following limitations:

  • There can be only one reference table per workbook. You can create templates and documents using the Accolade Office Extensions add-in. However, when building a reference table, the spreadsheet file can have only one table that is considered a reference table.
  • Versions after the first can have more columns than the first version, but cannot contain fewer columns.
  • Each cell in the table has a 500 character limit.
  • The broken bar ( ¦ ) character is prohibited. Use the pipe ( | ) characters instead. Many keyboards contain a key that displays a broken bar but that actually inserts a pipe.

Identifying the Worksheet that Contains the Reference Table

The reference table must exist on what is considered to be the first worksheet. In a file where the worksheets have been renamed, rearranged, or hidden, and it is no longer which worksheet is the first, create a custom document property called SGM_RefTableSheet and enter the name of the worksheet that contains the reference table as the custom property's value. Refer to Microsoft's online Help for information about adding custom properties to files.

Learn More: