Formula Theory
Formulas are used throughout the EDI system. The list below indicates
where formulas are presently being used:
1) EDI Segment Maintenance and EDI Segment Override Maintenance use
formulas to allow you to specify what value goes into a segment
when EDI data is generated, as well as allowing to manipulate incoming
data before it is assigned to a database field.
2) The EDI Function Keyword Maintenance function allows you to enter
formulas which can then be used in other formulas in the EDI system
when the EDI function keyword is specified in a formula. Use EDI
function keywords to simplify formula calculations, and reduce the
work necessary when a particular calculation has to be changed.
Formulas can be up to 240 characters in length (i.e., 4 lines of
60 characters each), and can be made up of PROGRESS functions, EDI
keywords, EDI function keywords and constants. Constants refer to any
literal numeric, alphanumeric (i.e., character), date, or logical value
entered in a formula.
You can use operators such as +, -, *, and / for addition, subtraction,
multiplication and division respectively in a formula. Parentheses
(i.e., "(" and ")") can also be used to group parts of a formula together.
To use an EDI keyword in a formula, enter the EDI keyword between two
vertical bars (e.g. |INVNUMB|). The two vertical bars are also known as
EDI brackets. The EDI keyword must have been created using the EDI Keyword
Maintenance function prior to its use in a formula. Please refer to the
documentation for the EDI Keyword Maintenance function for more information
on how to set up EDI keywords.
If you use a EDI keyword in a formula that refers to a field that has
multiple extents (i.e., is an array), you must specify which array element
to use. To do this, enter the array element to use between left ([) and
right (]) square brackets (e.g., |INVSHADD|[2]).
When entering a formula used to generate EDI data (i.e., an outgoing
formula), the following prefixes can be entered before EDI keywords.
- To insert the name of the file referring to an EDI keyword, enter
an "F:" before the EDI keyword, (e.g., |F:INVNUMB|).
- To perform a price or quantity conversion on an EDI keyword,
enter a "P:" (e.g., |P:INVGRPRC|) or "Q:" (e.g., |Q:INVQTYSH|) before
the EDI keyword respectively. Price and/or quantity conversions can
only be done on database fields that store numeric values.
Formulas that are used to generate EDI data can access calculated
values (i.e., values that are not stored in the database). To use a
calculated value in a formula, enter the name of the variable to use
between EDI brackets, preceded by the prefixes described in the table
below:
Data Type Prefix Example
--------- ------ -------
Character VC: |VC:CHAR-VAR|
Integer/Decimal VN: |VN:NUM-VAR|
Date VD: |VD:DATE-VAR|
Logical VL: |VL:LOGIC-VAR|
Price and quantity conversions can be done on numeric variables by
using the "PVN:" (e.g. |PVN:NUM-VAR|) and "QVN:" (e.g. |QVN:NUM-VAR|)
prefixes respectively.
Please note that values assigned to a variable must be done in the program
which is controlling the generation of EDI data. If it is not set up, then
the variable will be undefined.
To use an EDI function keyword in a formula, enter the EDI function keyword
between EDI brackets (e.g., |CALCNETP|). The EDI function keyword must have
been created using the EDI Function Keyword Maintenance function prior to
its use in a formula. Please refer to the documentation for the EDI
Function Keyword Maintenance function for more information on how to set up
EDI function keywords.
There are also special keywords that can be used when entering formulas
to generate and receive EDI data. To use these special keywords in a
formula, enter them between EDI brackets. The table below describes
the special keywords available, where they can be used, and what they
are used for:
Special Keyword Where Used Used for
--------------- ---------- --------
CURRELEM Incoming formulas To use the current element
being processed in an
incoming formula.
ELEMENT Incoming formulas To use a data element in
the segment line being
processed in an incoming
formula. To specify which
data element to use, you
would enter element number
between left ([) and right
(]) square brackets (e.g,
|ELEMENT|[2]). Please note
that the segment name is
always the first data element
on a segment line.
LEVEL Outgoing formulas To use specific values
depending at which level
a segment sequence number
is processed.
Please note that if you have created an EDI keyword and an EDI function
keyword with the same name, the system will interpret the keyword
between EDI brackets as an EDI function keyword. Also note that special
keywords have precedence over both EDI keywords and EDI function keywords.
You can also use PROGRESS functions such as IF THEN ELSE, MAXIMUM, MINIMUM,
TRUNCATE, and ROUND in your formulas.
When using the IF THEN ELSE function in your formulas, the following
operators can be used to evaluate expressions:
Note: CUSTCREL and INVGRAMT are EDI keywords which have been defined as
follows:
Keyword File Name Field Name
------- --------- ----------
CUSTCREL customer credit-limit
INVGRAMT invoice gross-add-amt
Operator Meaning Example
-------- ------- -------
AND Both expressions must be true |CUSTCREL| <= 10000 AND
|INVGRAMT| < 1000
OR Either expression can be true |CUSTCREL| <= 10000 OR
|INVGRAMT| < 1000
NOT Returns the opposite of the NOT (|CUSTCREL| <= 10000)
value of the expression
(i.e., if the expression is
false then it is true and vice
versa)
= or EQ True if the first expression |CUSTCREL| = 10000
equals the second one
> or GT True if the first expression is |CUSTCREL| > 10000
greater than the second one.
>= or GE True if the first expression is |CUSTCREL| >= 10000
greater than or equal to the
second one.
< or LT True if the first expression is |CUSTCREL| < 10000
less than the second one.
<= or LE True if the first expression is |CUSTCREL| <= 10000
less than or equal to the second
one.
<> or NE True if the first expression is |CUSTCREL| <> 10000
not equal to the second one.
Please also note that you must use the ELSE statement when the
IF THEN ELSE function is used. For example, the following formula
is invalid:
IF |CUSTCREL| >= 10000 THEN |INVGRAMT| * .02
The formula should be entered as follows:
IF |CUSTCREL| >= 10000 THEN |INVGRAMT| * .02 ELSE 0
In this case, if the customer's credit limit is less than 10,000 dollars,
then a value of 0 will be used for the EDI data generated.
The formula examples section below will briefly show you how to use
PROGRESS functions in formulas. However, for more details, please
consult the PROGRESS Language Reference, and PROGRESS Programming
Handbook for more information.
Formula Examples
----------------
The following are examples of formulas which can be entered:
- To "hard-code" your address in an outgoing segment, the formula
would be:
"123 Anywhere Street"
- To put the contents of invoice line's item number into a transaction
set, the formula would be:
|INVLNITM|
where INVLNITM is an EDI keyword set up as follows:
Keyword File Name Field Name
------- --------- ----------
INVLNITM invoice-line item-shipped
- To calculate 2 percent of an invoice's gross amount for discount
purposes, the formula would be:
|INVGRAMT| * .02
where INVGRAMT is an EDI keyword set up as follows:
Keyword File Name Field Name
------- --------- ----------
INVGRAMT invoice gross-add-amt
- If the safety stock for an item is 1.25 times quantity on hand,
then the formula would be:
1.25 * |ITMWHQOH|
where ITMWHQOH is an EDI keyword set up as follows:
Keyword File Name Field Name
------- --------- ----------
ITMWHQOH item-whs-d qty-on-hand
- If you provide a discount of 4 percent of an invoice's gross amount,
up to 100 dollars, the formula would be:
MINIMUM(|INVGRAMT|,100) * .04
where INVGRAMT is an EDI keyword set up as follows:
Keyword File Name Field Name
------- --------- ----------
INVGRAMT invoice gross-add-amt
- To calculate a discount amount of 2 percent if an invoice's gross amount
is greater than 100 dollars, the formula would be:
IF |INVGRAMT| > 100 THEN |INVGRAMT| * .02 ELSE 0
- To calculate a discount of 3 percent if an invoice's due date is within
30 days of when it is invoiced, the formula would be:
IF |INVDATE| - |INVDDATE| > 30 THEN |INVGRAMT| * .03 ELSE 0
where the following keywords have been defined using the EDI Keyword
Maintenance function:
Keyword File Name Field Name
------- --------- ----------
INVDATE invoice invoice-date
INVDDATE invoice due-date
INVGRAMT invoice gross-add-amt
- To enter an additional charge amount with 2 implied decimals, the formula
would be:
ROUND(|INVACAMT| * 100,0)
where INVACAMT is an EDI keyword set up as follows:
Keyword File Name Field Name
------- --------- ----------
INVACAMT invoice-add amount
Rounding the number to two decimals ensures that if the amount is stored
with 3 or more decimals, that they are not used.
- To use the first 4 characters of a customer's number as a store number,
the formula would be:
SUBSTRING(|INVCUSTN|,1,4)
where INVCUSTN is an EDI keyword set up as follows:
Keyword File Name Field Name
------- --------- ----------
INVCUSTN invoice cust-no
- To put the prefix "English Description:" in front of the first
description line for an invoice line, the formula would be:
"English Description: " + |INVLNDES|[1]
where INVLNDES is an EDI keyword set up as follows:
Keyword File Name Field Name
------- --------- ----------
INVLNDES invoice-line description
- To give a credit of a 2 percent if the extended price for an invoice
line is greater than 100 dollars, the following formula would be:
IF |INVLEXTP| > 100 THEN .02 * |INVLEXTP| ELSE 0
INVLEXTP would be an EDI function keyword that would calculate the
invoice line's extended price. The formula associated with the
INVLEXTP function keyword would be as follows:
|INVLNQTY| * |INVLNGRP| / |UOMUNITS|
where the following keywords have been defined using the EDI Keyword
Maintenance function:
Keyword File Name Field Name
------- --------- ----------
INVLNQTY invoice-line qty-shipped
INVLNGRP invoice-line gross-price
UOMUNITS uom unit
- There may be times when you will have to use the same segment sequence
number/line number/position combination for different purposes. For
example, to use the invoice's job number when generating invoice header
information, and the invoice line's job number when generating invoice
line information, the formula would be:
IF |LEVEL| = "1" THEN |INVJOBNO| ELSE |INVLJOBN|
where the following keywords have been defined using the EDI Keyword
Maintenance function:
Keyword File Name Field Name
------- --------- ----------
INVJOBNO invoice job-no
INVLJOBN invoice-line job-no
- The total weight for all the items in an invoice is not stored on file.
To use the total weight for an invoice, the following formula would
have to be used:
|VN:TOTAL-WEIGHT|
where TOTAL-WEIGHT would be a variable defined in the program used to
generate invoices.
- To specify an invoice's total weight to a whole number, excluding
decimals, the formula would be:
TRUNCATE(|VN:TOTAL-WEIGHT|,0)
- To charge an additional 2 percent if an invoice's total weight exceeds
100 pounds, the formula would be:
IF |VN:TOTAL-WEIGHT| > 100 THEN .02 * |INVGRAMT| ELSE 0
where TOTAL-WEIGHT would be a variable defined in the program used to
generate invoices, and INVGRAMT is an EDI keyword referring to the
invoice's gross amount.
- To use the last 10 characters of a user-defined hash total, the formula
would be:
SUBSTRING(|VC:HASH-TOTAL|,LENGTH(|VC:HASH-TOTAL|) - 9,10)
where HASH-TOTAL would be a variable defined in the program used to
generate invoices.
- To use the invoice's date when generating information at the invoice
header level, and use a calculated date when generating information
at the invoice line level, the formula would be:
IF |LEVEL| EQ "1" THEN |INVDATE| ELSE |VD:CALC-DATE|
where INVDATE is an EDI keyword referring to the invoice's date,
and CALC-DATE is a date variable defined in the program used to
generate invoices.
- To add the prefix "WALM" to an incoming value, the formula would
be:
"WALM" + |CURRELEM|
- To use the first 3 characters of the current element when processing
the incoming value, the formula would be:
SUBSTRING(|CURRELEM|,1,3)
- To convert the value of the current element into a decimal, the
formula would be:
DECIMAL(|CURRELEM|)
- To add the value of element 6 in a segment line, and the order's
job number to the data element being processed, the formula would be:
|ELEMENT|[6] + |ORDJOBNO| + |CURRELEM|
where |ORDJOBNO| is keyword referring the order's job number. Remember
that the segment name is always the first element in a segment line.
Validation
----------
After you enter a formula, the system removes all unnecessary spaces
from the formula, and places spaces around the +, -, *, and / operators.
The system will also check to see if EDI keywords, EDI function keywords,
calculated values, and special keywords are properly enclosed within EDI
brackets.
It will then check the formula to see if it is "mathematically correct".
If there is an error in the formula, you will receive an error message.
After the system has checked that a formula is "mathematically correct",
it will check the syntax of the formula by generating a PROGRESS procedure
for the formula. If there is an error in your formula, you will receive
an error message. Please see the error messages below for reasons why
your formula may be in error.
Notes
-----
- When entering numbers, do not use any character as a thousands separator
(e.g., 1,000,000), and use the period (.) as a decimal point
(e.g., 12.56).
- When entering alphanumeric values, make sure that they are contained
within single or double quotes (e.g., "ABC").
- Logical values should be entered as either yes, true, no or false, with
no quotes surrounding the value (e.g., yes).
- Since the "/" character is used as a division symbol, you must use the
PROGRESS date function to enter a literal date. For example, the
following formula segment checks if the employee's birth date is
greater than September 24, 1964:
IF |BIRTHDTE| > DATE(9,24,1964) THEN ... ELSE ...
The PROGRESS date function uses the following syntax:
DATE(MONTH,DAY,YEAR)
The year should be entered as a 4 digit year.
- If your formula includes very large integer constants (i.e., over
1000000000), you should enter them as a decimal (e.g., 9999999999.0).
- You do not have to use an "if" statement if the formula returns a
logical value. For example, the two formulas below are equivalent:
|INVLEXTP| > 30000
and
IF |INVLEXTP| > 30000 THEN YES ELSE NO
- The system does not check whether or not the array elements specified
are within the limits of the field associated with an EDI keyword, or
the ELEMENT special keyword when the formula is entered. This is only
done when the formula is calculated (i.e., During the Generate EDI
Formulas, Generate EDI Data, and Receive EDI Data functions).
- The system does not check to see if a formula has a circular reference,
(i.e., An EDI function keyword, which by referring to other EDI function
keywords in its function formula, eventually refers back to itself)
when the formula is entered. Circular references are only checked when
the formula is calculated (i.e., During the Generate EDI Formulas,
Generate EDI Data, and Receive EDI Data functions).
- The system does not check to see if the variable names entered are
valid PROGRESS variable names. This is only done when the formula is
calculated (i.e., During the Generate EDI Formulas, and Generate EDI
data functions).
- The PROGRESS procedure used to verify the syntax of the formulas you
enter is stored in the directory from which your ran your MXP software.
In order to determine the current working directory, the system uses
the following programs, depending on the operating system used to run
the MXP EDI system:
Operating System Program Comments
---------------- ------- --------
UNIX pwd This program is usually
located in the /bin
or /usr/bin directory.
Please make sure that
the directory where
the "pwd" command is
located is included
in your PATH statement.
MS-DOS and OS/2 cd The "cd" command returns
the name of the current
directory. Make sure
that your COMSPEC
enviromental variable is
set up properly, and
that you have enough
memory to exit to the
operating system.
VMS show default If you set the default
directory to an invalid
directory before running
the MXP software, the
system will not be able
to determine the correct
directory, and as a
result, will not be able
to generate the PROGRESS
procedure necessary to
verify the formula's
syntax.
FORMULA ERROR/WARNING MESSAGES
------------------------------
ERROR: Length of expanded formula is greater than <nnn> characters.
-------------------------------------------------------------------
Reason: The length of the formula after being fully expanded is more
than <nnn> characters.
Action: Make sure that your formula, after being fully expanded, is
less than <nnn> characters. Try reducing the complexity of your
formula, if possible.
ERROR: There is no leading opening '(' parenthesis.
---------------------------------------------------
Reason: You have entered a closing parenthesis in your formula, but there
is no opening parenthesis before it. As a result, the formula
cannot be mathematically correct.
Action: Make sure that there is at least one opening parenthesis before
the first closing parenthesis in your formula.
ERROR: No closing EDI bracket after opening EDI bracket at position
<position>
-------------------------------------------------------------------
Reason: You are missing a closing EDI bracket.
Action: Make sure that there is a closing EDI bracket for each opening
EDI bracket.
ERROR: Nothing is specified between EDI brackets.
-------------------------------------------------
Reason: You have not entered anything between EDI brackets.
Action: Make sure that you have entered something between EDI brackets.
ERROR: You cannot use a function keyword in its own function keyword
formula.
--------------------------------------------------------------------
Reason: Your function formula contains a reference to the same EDI
function keyword as you are editing. For example, if you are
editing EDI function keyword |INVLEXTP|, the following function
formula would be invalid: .01 * |INVLEXTP|.
Action: Make sure that the function formula does not contain a reference
to the EDI function keyword you are editing.
ERROR: Prefixes cannot be used with function keywords.
------------------------------------------------------
Reason: You have used the "F:", "P:", "Q:", or other valid prefix with an
EDI function keyword. Since an EDI function keyword refers to
another formula, there is no use in associating a prefix with an
EDI function keyword.
Action: Make sure that there are no prefixes before EDI function keywords
in your formulas.
ERROR: Only keywords that return numeric values can use the P: and Q:
prefixes.
---------------------------------------------------------------------
Reason: You have entered a "P:" or "Q:" prefix in front of an EDI keyword
that is not numeric (i.e., a character, date, or logical field).
Action: Remove the prefix from in front of the EDI keyword, or make sure
that the EDI keyword you are referring to is numeric.
ERROR: Value in EDI brackets isn't a keyword or a function keyword at pos.
<nnn>
--------------------------------------------------------------------------
Reason: You have entered a value between EDI brackets that is neither an
EDI keyword, function keyword, or special keyword.
Action: Make sure that that the value you have entered between EDI brackets
is a valid keyword or function keyword.
ERROR: Prefixes cannot be used with special keywords.
-----------------------------------------------------
Reason: You have entered a valid prefix before a special keyword
(e.g., |F:LEVEL|).
Action: Remove the prefix from in front of the special keyword, or
make sure that you have entered a valid EDI keyword or function
keyword.
ERROR: Special keywords for input and output formulas cannot be used
together.
--------------------------------------------------------------------
Reason: You have entered keywords used for incoming and outgoing formulas
in the same formula (e.g, IF |LEVEL| eq "1" THEN |CURRELEM|).
Action: Make sure that you do not mix special keywords for incoming
and outgoing formulas in the same formula.
ERROR: There is no expression before the '/' or '*' operator at position
<nnn>
-------------------------------------------------------------
Reason: You are attempting to multiply or divide but there is no number
or expression before the multiply or divide sign (e.g. / 6).
Action: Make sure that there is an expression before the multiplication
('*') or division ('/') operators.
ERROR: An operator (+,-,/,*) cannot be the last character in a formula.
-----------------------------------------------------------------------
Reason: You have entered an operator as the last character in a formula.
Since there is nothing after the operator, the formula would be
invalid.
Action: Make sure that an operator is not the last character in your
formula.
ERROR: A decimal point cannot be the last character in a formula.
-----------------------------------------------------------------
Reason: You have entered a decimal point as the last character in a
formula. Since there is nothing after the decimal point, the
formula would be invalid.
Action: Make sure that a decimal point is not the last character in your
formula.
ERROR: Cannot have more than one decimal point in a number.
-----------------------------------------------------------
Reason: You have entered a number which has more than one decimal point
in it (e.g., 1.233.4). A number entered like that is not
acceptable.
Action: If you use numbers with decimal points in your formula, make sure
that there is only one decimal point in it.
ERROR: No closing array bracket after opening array bracket at
position <position>
--------------------------------------------------------------
Reason: You are missing a closing array bracket (i.e., "]").
Action: Make sure that there is a closing array bracket for each opening
array bracket.
ERROR: No array element is specified.
-------------------------------------
Reason: You have not specified which array element to use between array
brackets.
Action: Make sure that you specify which array element to use.
ERROR: Invalid character in array element: <character> at position
<position>
------------------------------------------------------------------
Reason: You have entered a character which is not a number between array
brackets.
Action: Make sure that the value between array brackets is numeric.
ERROR: You cannot use array element 0 in a formula.
---------------------------------------------------
Reason: You have used array element 0 in a formula. The array element
specified must be greater than or equal to 1.
Action: Make sure that the value entered between array brackets is greater
than or equal to 1.
ERROR: No closing quote after opening quote at position <position>
------------------------------------------------------------------
Reason: You are missing a closing quote.
Action: Make sure that there is a closing EDI bracket for each opening
quote.
ERROR: Missing <number> closing ')' parentheses.
------------------------------------------------
Reason: Your formula has more opening parentheses than closing parentheses.
Action: Make sure that you have same number of opening and closing
parentheses.
ERROR: Missing <number> opening '(' parentheses.
------------------------------------------------
Reason: Your formula has more closing parentheses than opening parentheses.
Action: Make sure that you have the same number of opening and closing
parentheses.
ERROR: There is an error in your formula.
-----------------------------------------
Reason: Your formula has an error in it due to one or more, but not
limited to, the following reasons:
- You have used PROGRESS functions in your formula, and have not
spelled the function names correctly.
- You have given a PROGRESS function the incorrect number
of parameters in your formula. For example, the following
formula is invalid because the MAXIMUM function requires
two parameters:
maximum(|INVLEXTP|)
- You have not used an ELSE statement when using an IF statement.
For example, the following formula is invalid.
IF |INVLEXTP| > 30000 THEN |INVLEXTP| * .01
- You have not left spaces between PROGRESS functions and/or
EDI keywords and/or EDI function keywords. For example, the
following formula is invalid:
if |INVLEXTP| > 30000AND|INVORDNO| > 100
The system cannot calculate your formula in this case.
- You have referred to a field that has multiple extents in it
(i.e., is an array), and you have not specified which array
element to use. For example, the following formula segment
is invalid.
IF |INVSHADD| begins "1000" THEN ... ELSE ...
where INVSHADD is an EDI keyword that refers to a field
that is an array.
- The PROGRESS procedure generated has a statement with more
than 4,096 characters in it. This may occur if you are
using many EDI function keywords in your formula.
- The path of the directory where the compiled program is
saved to has an extension (i.e., a "." or point) in it.
For example, if the directory where a compiled program
is to be saved in is called /usr/mapics.run/ediprogs,
certain versions of PROGRESS may not save the compiled
program in the proper directory because there is an
extension in the pathname (i.e., mapics.run) where the
compiled program is to be saved.
- You do not have the proper read/write permission to create
the PROGRESS procedure, and/or the compiled program in the
current working directory.
- You do not have enough disk space to generate the PROGRESS
procedure required to calculate your formula, and/or the
compiled program.
Action: Make sure that you have entered your formula correctly, that
PROGRESS functions are spelled and used correctly, and you have
left spaces before and after PROGRESS functions and/or EDI
keywords and/or EDI function keywords. If you still cannot
determine what the problem is with your formula, verify the
PROGRESS procedure, in your current directory which will be
called:
prg<terminal-no>
(e.g., prgttyh7)
where <terminal-no> is the terminal which you are using. This is
the procedure used to calculate the value of your formula. You
can verify this procedure by using the PROGRESS editor, or the
text editor of your choice. If you are still unable to determine
what the problem is, please contact your VAR and/or MXP
technical support.
If the directory where a compiled program is to be saved in has
an extension in it, and programs are not being saved properly,
then the directory will have to be renamed. Please see your
System Administrator.
If there is not enough disk space to generate the PROGRESS
procedure necessary to calculate the value of your formula,
or you do not have read/write permission in your current working
directory, then please see your System Administrator.
ERROR: Formula results in a division by zero or is undefined.
-------------------------------------------------------------
Reason: Your formula is valid, but is returning an undefined (i.e., ?)
value.
Action: Make sure that you are not dividing by zero in your formula,
and that EDI keywords and/or EDI function keywords,
do not result in an undefined value.