AX1375
When the refresh behavior of an Axiom query is set to Update, Axiom Software references the data control codes in the insert control column to determine the data to update in each row.
In the following example, the sum by for the query is Acct.Category and the insert control column is column A. When the query is refreshed, Axiom Software checks column A for valid Acct.Category codes and then updates the row with the appropriate data for that code.
When data is inserted by an Axiom query (using either Rebuild or Insert), the query automatically adds the appropriate codes to this column. You could then change the behavior of the query to Update and the codes will already be there for subsequent updates.
However, in some cases you want to design an Axiom query to use Update refresh behavior in files where the codes are not already placed in the sheet. This means that you must manually add the codes to the appropriate column. For example:
-
Some plan files are designed so that updated data is periodically brought into the files by running an Axiom query. If users are allowed to add rows to these files by inserting calc methods, then the calc methods must be designed to place their relevant key codes in the insert control column, or else those rows will not be updated when the Axiom query is run.
- In a report, you may want to create a "static" income statement by manually setting up each row, and then using an update-only Axiom query to bring in the latest data. In this case you must manually place the key codes in the insert control column to use for the update.
The data control codes placed in the sheet must correspond to the "sum by" level for the query. If multiple columns are used for the sum by, then multiple codes are separated with semicolons. The codes must be placed in the same order that the columns are listed in the "sum by" field.
For example, if the sum by level for the query is ACCT.ACCT;DEPT.DEPT, then the data control codes for the query would be similar to the following:
10000;100
Where 10000 is the account represented on the row, and 100 is the department.
NOTE: If the field definition spans multiple rows or columns, then the codes should be placed on each row or column that corresponds to the field definition. For example, if the field definition is three rows, then the insert control column should have three sequential rows for each key code.
Treatment of duplicate codes within a data range
If a data range contains multiple records with the same data control code, then Axiom Software will treat those duplicate codes as follows:
-
If the duplicate codes are not contiguous (meaning there is a different code between the duplicate codes), then Axiom Software will update each duplicate record with the appropriate data for that code.
-
If the duplicate codes are contiguous, then only the first record in the contiguous block will be updated.
In this context, "record" refers to each distinct data record that corresponds with the query's field definition. If the field definition is a single row, then each row is a record and each row will be evaluated individually. If the field definition is multiple rows, then each distinct multi-row block is treated as a record. For example, if the field definition is two rows, then each record consists of two rows with the same code—those rows are not considered to be contiguous in this case because both rows belong to the same record. However, if those two rows are followed by another two-row record with the same code, then the second record is contiguous and will not be updated.
If duplicate codes are separated by a blank row (a row without a code), then by default Axiom Software will treat that row as a separator and will update each duplicate record. However, if you want Axiom Software to ignore blank rows and instead treat the records as contiguous, then a system configuration setting is available to change the behavior system-wide (AllowBlanksToBeDataRowSeparatorsInAQDataUpdate).
AX1375
