Adding Import Expressions

FDMEE provides a set of powerful import expressions that enable it to read and parse virtually any trial balance file into the FDMEE database. Advanced expressions are entered into the Expression column of the field grid of the Import Formats screen. Import expressions operate on the value that is read from the import file.

Also see Stacking Import Expressions and Processing Order.

  To add an import format expression:

  1. On the Setup tab, under Integration Setup, select Import Format.

  2. In Import Format, from the Import Format Mapping grid, select the file-based source column.

  3. In Expression, specify the import expression.

  4. Optional: You can also specify the expression type and value on the Add Expression field.

    1. Click Image shows Edit button.

    2. In Add Expression, under Expression Type, select the expression type.

      The number and types of expressions available depend on the field that is being modified (for example, Account or Account Description).

    3. In Expression Value, enter the value to accompany the expression and click OK.

  5. In Import Format Mapping, click OK.

Import Expression Types

Expressions supported by FDMEE:

Nonstandard Numeric Sign Conventions

The Sign expression is used to manage nonstandard numeric sign conventions. FDMEE interprets numbers with leading and trailing minus signs and numbers within parentheses as negative numbers. You can also use other leading and trailing characters to indicate negative numbers. To define custom signs, use expressions that follow this form: Sign=[Positive String],[Negative String].

For example, if positive numbers are followed by DR (1,000.00DR), and negative numbers are followed by CR (1,000.00CR), the expression is Sign=DR,CR.

Numbers within <> are also treated as negative. For example, if you specify (100.00) and <100.00> both are treated as negative numbers.

If positive numbers are unsigned (1,000.00), and negative numbers are followed by CR (1,000.00CR), the expression is Sign=,CR.

Displaying Debit and Credit Columns

The DRCRSplit expression is used to parse split numeric columns. By default, FDMEE assumes that numeric values in Amount fields are debits. However, you can position debit values on the left and credit values on the right.

Table 13.  DRCRSplit expression example

Account NumberDescriptionDebit Credit
1000-000-00Cash-Operating Account68,603.91 
1010-000-00Cash-FANB-AP 177,216.16

DRCRSplit, which enables FDMEE to interpret left-right positioning and to assign the correct sign, follows the format DRCRSplit=Mid Point of the DR and CR columns.

When the file is imported, credit amounts are assigned negative signs (and thus are interpreted as positive), and debit amounts are unchanged (and thus are interpreted as negative).

Converting from European to U.S. Notation

The Fill=EuroToUS expression is used with the Amount field to trigger a number format conversion from (.,) to (,.) format.

Padding Fields with Leading Fills

The FillL=LeadingFill expression is used to fill fields with leading characters. Text values that are shorter than the specified fill expression are padded, as directed by the fill expression.

Padding Fields with Trailing Fills

The Fill=TrailingFill expression is used to fill fields with trailing characters. Text values that are shorter than the specified fill expression are padded, as directed by the fill expression. For example, if the account number is 103950- and the expression Fill=000000000 is used, the account number after import is 103950-000. Another example is an account number of 243150 with the expression Fill=111111111. The account number after import is 243150111.

Multiplying by Whole-Number and Decimal Factors

The Factor=Value expression is used to factor source-file amounts by user-defined values.

Amounts can be multiplied by whole numbers or decimals; thus, for example, amounts can be doubled or halved.

Disabling Zero Suppression

The NZP expression is used to disable zero suppression during the data-load process. By default, FDMEE bypasses accounts in the trial balance that have zero balances. In certain circumstances, you may want to load all accounts, to ensure that values that should be zero are replaced. You enter NZP in the Expression column of the Amount field to disable zero suppression.

Stacking Import Expressions

One field can use multiple import expressions. To stack expressions, separate the expressions with a semicolon. Consider the order in which the layered expressions are processed.

For example, to stack the import expression for the General Ledger Amount column, enter the expression: Script=ParseAcct.py;Fill=0000000.

Defining a Multiple Period Data Load in Import Formats

When you define the mapping for an Amount column, you specify the number of amount columns in the Expression field. To do this, you specify the Columns=start,end values. For example when column 11 through 22 are amount columns, then specify Column=11,22.

Note:

The Expression field ignores the Field Number value when you provide a column expression for a multiperiod data load.

Note:

Amounts have to be in contiguous columns in the data file.

Processing Order

For all fields except the Amount field,FDMEE processes stacked expressions in the following order:

  1. Script

  2. Fill or FillL

For the Amount field, FDMEE processes stacked expressions in the following order:

  1. DRCRSplit

  2. Fill=EuroToUS

  3. Script

  4. Sign

  5. Scale

  6. NZP