XML Generation
Formulas
Formula are additional data defined within records. Formula are derived
during XML processing.
Two types of formula may be defined, from this window which opens
automatically after Add is pressed.
Mathematical formula (number) are used for two purposes:
-
within record calculations (such as "extension = qty * unit price")
running totals or other calculation (to a Variable)
Concatenated/substituted text field (text) are used for two purposes:
-
assembly of data into a single field for purposes other than FormTrap formatting (such as Suburb+comma, State and PostCode)
Substitution lookup value
To define a formula, select the record (Product in this example) from
the Document window, then right-click and select Properties ...
... or select via Properties.
All selection methods open the Record Properties window. From
Properties windows select Formulas, Add, then one of the two
options from the pull-down.
For Concatenated text field, click HERE.
Mathematical Formula
Supply Name, then construct the Formula.
Formulas can use any of the field names defined for this project, via the Insert
into formula ... pull down.
Fields show in the list in order of their source (Master for example),
then alphabetically:
Operators are any of the set shown (table is reproduced from the web reference).
muParser from sourceforge provides the Mathematical formula functions of FormTrap and may be viewed at http://muparser.sourceforge.net
This formula is for a running total of detail lines, and is a common variable/formula combination:
[master/Running total] = [master/Running total] + [Product/Extended Cost]
You may check the syntax of ...
... and test values for the formula.
Rounding
Rounding for money amounts and quantities is required where excess decimals
are produced. The simplest way is to construct the "raw" formula and test it,
then to add rounding later.
- Construct and test the raw formula until satisfied.
- Copy then Delete the section of the formula to be
rounded.
- Press Insert into formula and select Round
to 2 decimals.
- Select [record/field] as shown and Paste the
original (copied) formula over it.
- Insert appropriate brackets (highlighted) to produce
the finished, rounded formula
(in this case the additional brackets are redundant).
If-Then-Else Statements in Formula
Extensions implemented November 2014 allow If Then Else statements in
muParser.
This expanation is copied from the web reference:
This is a typical If Then Else expression, calculating a line discount. The record may have an Amount-On to calculate discount on or, if that is zero, calculates using running total.
- ([Discount/Discount-On] != 0)
Discount-On != (not equal) 0 (zero) - ? ([Discount/DiscountPercent] /100) *
[Discount/Discount-On]
? (true), calculation is based on Discount-On. - : ([Discount/DiscountPercent] /100) *
[master/Running-Total]
: (untrue), calculation is based on Running-Total.
Note, the entire formula is on the one line.
This is another formula: If the check box GST Applies? is
ticked (returns 1, defined as Parser Numeric) then calculate
rounded GST, else set GST to zero:
- [master/GST] = ([master/GST Applies?] == 1) ? rint([master/gross invoice] * 0.1 * 100) / 100 : 0
Ordering Formula
Mathematical formula may need to be ordered as all formula are evaluated prior to layout operations. When a prior value is required in the detail record (for example, a discount where the value of invoice to date is used as the basis), you will need a formula to store the value to date and must order to occur prior to the update of invoice value including discount.
Select Formulas, Edit, 
Evaluation order, then order your formula per this screen shot:
Concatenated Text Formula
These allows fields defined as text to be concatenated for later processing,
and is used mainly to form "substitution" names.
Substitution names may reference the Lookup file and, where found, substitute
the associated text.
For Version 7 compatibility, the substitution file name may be used instead.
This is a substitution formula that obtains the product description for a
product from lookup. Note the product code forms part of the data file name.
Use this if your product descriptions are abbreviated beyond recognition.
This example shows an Email address being substitution based
on the Customer Number:
Supply Name, then construct the string using constants and the data
field taken from the Insert ... list.
Fields are enumerated in the list in this order:
- current record
master record
master variable fields
system fields
... then alphabetically.
Fields are placed adjacent and intermediate characters may be keyed, normally spaces or carriage returns
You may view results by keying data values, with ";" following to see the
concatenated result.
For Substitution formula, make sure text source fields are Normalized
to "Trim" leading and trailing spaces.
Numeric data with leading zeroes should be Normalized to "Remove leading zeros".
