AX2625

About table classifications

Each table in Axiom Software is classified as one of the following:

  • Data table
  • Reference table (including picklist tables and KPI tables)
  • Document Reference table

This topic discusses the properties and general design considerations for each table classification.

Data tables

Data tables hold imported or calculated financial data (or both). Data tables are the primary targets of save-to-database processes in plan files, and are the primary sources of data for reports.

Data tables can have multiple key columns. For example, the key columns for a data table might be DEPT and ACCT. Every combination of these key columns defines a unique row of data in the table. In this case two rows of data in the database cannot share the same account and department code.

Key columns in data tables typically have lookup relationships with reference tables that define the valid values for the key columns. For example, if a key column in the data table is ACCT, that column would have a lookup to the ACCT column in the ACCT reference table to define the set of valid accounts. However, key columns are not required to have a lookup relationship. If a key column does not have an assigned lookup column, then the entries for that column are not validated during an import or a save-to-database.

By default, non-admin users do not have access to the data in data tables. If you want non-admin users to be able to query data in a data table, they must be granted access to the table in Security. Table types may be used to group related tables and manage security access as a group.

Reference tables

Reference tables hold lists of information to be referenced elsewhere. Reference tables can be used for many purposes, from lists of planning codes to import mapping tables.

Reference tables also define grouping relationships for security filters and reporting. For example, a department table might also have columns that assign each department to a region, country, or VP. By defining a filter criteria statement based on a grouping column, you can restrict access to data or define summing levels for a data query.

Reference tables can be designated as the plan code table for a file group. In this case, each code or record in the table has an associated plan file, and the table can contain additional columns to drive file group processes.

Reference tables can have only one key column. Each entry in that key column must be unique.

By default, non-admin users do not have access to the data in reference tables. If you want non-admin users to be able to query data in a reference table, they must be granted access to the table in Security. If the table holds common data that all users need to be able to access, such as ACCT and DEPT tables, you can use the Everyone role to grant access to all users.

Reference tables support two special sub-classes of tables, intended for specific purposes. These special tables generally follow the same rules as reference tables, but with some additional requirements and restrictions.

  • Picklist tables: Picklist tables hold lists of items from which users can make selections. For example, you might want users to select from a list of defined categories or status codes, and these lists can be defined as picklist tables. For more information on using picklist tables, see About picklist tables.

  • KPI tables: KPI tables hold key performance indicator values (KPIs), for display within reports. The KPI Panel component in Axiom forms and web reports can display KPI values held in KPI tables. For more information on using KPI tables, see About KPI tables.

Document reference tables

Document reference tables are single-key tables that are controlled using a source spreadsheet file. Each document reference table is linked to a specific managed Axiom file, where the table and its data are managed. Traditionally, document reference tables are used to maintain file group driver tables, and can also be used for other data if desired.

Save Type 3 is used to create a document reference table in the database, based on the structure of the source sheet. Each time the Axiom file is saved, the associated document reference table is re-created based on the structure and data within the Axiom file. For this reason, document reference tables are not created or edited from the Tables menu (although you can launch the source file from that location, if you have the appropriate rights).

By default, all users have security permission to query the data in document reference tables, via the Everyone role.

Generally speaking, document reference tables are being de-emphasized in favor of using regular reference tables and picklist tables, especially in Axiom Software packaged products. Although document reference tables are still supported, it is recommended to use other solutions when possible. Document reference tables have many limitations as compared to the other table classifications. For example, document reference tables do not support the full range of table and column properties, and cannot belong to table types. Columns in document reference tables cannot look up to other tables, and other tables cannot look up to document reference tables.

See also