Show/Hide All

A calculation expression can be specified for numeric, string, or date fields to perform calculations based on values stored in other fields. The results of the calculation are used to replace the value for the specified data item.

A calculation expression can be entered for any data item for which Calculated is selected in the Operation field in the Import Field Properties window (accessed from the Import Job Maintenance Data tab).

A calculation expression consists of valid data items, dates, literal text, or numeric constants separated by operators. You can use actual data item names enclosed by curly brackets { }. Data item names consist of a file name followed by a period (.), followed by a field name (for example, {AP_Vendor.AverageDaysToPay}).

The following examples illustrate the use of calculation expressions.

 Example 1

The source file to be imported has aging data for the AR_Customer table, but there is no Credit Limit information. To set a $1000 credit limit for those customers that have aging data in Aging Categories 3 and 4, use the following calculation expression for CreditLimit:

1000*(AR_Customer.AgingCategory3+AR_Customer.AgingCategory4<>0)

The expression in parentheses evaluates to 1 or 0 (true or false).

 

 Example 2

The source file contains names and addresses for the AR_CustomerContacts table, but the source file does not contain a Contact Code. To use the Customer Code plus the first 3 letters of the contact name, if the contact name is in field number 4 of the source record, use the following calculation expression for ContactCode:

PAD(AR_CustomerContacts.CustomerNo$,7)+PAD(IMP$[4],3)

Note  The following expression would not work:

PAD(AR_CustomerContacts.CustomerNo$,7)+PAD(AR_CustomerContacts.ContactName$,3)

Using this expression, ContactName$ will always be empty, because ContactCode$ is part of the key, and key elements are always assigned before non-key elements, such as ContactName$, no matter where they are located in the job definition. As shown in the correct expression, fields from the source file are first assigned to a string array called IMP$[all], where "all" represents a series of integers from 1 to 999 (or as many fields as there are in a source record).

 

 Example 3

The source file contains data that will be imported into the AR_FinanceCharge table. The source file contains the past due amount, but the FinanceChargeApplied must be calculated using the ServiceChargeRate in the AR_Cus tomer table. First, the ServiceChargeRate must be assigned to a Temporary variable. Then, the calculation can be performed.

Data Item	Expression	Operation
Temp001	AR_Customer.ServiceChargeRate	File Assign
FinanceChargeApplied	PRC(PastDueAmt*Temp001/100,2)	Calculation

The PRC() function rounds the calculation to 2 decimal places. Temp001 must precede FinceChargeApplied in the job definition, so that it has a value at the time FinceChargeApplied is assigned.

 

 Example 4

The source file contains data that will be imported into the AR_InvoiceHeader and AR_InvoiceDetail tables. The source file contains an Invoice Date, but the Due Date needs to be calculated based on the TermsCode. The Day s Before Due must be obtained from the TermsCode table, and then the Invoice Due Date can be calculated. The I nvoice Date will have a YYYYMMDD format in the record.

Data Item	Expression	Operation
Temp001	AR_TermsCode.DaysBeforeDue	File Assign
Temp002	NUM(InvoiceDate$(7,2))+Temp001	Calculation (Day+Days)
InvoiceDueDate$	InvoiceDate$(5,2) +STR(Temp002:"00") +InvoicDate$(1,4)	Calculation MMDDYYYY assumed

Note The above example makes several simplifying assumptions, but is intended to show how calculations are done.

 

 Example 5

It is permissible for a calculation expression to refer to itself. This feature is especially useful when counting the number of records imported or when displaying running balances.

Data Item	Expression	Operation
Temp002	Temp002 + 1	Increment count by one
Temp003	Temp003 + AR_InvoiceHeader.InvoiceBalance	Print a running balance