AX1088

Add new dimension elements during an import

To add new dimension elements during an import, create a transform step that uses the built-in function Add new dimension elements. You can use this function to add new accounts, departments, or other dimensions as part of the import.

This function is intended to be used in cases where the import data may contain new dimension elements that have not yet been added to the relevant reference tables in Axiom Software, and the organization wants these records to be added automatically as part of the import. Other organizations may prefer to prevent these records from importing and instead add the new dimension elements manually, in which case this function should not be used.

If new dimension elements are added by this function, then after the import is complete an administrator will most likely need to edit the reference table in order to fill in grouping columns for the new element. This is why the function supports notifying one or more users of the added dimension elements. Keep in mind that it is possible for invalid dimension elements to be added when using this function, if the source data for the import is not correct.

Function parameters

This function uses the following parameters:

Parameter	Description
Table	The name of the dimension table to update. This can only be reference tables that have a single-level lookup relationship with the destination table for the import. For example, if you are importing data into GL2019, and that table has columns Acct and Dept which have lookup relationships with the Acct and Dept tables, then you can add new dimension elements to the Acct and Dept tables. When this transform step is performed, the data in the temp table will be validated against this dimension table. If any dimension elements are found that do not match the dimension table, those new dimension elements will be added to the dimension table.
Column defaults	Optional. Values to use for grouping columns in the target dimension table when new dimension elements are added. You can add as many column=value pairs as needed, separated by commas. See the discussion following this table for more information on the specific syntax. Any columns in the target dimension table that are not listed here will use the column's default value (as defined in the column properties) when the new record is added. If the target dimension table contains any validated columnsColumns that have been assigned a lookup column. The lookup column contains the set of allowed values for the validated column. , then you must do one of the following: List the validated column in this parameter with a valid value. That valid value will be used when the new records are added to the table. OR Make sure the validated column has a valid default value in its column properties. If the validated column is not listed in this parameter, then its default value from the column properties will be used when the new records are added to the table. If the validated column is not assigned a valid value using one of these options, then an error will occur when this transform is processed and the import cannot continue.
Email notification	Users and or roles to notify via email when new dimension elements are added to the table. Enter a list of one or more user and role names, separated by commas.
Task pane notification	Users and or roles to notify via the Notifications task pane when new dimension elements are added to the table. Enter a list of one or more user and role names, separated by commas.

Defining column defaults

You can populate a grouping column using a fixed default value, or by using a value from a column in the import temp table.

• To use a fixed default value, use the following syntax:

  GroupingColumnName='StringValue',GroupingColumnName=NumericValue, etc.

  Where GroupingColumnName is the grouping column in the target dimension table. If the grouping column is a string column, then the value must be placed in single quotation marks, just like when writing a filter statement.

• To use a value from a column in the import temp table, use the following syntax:

  GroupingColumnName={temptable}.TempTableColumnName, etc.

  For example, if the temp table contains a column named Desc that you want to use to populate the Description column in the target dimension table, you would enter: Description={temptable}.Desc

NOTE: If the default values are populated from a column in the import temp table, those values must be the same for all instances of a particular dimension element. If the same dimension element has multiple rows in the temp table with different values in the specified temp table column, then the maximum value will be placed in the grouping column for that dimension element.

Example

To add new departments when importing GL actuals data, the function parameters could look as follows:

When the import is run, any departments that do not already exist in the DEPT table will be added to that table. The Description column in the DEPT table will be populated with the value from the Desc column in the import temp table, and the DeptStatus column in the DEPT table will be populated with the string "New". An email notification of the added departments will be sent to users in the role sysadmins, and a task pane notification of the added departments will display for user jdoe.

All other columns in the DEPT table will use the default value defined for the column in the column properties.