Specifying export field formats

Export field formatting is entered in the Comparison Value section of the Export Utility window. You can specify formatting for constants, iMIS field values, and Omnis calculations. You can also specify if/then processing of the output by formatting the export output with condition statements. This step narrows the general search criteria for specific fields.

To format export fields

  1. In the Advanced Accounting Console tool bar, go to Utilities > Data Transfer Utilities > Export Utility.
  2. Select an Available Export.
  3. Click Edit. There are three types of values that can be exported: a constant, a value contained in an iMIS field, and an Omnis calculation. Enter the formatting in the Value field.
    • To specify the format for a constant (literal) value, type a double-quotation mark followed by the value. For example, if the first four characters of the export record must contain the value 0001, enter "0001 in the Comparison Value field.
    • To specify the format for an iMIS field value, type the field name, for example, Name.MEMBER_TYPE.
    • To specify the format for an Omnis calculation, type an equal sign followed by the calculation. For example, if you want to export the area code of the home telephone number field, type =mid(Name.HOME_PHONE,2,3). This statement exports the second, third, and fourth characters from the Name.HOME_PHONE field.
  4. (Optional) Specify the desired Length of the field. This allows you to truncate or extend the field length.
  5. (Optional) Enter a code in the Format field (see below).
  6. Click Add to save the format.
  7. After all formatting instructions are added, click Save to save the formats.

The following table lists the available Format codes.

Code

Definition
1

Exports the value on the first record only (useful for header records). Must be in the first position of the Format field.
C

Formats the output to initial capital letters.
D6

Exports the value as a date in the MMDDYY format.
D6/

Exports the value as a date in the MM/DD/YY format.
D8

Exports the value as a date in the MMDDYYYY format.
D8/

Exports the value as a date in the MM/DD/YYYY format.
F

Appends a LF (line feed) return to the end of the field.
L

Formats the output to all lowercase letters.
P

Pads the field to the length specified in the Length field with the character immediately following the P.
P0

Pads the field without zeros.
N

Specifies all blank or zero field values to be output as ‘Null’.
N2

Same as N except that the characters. , - $ ( ) are saved.
R

Appends a CR (carriage return) feed at the end of the field.

This code should be used with the last field of each format.
U

Converts the output to all uppercase letters.
S

Skips the delimiter and concatenates with the next export column.
X

Updates data in the iMIS database.

You must include search criteria and a calculation with this format. The search criterion identifies the specific record to be updated. The calculation determines the value that is output in the export record.
6X

Indicates which record will be selected when searching extra records. For example, the code 62 will take the field value from the second record that matches the search criteria.
Y

Outputs a YES or NO. If the field value or calculation in the Value field evaluates True, a YES is exported. Otherwise a NO is exported.
y

Same as Y, except outputs a Y or N.

To format output with condition statements

You can format the export output with condition statements by creating a series of IF statements with corresponding output values.

  1. In the Advanced Accounting Console tool bar, go to Utilities > Data Transfer Utilities > Export Utility.
  2. Select an Available Export.
  3. Click Search. Highlight an export Value.
  4. Click Edit Calculations to display the Export Calculations Utility window.
  5. Click New to create a new calculation.

  6. Click Insert at the bottom of the window and click in the Name field to add a new name for the calculation.
  7. Click Add to add a calculation or condition statement. In the Calculation field, type an equals sign followed by the calculation, for example, =Name.MEMBER_TYPE=‘CM’.
  8. In the Output field, type a double quotation mark followed by the desired output value, for example "CO. In this example, every CM Member Type is exported as a CO value.

    9. You can add as many calculations as you need. The utility exports the result of the last True calculation. The result can be an iMIS field name, a constant, or an Omnis calculation.

  9. Click Save to save the calculation. The calculation is now listed as an Available Calculation on the Export Calculations Utility window.

To load record formats from other compatible software

To load records from compatible third-party software applications, the records must be comma- delimited or tab-delimited. Each record to be loaded must contain the following fields in this exact sequence: Value, Length, Format, Calculation, and Search.

Do the following:

  1. In the Advanced Accounting Console tool bar, go to Utilities > Data Transfer Utilities > Export Utility.
  2. Select an Available Export.
  3. Click Edit.
  4. Click Load to select the file to load into the export definition.
  5. Click Open to start the load process.
  6. Click Save to save.

More:
Export Calculations Utility window