AX2441

Using variables in imports

Imports can use variables, so that certain import settings can change dynamically depending on the variable value. You can use two different types of variables in imports:

  • Import variables: Import variables are defined on the Variables tab of the Import Wizard. Import variables can be used throughout the import settings (though not in all settings—see the documentation for each setting to see if variables are supported there). When the import is executed manually, the user is prompted to define values for the variables. If the import is run using Scheduler, the Scheduler job must define values for the variables.

  • Transform variables: Transform variables are defined on the Transforms tab of the Import Wizard. Transform variables can only be used in transform statements, and as destination columns. Transform variables are associated with a specific SQL statement that results in a single value.

The values for import variables are defined at the start of the import, before any other import steps are processed. Therefore import variables are a good fit for actions such as:

  • Selecting the appropriate source file based on user input.
  • Selecting the appropriate destination table based on user input.

On the other hand, values for transform variables can only be determined as a result of a SQL statement, and are defined near the end of the import, after the temp table has been created. Transform variables are a good fit for situations where actions need to be driven dynamically based on the contents of the imported data, not by a user selection.

Variable syntax

To use a variable in the import, enter the variable name into one of the supported areas of the import settings, enclosed in curly brackets {}. For example, if the variable name is "mycolumn", you would enter {mycolumn}.

NOTE: If the variable defines the destination table, then you must place the variable in double curly brackets when you use it in a SQL statement, so that the eventual table name value is enclosed in curly brackets as expected. For example, if you have a variable named "destinationtable", you would reference that variable as {{destinationtable}}. That way, when the {destinationtable} value is defined, it will resolve as {GL2019}.

System variables

In addition to the user-defined variables, you can reference system variables in imports. The following variables are supported:

System Variable Description Can Be Used In

{CurrentPeriod}

 The current period as defined for the destination table (if not set, then this is the system current period). All import locations that support variables, except the destination table.

{CurrentUserDomain}

The domain name of the user running the import. Returns blank for users who do not have a defined domain.

 All import locations that support variables.

{CurrentUserEmail}

 The email address of the user running the import. All import locations that support variables.

{CurrentUserLogin}

The login name of the user running the import.

All import locations that support variables.

{DefaultRemoteDataConnection}

The name of the default remote data connection for your system. If your system has multiple defined connections, then the default is determined alphabetically among the connections that are not enabled for authentication. If all of the connections are enabled for authentication, then the default is simply determined alphabetically.

In the Remote Data Connection field on the Source tab, or as an import variable choice.

{SourceFileName}

The name of the source file for the import.

Import transformation steps only.

{SystemCurrentYear}

The current year as defined for the system.

All import locations that support variables.

{SystemCurrentPeriod}

 The system current period. All import locations that support variables.

{TempTable}

The temporary table where imported data is placed before saving to the destination table.

 All import locations that support variables.

{TableName}

Any user-defined table created in the Axiom Software system. For example, {ACCT}, {DEPT}, {GL2019}, {BGT2019}.

 All import locations that support variables.
See also