Standard vs. Historical Tables
What is the difference between standard and historical tables?
Purpose: To help you understand when it is best to use a historical table for data entry and a standard table.
Table of Contents
Pre-set tables (such as the historical ones listed below) cannot have their type changed. Any custom table can be set to either historical or standard.
Historical tables
Historical tables structure upon dates and store information in chronological order. The Job Information, Employment Status, and Compensation tables on the Job tab are examples of historical tables.
As shown in the screenshot above, the first line indicates an employee's current employment status with a green dot.
Standard tables
A standard table keeps track of information that applies to the employee at any given time. An example of a standard table is the Assets table pictured in the screenshot above.
Default (Historical tables)
By default, custom reports pull the top and most recent data line in a historical table. Note that the Show History box is unchecked in the screenshot above.
Show History (Historical tables)
By selecting Show History, all lines of the historical table will pull into the report, as shown in the screenshot above.
Standard tables
When reporting on a standard table, all data lines will automatically pull from the table unless a filter applies to the report.
While creating a report, please note that pulling the full history from more than one table into a single report will result in an error. When reporting on more than one table within a single report, you can use the following combinations:
- The top and most recent data line from one table, plus the full history from a second table.
- The top and most recent data line from multiple tables.
You can add a single data field (those not included in a table) to either of the above options as well.
When you use the Submit a Change Request process, data within the submission form will display differently if you are using a standard or historical table.
For standard tables or tables that do not store data historically, the system does not know what is the most current information in that specific table. Therefore, the form will not display any previously entered data.
Historically tracked tables always use the row with the most current date. For this reason, the submission form will display the most current information in that table. The user submitting the change request can then edit the most current information and submit it. When the designated approver sees the request, they will only see what was edited (not the entire submission). This is most common with our standard Job Information, Compensation, and Employment Status change requests which are Custom Approval Templates. That said, it can also apply to custom tables you have that are tracking historically.
When you use the Custom Table Date email alert, it will send information differently if you are using a standard or historical table.
For standard tables or tables that do not store data historically, the alert can be sent based on any date field in the table. Additionally, it will send for that date field if that selected date on ANY row applies.
For historically tracked tables, the email alert can only be sent based on the date field that the table is sorting by. The alert relies on the row with the most current date.
Please note that standard tables, or tables that do not store data historically, cannot be utilized as placeholders within an e-signature document. We use the most recent data line from a historical table for an e-signature document because it consists of an employee's current information. For a standard table (i.e., assets listings, allergies, etc.), the signature document will not recognize which data lines to pull from because all lines of data could be current. You can utilize any historical tables and standard fields in a signature document.