AX1630

Using table variables for file groups

You can use table variables in file groups to:

  • Identify and reference the tables associated with the file group.
  • Control which tables the file group can save data to.
  • Automatically create new tables as needed when cloning the file group or creating a file group scenario.

Table variable values can be returned in files by using the GetFileGroupVariable function as normal. Using this approach, you can dynamically return the names of the current tables associated with the file group, and use those tables as needed. By using variables to define the tables, you can control the data queries and data saves for the file group from a single location. Any changes made to the table variables will flow through all files in the file group that reference the variables.

All table variable creation and configuration takes place on the Table Variables sub-tab in the file group properties. To access this area:

  1. On the Axiom tab, in the Administration group, click Manage > File Groups.

  2. In the Axiom Explorer dialog, navigate to the file group that you want to edit, then right-click the file group and select Edit.

    TIP: You can also access this dialog by right-clicking a file group in the Explorer task pane.

  3. Select the Variables tab, then select the Table Variables sub-tab.

From this area, you can create new variables, edit existing variables, and delete variables. Only administrators or users with the Administer File Group permission can access this area.

About table variables

Any table that you plan to query data from or save data to—from within any file that belongs to the file group—should be defined as a table variable and then referenced in file group components using the GetFileGroupVariable function. This includes tables such as the following:

  • Data tables that hold GL actuals and other historical data, including planning data from prior years. Plan files query data from these tables in order to build out the starting point for the plan, but they do not save data to these tables.

  • Data tables to store the planning data that is calculated in plan files. Plan files save data to these tables.

  • Reference tables or document reference tables that store the driver data used by the plan files. Driver files save data to these tables.

NOTES:  

  • It is not typically necessary to define query-only reference tables such as DEPT and ACCT as table variables, although you can if desired. Reference tables usually remain constant, and in that case, there is no advantage to using a table variable versus simply referencing the table directly. However, if the file group needs to save data to these tables, then they should be defined as table variables.

  • If you are using picklist tables with a file group, those tables should be defined as picklist variables instead of table variables, so that you can use the additional picklist-specific features. However, if necessary, you can create a table variable that resolves to a picklist table—for example, if you need to save data to the picklist table from the file group.

The reason for using table variables is so that you can dynamically change which tables are used by the file group when cloning the file group or when creating file group scenarios. If instead the table names are "hard-coded" into the template or the drivers, then you may forget to update these names or accidentally overlook a reference to a particular table. This can result in querying data from the wrong tables, or worse, saving data to the wrong tables. Using table variables means there is one place where you manage all of your table names for the file group, and you can update these names as part of the cloning process or scenario creation.

Using table variables also provides the following benefits:

  • When cloning a file group, if a table variable resolves to a table that does not yet exist, the cloning process will create the table for you. For example, if the variable resolved to BGT2019 in the old file group and resolves to BGT2020 in the new file group, the cloning process will create BGT2020 by cloning the structure of BGT2019.

  • When creating a file group scenario, the scenario wizard will automatically create new data tables to hold the data for the scenario. This is accomplished by cloning the target tables for any table variables that have Allow file group to save data to this table enabled. For example, if the original file group saves data to BGT2020, the scenario wizard will automatically create a table such as BGT2020_V1, as long as BGT2020 is set up as a table variable with saves enabled.

  • You can limit the tables where the file group can save by using the optional setting Restrict Saves To Tables Defined Below. If this setting is enabled, then the file group can only save to tables that are defined as table variables with Allow file group to save data to this table enabled.

Creating table variables

You can create new table variables as needed.

To create a table variable:

  1. On the Table Variables sub-tab, click New.

  2. In the Edit Table Variable dialog, complete the following fields:

    Item Description

    Variable Name

    The name of the variable. The name should be brief, yet self-explanatory. For example, PlanData to define the name of the target table to save planning data.

    Variable Value

    The value of the variable. You can type in a value, and/or you can reference another file group variable to define the value.

    To reference another file group variable within the value, enter the variable name in curly brackets. You can also use the Insert variable tool to insert any user-defined general variable within the file group, or to insert a built-in variable such as {FileGroupYear}.

    Alternatively, you can use the table chooser to the right of the box to choose a table and set the variable value to that specific table name.

    For example, if the variable name is PlanTable for the table where planning data will be saved, then the variable value might be BGT{FileGroupYear}. This would resolve to BGT2020 if the file group year is 2020.

    Allow file group to save data to this table

    Specifies whether the file group is intended to save data to the table referenced by this variable. You should clear this option if the file group will only ever query data from this table. By default, this option is selected.

    This setting is used for two purposes:

    • It determines which tables the file group is allowed to save data to. This restriction is only enforced if the setting Restrict saves to tables defined below is also enabled.
    • It determines which tables will be cloned when creating a file group scenario. Only tables where the file group saves data will be cloned.

    Once the variable value has been defined, the Resolved Table Name shows the table name that the variable resolves to. If the resolved table name does not match an existing table, a warning icon is displayed. This may be expected if the target table has not yet been created.

    Example table variable

  3. Click OK to close the Edit Table Variable dialog and add the new variable to the Table Variables grid.
  4. Click Apply or OK in the Edit File Group dialog to save your changes.

Restricting data saves based on table variables

You can limit the tables that the file group is allowed to save data to by using the optional setting Restrict saves to tables defined below. This setting is located at the top of the Table Variables sub-tab, above the variable grid.

If this setting is enabled, then the file group can only save to tables that meet both of the following criteria:

  • A table variable in the file group resolves to the table name.
  • The table variable has Allow file group to save data to this table enabled.

If the target table for the save is not associated with a file group variable, then the save will be prevented. The save will also be prevented if the target table is associated with a file group variable, but Allow file group to save data to this table is not enabled.

This feature is a safety measure that is intended to help prevent accidentally saving to the wrong tables—for example, if someone "hard-coded" a table name into a template instead of referencing a variable. For example, if a template is set up to save to BGT2019 but BGT2019 is not defined as a table variable for the file group, then the save will be prevented.

Managing existing table variables

To edit a table variable, select the variable in the grid and then click Edit. When editing a variable, keep in mind the following:

  • Generally speaking, you should not change a variable name once any file group components have been created that reference the name, such as plan file templates, drivers, or utilities. If you change the name, you will need to manually find and update any GetFileGroupVariable references to the old name in file group components.

  • If you change the variable value so that it resolves to a different table, be sure you understand the impacts of the change within the file group. Changing the variable value may impact processes such as the data being queried into plan files, and the tables being saved to. The ability to modify file group behavior by changing variable values is the purpose of file group variables, but you should take care to ensure you are setting the values as appropriate for the file group.

To delete a table variable, select the variable in the grid and then click Delete. Before deleting a file group variable, you should be absolutely sure that the variable is not needed. Deleting an in-use variable will cause GetFileGroupVariable functions that reference the variable to return an error.

See also