Adding Reference Tables

Anyone in your organization can create a reference table. However, only Administrators or Process Designers can add the first version of a reference table to Accolade. After a reference table is added to Accolade, its table owner can then upload later versions and set it for automatic upload to maintain the contents of the table.

To add a reference table to Accolade:

1. From the System menu, select Content Sources > Reference Tables.

To narrow the reference table list, search by the table name, system name, or category.

2. Do one of the following:

• To add a new reference table - Click Add New in the upper right corner of the page.
• To edit the details of an existing reference table - Click the name of the reference table to open it for editing.

3. Complete the following information to identify and describe the content of the reference table:

Important! Reference tables used for project data or resources imports have unique naming requirements. See Importing Project and Resource Data Overview for more information.

Required fields display with red text and an asterisk * if the field is empty.

 

Field	Description
Name	Enter a unique name, up to 80 characters long, which identifies the reference table.
System Name	Enter a unique name that identifies the table in reporting views, reports created using the Accolade Office Extensions add-in, field codes, and other places in Accolade. The name must be unique among reference tables and can contain only letters (English alphabet), numbers, and the underscore.
Description	Enter a description of the purpose or nature of the reference table. This description helps other users identify the reference table throughout the system.
Category	Enter or select the group to which this reference table belongs. More... Use categories to organize like tables together. For example, you may choose to group all the reference tables used to import data into the same category, in order to separate them from tables that provide additional reference information for users. Leave this field blank to add to the Default category. To define a new category, select New Category and enter the category name. To delete a category, remove every item from the category. Empty categories are deleted automatically.
Owner(s)	Click and enter users that can own updating the details and versions of this table. To filter the list of users, enter one or more search criteria to filter by name, login name, email address, function, or extended field. More... Clicking Select current user will assign the role to the current user (if they have the appropriate rights). Selecting a Function in the drop-down will display available users that are assigned to the function. The current selection defaults to the function to which you are assigning a user, however depending on the project configuration, you can assign any user. Clicking the Show advanced filters check box displays or hides the additional filter options. Clicking Clear removes the current user assignment and displays [None] to indicate that no user is assigned. Table owners maintain the content of the reference table after its initial upload. Note:  Only users with the Reference Table Manager user role are available to select as table owners.
Configuration Access Groups	Select the access group to which the reference table setup belongs. A user must be assigned to the same access group as the reference table to edit details such as the name, system name, etc. Note:  Only Owners are able to upload new versions of files.
Access Group	Select the access group to which the reference table content is available to. A user must be assigned to the same access group as the reference table to see the table and use the data within it. To make the table and its contents available to all users, select [None] - Accessible by all users. Select [None] - Accessible by all users when adding the currency reference table. Note:  Administrators and Process Designers can create reference tables that are outside their access group rights; however they cannot see or modify the tables after creation.

4. In the File field, click and select the spreadsheet, CSV, or XML file that contains the reference table.

The columns from the table in the uploaded file are loaded and display in the Column Definitions section of the page.

Column data types are automatically determined based on the reference table information included in the initial upload, either when adding a new reference table or when adding a new version with new columns to an existing table. If desired, Number and Date data types for new columns can be changed by clicking on the Data Type drop-down and selecting String. Once the columns have been added and you click Create or Apply, existing data types are not able to be changed in later versions of the table.

5. Select the Unique Values in First Column check box if the table contains unique values in each row of the table's leftmost column. Selecting this option allows cell values to display using Accolade field codes and to reference a cell in the table in calculated metrics.

Note:  If selected, you cannot change this setting in later versions of the table.

6. Complete the information about version limits and how the file is updated to Accolade. More...

    

   Field	Description
   Versioning	Select how many versions of the table to save in the database. If you enter a maximum number instead of selecting Unlimited, older versions of the table are discarded as newer versions are added. Sopheon recommends adding no more than 250 versions of a single reference table.
   Loading Schedule	If you want to enable automatic loading of new versions of the document through either a directory on the application server or an FTP website, select an option to upload a version immediately or at a scheduled time each day. Load immediately - Loads any files placed in the drop box folder on the application server immediately. Daily at - Loads any files placed in the drop box folder on the application server at the time you specify. Note that this field is in 24-hour time. To upload all files at 10:00 pm, enter 22:00. The time entered is the time in the application server's time zone, which may be different than the time zone you are in when entering the time in Accolade. If the reference table is assigned to an access group, the user assigned the Service Account user role must be assigned the same access group for the reference table to automatically upload. If you are creating reference tables for data import and the imports are large, schedule them to run overnight. Reference tables for information such as currency exchanges are smaller and can load immediately. If you do not want to enable automatic loading for the reference table, select Off.

7. Select one or more of the "Available to" options to make the contents of the table available to other portions of Accolade.

 

Field	Description
Available to Metrics	Select this check box to make the content within the table available for use in metrics, for example, when defining list values or to use in creating cascading list or calculated metrics.
Available to Portfolio Optimizer	Select this check box to make the content within the table available to Accolade Portfolio Optimizer, for example, for strategic bucket charts or calculated metrics tables.
Available to Reporting	Select this check box to allow the reference table to be used in reports created in Accolade Online Reporting or reports created using the Accolade Office Extensions add-in. For example, select this check box for reference tables that contain information that is valuable to report and track for historical purposes, such as those used for currencies or target information. A reference table must also be added to Accolade as an online report in order to be available to use in reporting. The reference table information is included by selecting the Reference Tables option when selecting columns to be included in the report. See Creating Online Reports within Accolade. Clear this check box for reference tables used for importing data into Accolade or for tables that hold values for configuration, such as cascading list metric values.

8. Click Create to create the new reference table or Apply to save your changes to an existing table.
9. Review and update the column definitions to provide additional information about the columns in the reference table. More...

    

   Field	Description
   Display Name	Read-only The display name, based on the column headings from the uploaded file.
   System Name	Confirm or modify the system name for each column. Column system names must begin with a letter and contain only letters, numbers, and the underscore character. They can be abbreviated to make them easier to use in queries, reports, and Accolade fields.
   Data Type	Read-only The data type for the column.
   Number Format	For columns that contain number metrics, enter a custom number format to use when data in Number type columns is displayed in Smart documents. See Custom Format for Number Metrics for more information.

10. Click Apply to save your changes.

Notes:    To add version notes to a reference table, display the reference table details. In the Versions of the Reference Table section of the table, click in the reference table's Comments section to add comments, and click Apply to save your changes. To delete a version of a reference table, display the reference table details. In the Versions of this Reference Table section of the table, click for the version that you wish to delete and click Apply to save your changes. You cannot delete all versions of a reference table. To delete a reference table, display the reference table details and click Delete in the lower left corner of the page. Reference tables that are used in list metric definitions cannot be deleted until the metric no longer references the table.