AX2085

Using an Axiom query to return system information

You can use an Axiom query to return system information within an Axiom file, such as:

  • Table metadata (tables, columns, calculated fields, aliases, hierarchies, table types)
  • Security information (users, roles, permissions)
  • File group information (file groups, variables, plan file attachments, aliases)
  • Process information (process events, process tasks, process steps)
  • Workflow information (workflows, workflow instances, workflow tasks, workflow comments)
  • Other system information (configuration settings, alert notifications, file access, etc.)

This information is held in various system tables. A system table is a table maintained by Axiom Software, as opposed to the client-defined tables defined in the Table Library. System tables are named using the convention Axiom.TableName—for example, Axiom.Aliases is the system table that holds information on column aliases.

For more information on which system tables can be queried, including their table and column names, see System tables.

Requirements and limitations

To query system information using an Axiom query, you use the appropriate Axiom.TableName as the Primary Table, and then use columns from that table in the field definition. For example, to query alias properties, the primary table is Axiom.Aliases, and the "column name" to return alias names is Alias.

NOTE: Column names from system tables should never be fully qualified. For example, always use just the column name of Alias, not Axiom.Aliases.Alias.

The Axiom query is otherwise set up as normal. Note the following considerations and limitations:

  • Invalid settings: The following Axiom query settings do not apply when querying system information:

    • Sum by: The "sum by" level should be left blank when the primary table is a system table, because the sum level is inherent to the table and cannot be changed. There is one exception—you can change the sum by when querying the Axiom.ProductColumns table.

    • Column filters: Column filters cannot be used on field definition entries when the primary table is a system table.

    • Batch number: Batch processing cannot be used on a query when the primary table is a system table.

    Other advanced settings are not supported with system tables, such as limit queries or "top n".

  • Filtering results: You can use the Axiom query's main Data Filter and/or data range filters to filter the data returned by the query. To define a filter, use the column names for the information category that you are currently querying. For example, if you are querying Axiom.Aliases and you want to see only the aliases defined for table GL2019, you could use the following filter in either location: TableName='GL2019'

    NOTE: If the column that you want to filter returns True/False, then in the filter criteria statement you can use either the text values True and False, or the numeric equivalent (1=True, 0=False). For example, IsAdmin='True' and IsAdmin=1 will both work when querying Axiom.Principals.

  • Preview Axiom query data: When previewing Axiom query data for a system table, depending on the table being queried the preview may show all columns in the table, or only the columns in the field definition.

  • Single table only: The Axiom query can only use fields from the system table designated as the primary table. It is not possible to join two system tables, or to join a system table with a client-defined table.