DP4 uses essentially the same formula processor in several DP4 utilities: QAB; Report Writer; Browser; Auditor; RECASTDB. This formula processor uses a sytax that is generally similar to that used by most programming languages. The syntax is explained in detail on the following pages, but you may find a brief over-view helpful.
Operands must be separated by spaces or operators that cannot be part of the operand.
For example, 'A MOD B' has to be written 'A MOD B' and not 'AMODB', but A+B is acceptable.
An expression may use operators (+ - * / and also operators like
mod denoted by an identifier), function calls (e.g.
round(rate*amount,2), references to fields
(customer.name), standard variables
(for example end_key in QAB, or CLIENTNAME in Report Writer), and use constant values and
parentheses.
For the purpose of matching names neither case nor underscores
are significant. Thus all the following are equivalent:
CUST_NR, Cust_Nr, cust_nr,
custnr.
When referencing fields in formulas and conditions you can only refer to the fields already defined as part of the report. Note that this is quite different from QAB. As a result your field processing may include fields that are never listed.
DP4 does not prohibit you from declaring fields with names that have a predefined meaning as an operator or built-in function. For example you might inadvertently use YEAR as a field name in a program or on the database. If you do this the operator or function will not be available under that name. To minimise the chances of this causing you a problem you should use the RESWORDS program to post the reserved words for QAB to your database. (This list also does duty for Report Writer)
If an identifier is not the name of a field, role, or table
defined on the database or a local field defined on your program,
or a macro (but not a function) defined using QAB Function Definition, The formula processor will look
for a match with the names of the operators and functions built
into the formula compiler. In this case a partial match is
acceptable: as long as at least three characters match the
interncal function or operator, and as many characters as are
specified match, then the identifier is considered to match. Thus,
SEC(time), SECOND(time), and
SECONDS(time) all extract the seconds from a time, but
SECD(time) would generate an error. You are recommended only to use
abbreviated operator or function names where there is no danger of
confusion, for example MIN(time) would not extract the
minutes from a time, because MIN is a function in its own right. As
it takes a numeric parameter you would get a compiler error.
Each operator and function must be used with operands of the
correct data types. Consider the formula expression:
code_prefix CONCAT code_number
In this formula CONCAT joins two character fields to give one
character field. It is essential that code_prefix and code_number
are both character fields. If code_number were, say, an integer
type, an error would occur at compile time.
Conditions and formula expressions are not simply worked out
from left to right. Operands have a precedence of operation, which is
overruled only by parentheses. For example: ( 1 + vat_rate ) * price
The multiply operator * has a higher precedence than the add
operator +. Without the parentheses, the expression would mean
multiply vat_rate by price and then add 1.
It is sometimes worth using parentheses even when not actually required, if this makes your intention clearer. On the other hand using parentheses where the precedence should be obvious can be confusing.
You may include comments in your expressions, using the comment
delimiters { }. For example:
(1 + vat_rate) * price
{vat_rate equals a decimal fraction such as 0.175}
The DP4 Formula Processor allows you to process multi-occurs fields. This is where DP4 differs most from other programming languages in the way it processes expressions. How the DP4 Formula Processor handles-multi-occurs fields in formulas is introduced in Multi-Occurs Fields and Processing