AX2068
The field definition determines which database columns will be queried and where that data will be placed on the sheet. The field definition can be multiple rows or columns, to accommodate multiple-row and multiple-column calc methods.
Specifying the location of the field definition
The location of the field definition is defined on the file's Control Sheet. If the query is a standard vertical query, you specify one or more field definition rows. If the query is a horizontal query, you specify one or more field definition columns.
You can use the Sheet Assistant to define these settings, or you can manually edit the Control Sheet. The Sheet Assistant updates dynamically depending on whether the query is vertical or horizontal:
|
Vertical query |
Horizontal query |
In the Control Sheet, you must expand the appropriate section to reach the setting, either Vertical Configuration or Horizontal Configuration.
Vertical configuration
Horizontal configuration
Keep in mind that once you have specified a row or a column location, it does not update automatically if you insert rows or columns into the sheet, or if you move your field definition codes in the sheet. You may want to first design the query on the sheet, figuring out where you want to place items such as headers and subtotals (if needed), and then complete the query settings. If you do change the location in the sheet, you must update the Control Sheet setting.
Creating the field definition on the sheet
To bring data into the target sheet, you must populate the field definition row or column with database column names. When the file is refreshed, data for the specified database column will be inserted or updated into the sheet, within the boundaries of the data range tags (as defined in the insert control column or row).
The following entries can be made in the field definition:
| Valid Entry | Description |
|---|---|
|
Column names from the primary table |
You can query data from the primary table for the query. You can use fully qualified Table.Column syntax, or "column-only" syntax. If the table name is omitted then the primary table is assumed, unless the column is a validated key column, in which case the lookup table is assumed. For example, You can also use column-only syntax for shared non-validated key columns such as Detail, if that column is also used as the sum level for the query. For example: |
|
Column names from a lookup reference table |
You can query data from any reference table that the primary table has a lookup relationship with. If multiple data tables are used in the query, then all of the data tables must have lookup relationships with the reference table. If the column is a lookup column for a validated key column in the primary table, then "column-only" syntax can be used. For all other columns in the reference table, fully qualified Table.Column syntax must be used. If the query pulls data from multiple data tables, then all of the data tables must link to the reference table. For example: NOTE: Reference tables can have multiple levels of lookups, all of which are eligible to be included in the Axiom query. For more information, see Using multiple levels of column lookups. |
|
Column names from a table other than the primary table |
If the primary table is a data table, then you can query data from any other data table that is eligible to be joined to the primary table (based on the sum by level of the query). Fully qualified Table.Column syntax must be used. NOTE: If you are querying data from more than one data table, you should use Table.Column syntax for all data columns in the field definition (or alias names), so that it is clear at a glance which table is being queried for each column. For example: If the primary table is a reference table, then you can query data from any other data or reference tables that directly look up to the primary table. Multiple-level lookups are not supported. |
|
Column alias names |
You can use column alias names instead of literal column names. Enter only the alias name, and it will automatically point to the assigned table and column for the alias. If the alias name is later edited to point to a different table column, the query will pull from the new column the next time the query is refreshed. Note that if an alias has the same name as a literal column in the primary table, the literal column is used instead of the alias. We recommend keeping alias names unique from column names to avoid any ambiguity. For example: |
The entries in the field definition must logically complement the structure of the calc methods used in the Axiom query (either an in-sheet calc method or the sheet's calc method library). For more information, see How calc methods work with field definitions.
The field definition must contain only valid database column names (including aliases), and reserved Axiom Software tags for other worksheet processes. Any other content in the field definition will cause an error when the Axiom query is run.
Any reserved Axiom Software tags placed in the field definition will be ignored by the Axiom query. For example, you can place the tag [ExportToFile] in the field definition row, so that you can use the same row for two purposes (as the field definition row for the Axiom query, and as the control row for an export-to-file process).
NOTES:
-
Field definition entries cannot be placed within the data control column or row (which is usually the same as the insert control column or row).
-
If the primary table is not explicitly specified on the Control Sheet, then Axiom Software will assume the primary table based on the first data column in the field definition. (Reference table columns are ignored, unless all of the columns are from a reference table.) Generally, this is intended for situations where you are using alias names in the field definition, so that the primary table can dynamically change depending on the table that the alias names point to. The data column must be an alias name or a fully qualified Table.Column name to assume the primary table.
Field definition example
In this example, row 3 is the field definition row for a standard vertical Axiom query:
-
Wherever a database column code is placed in the field definition row, data from that column is brought into the data range (the section beginning with
[aq1]). -
Two data tables are included in this Axiom query, GL2017 and Plan2017. Instead of the fully qualified column names in the above example, alias names could have been used, such as CYA_YTD and CYB_YTD.
-
Column-only syntax is used to bring in DEPT and ACCT. Axiom Software intelligently interprets these entries as DEPT.DEPT and ACCT.ACCT, so that they apply to both data tables. To bring in the department and account descriptions, fully qualified syntax must be used (DEPT.Description).
