AX1562

Refresh data only when table has been modified (time-stamped Axiom queries)

You can configure an Axiom query so that it will only refresh data if the primary table has been modified since the last execution of the query. If the table has not been modified, then the query will not execute and the existing data in the sheet will be left as is. This feature is known as time-stamped Axiom queries.

This configuration is primarily intended for Axiom queries that bring in supporting data for the file, where this supporting data does not change frequently. Eliminating the automatic execution of these queries when the table data has not changed may improve file performance.

Configuring a query to refresh based on the last table modification

To configure an Axiom query as a time-stamped query, use the setting Refresh only if primary table changed since last refresh. This setting can only be changed on the Control Sheet, in the Refresh behavior section of the Axiom query settings.

By default, this setting is Off, which means that the query will run whenever the query is eligible to refresh (based on the other refresh behavior settings). If you change this setting to On, then the query will only run if the primary table of the query has been modified since the last time the query was refreshed (as indicated in the system-controlled Last refresh time field).

Time-stamped queries must also meet the following requirements:

  • The primary table of the Axiom query must be explicitly defined in the query settings. If the primary table is inferred based on field definition entries, then Axiom Software has no table to compare against and therefore always refreshes.

  • The sheet-wide setting Convert Axiom Query results to zero on save must be disabled for the sheet. If it is enabled, then the Refresh only if primary table changed since last refresh setting will be ignored, and the query will zero and refresh as normal.

How time-stamped queries work

Time-stamped queries use the Last refresh time for the query on the Control Sheet (shown in the previous screenshot). Each time an Axiom query is run, the date and time of the refresh is written to this field. If Refresh only if primary table changed since last refresh is enabled, then before the query is executed, Axiom Software compares the last refresh date-time of the query to the last modified date-time of the primary table:

  • If the primary table has not been modified since the last refresh of the Axiom query, then the query is not refreshed and the existing data in the sheet is left as is.
  • If the primary table has been modified since the last refresh of the Axiom query, then the query is refreshed.

The Last refresh time field is system-controlled and should not be manually modified. The only exception is that you can manually clear the field if you want to "reset" the last refresh time of the query. The date-time of the last refresh is written in UTC (Coordinated Universal Time) to ensure consistent date-time comparisons; the date-time is not converted to the local time of the client where the refresh occurred.

NOTES:  

  • The last modified date-time of the table reflects both data changes and structure changes. The query will also refresh if the structure or properties of the primary table has changed.

  • By default, the Last refresh time field is not populated when refreshing Axiom queries in a template file for a file group. This is because when you create plan files from the template, the field must be blank so that it can be populated for each individual plan file. However, if you are using virtual plan files and therefore you want to maintain a time stamp in the template itself, you can use the Process Template List task for this purpose. This task runs Axiom queries set to Refresh during template processing and then time-stamps them for use in virtual plan files. For more information, see Scheduler task: Process Template List.

The time-stamped query behavior is honored by all Axiom query refresh settings (manual refresh, file open, after save data, etc.). If you enable this feature for a query, and then you later change something about the query—for example, if you add a column to the field definition, or you change a formula in the in-sheet calc method—then you should manually clear the last refresh time of the query so that the query will execute using the new query settings. Otherwise the query will not execute again until the primary table is modified.

Design considerations

Generally speaking, the time-stamped query feature is only a good fit for queries that meet the following criteria:

  • The Axiom query only queries data from a single table, the primary table. Only the last modified date of the primary table is evaluated to determine if the query should be run. If the query includes data from other tables and those other tables have changed but the primary table hasn't, the query would not reflect the latest data from the other tables.

  • The Axiom query settings are static. Query settings do not use formulas to dynamically change based on other factors such as refresh variables. The exception would be an Axiom query in a plan file that uses a formula to filter the query based on the current plan code, because in that case the filter will always be the same for that plan file.

    For example, you would not want to use this feature with a query where the filter changes based on a refresh variable selection, because the query would not refresh to reflect the changed filter.

  • The data in the primary table is mostly static. Modifications to the table data are rare. If the query has to run each time the file is opened because the primary table keeps getting modified, then this configuration does not provide much benefit.

  • The data in the primary table is available to all users who will access the file. The users do not have security filters that restrict access to the table. If the users who access the file have different security filters on the primary table, then the query needs to always run to ensure that the current user's filter is applied to the query.

If you enable this feature for a query that does not meet these criteria, the setting may not have any benefit and/or the query results may not be as expected.

NOTE: This feature is not supported for use with file processing. Specifically, it should not be enabled for any queries that are executed for each pass of file processing. It could be enabled for a query that runs on file open to provide supporting information for the file, but otherwise the query is not executed during file processing.