Open topic with navigation

Kourier Integrator Online Help

Export Formulas

Export formulas can be used to derive values from information stored in the current field or the current record being processed. An export formula can contain an arithmetic, logical, or string expression, a BASIC function, an external subroutine call or almost any other type of BASIC statement. Conceptually, an export formula provides the same functionality as I-type dictionaries without the need to create permanent dictionaries and the freedom to use standard BASIC syntax.

The following table lists the most common system variables used in an export formula:

Variable Name

Description - Usage - Notes

@ID

For U2 to SQL/File or REST Exports, this variable will contain the item-id of the record that is currently being processed.

For ODBC to SQL/File or Flat-file Exports, this variable will always contain an empty string.

@RECORD

For U2 to SQL/File or REST Exports, this variable will contain the contents of the record that is currently being processed.

For ODBC to SQL/File or Flat-file Exports, this variable will contain the contents of the row that is currently being processed. The data from each Source Column is copied into the attribute corresponding to the Line number of the field specified in the export.

FIELD$

For U2 to SQL/File or REST Exports, this variable will contain the contents of the dictionary or the attribute, value or sub-value referenced in the Field/Position column in the export, in internal format (e.g. the variable may contain system delimiters). If a literal value was specified in the Conversion column, this variable will contain that value.

For ODBC to SQL/File or Flat-file Exports, this variable will contain the value referenced by the Source Column, in external format. If a literal value was specified in the Conversion column, this variable will contain that value.

Use this variable to return information to the Kourier export engine.

FILENAME$

For U2 to SQL/File or REST Exports, this read-only variable will contain the primary Export Data File name as defined in the export. For REST exports, if the Export Data File name contains a substitution expression, this variable will contain the fully processed substitution expression (e.g. if the value of a parameter named Location is FLA and the Export Data File field contains the string {%Location_DEALS} this variable will contain the string FLA_DEALS).

For ODBC to SQL/File or Flat-file Exports, this read-only variable will contain the Source Table name specified in the export. If the Source Table name contains a substitution expression, this variable will contain the fully processed substitution expression.

SKIP$

 This variable can be set to indicate that the current record being processed should not be included in the dataset being built by the export.

Cautions

You should avoid using any BASIC statements or functions that will stop the processing of a BASIC subroutine. Examples are STOP or ABORT. Instead, you should return an appropriate value in the FIELD$ variable. 

If you use equated constants to access attributes in a dynamic array (i.e., @RECORD<CUSTOMER.NAME>), you may not be able to use this coding technique in a Kourier formula depending on the configuration of the Kourier website (see ValidateRequest key). By default, .NET will see @RECORD<CUSTOMER.NAME> as an attempt to do "cross site scripting".  You can trick .NET into allowing this coding technique by entering a space character after the < and before equated constant (e.g., @RECORD< CUSTOMER.NAME >).

Examples

ClosedExample 1 - Returning 5-digit Postal Code Data

In this example, the data passed in FIELD$ may contain either a 5-digit (92021) or 9-digit (92021-1457) US postal code or a Canadian postal code (BN9 19R). The following export formula will return the first 5 digits of a US postal code but will return all digits in a Canadian postal code. 

IF LEN(FIELD$) > 5 THEN
  CHK.VAL = FIELD$[1,5]
  IF NUM(CHK.VAL) THEN
    FIELD$ = CHK.VAL
  END
END 
ClosedExample 2 - Returning the Last Value in a MultiValue field

In this example, the data passed in FIELD$ may contain several values (MICHAELP]STEVENH]KATHYI). The following export formula will return the last value (KATHYI). 

NUM.VNOS = DCOUNT(FIELD$,@VM)
FIELD$ = FIELD$<1,NUM.VNOS> 
ClosedExample 3 - Extract the Year from the File being Processed

This example illustrates how to use the FIELD$ and FILENAME$ functions to extract the year from the file the is currently being processed. In the example below, the files names being processed consists of GL, GL-2008 and GL-2007. In Line 002, if the year could not be found (as is the case if the current year's file named GL), then the year will be determined by the current system date. Line 003 returns the year using the standard FIELD$ variable. 

   
   001:  YEAR = FIELD(FILENAME$,'-',2)
   002:  IF YEAR = '' THEN YEAR = OCONV(DATE(),'DY')
   003:  FIELD$ = YEAR 
ClosedExample 4 - Creating a Loop when Dealing with MultiValue Data

This example illustrates how to create a simple loop when dealing with multi-value data. The FIELD$ variable will contain all system delimiters and it is up to the formula to loop through each value taking the appropriate action while maintaining the structure in FIELD$. In this example, the desired action is to make any non-numeric data values null while leaving all numeric data values alone. 

   
   001:   NUM.VNOS = DCOUNT(FIELD$,@VM)
   002:  FOR I = 1 TO NUM.VNOS
   003:    DATA.VALUE = FIELD$<1,I>
   004:    IF NOT(NUM(DATA.VALUE)) THEN FIELD$<1,I>=""
   005:  NEXT I