The formulavalue statement calculates the result of a formula. Usually this is done with an assignment statement (for example x=2 * y ), but the formulavalue statement gives you more flexibility. You can specify what database is to be used for the calculation (an assignment statement always uses the current database) and you can specify the formula using a variable, making it easy to change on the fly. This statement also gives you more control over how errors are handled.
Parameters
This statement has three parameters:result – is the name of the field or variable that will receive the result. If a field is specified it must be in the current database, even if you have specified that the formula be calculated using a different database.
database – is the database that the formula will be calculated in. This database must be currently open. If this parameter is "" then the current database will be used.
formula – is the formula to be calculated. Unlike an assignment statement, the formula is contained in a formula, allowing you to change the formula on the fly. If the formula doesn’t need to be changed you must enclose it in quotes, otherwise you can store the formula in a variable.
Description
This statement calculates the result of a formula. This statement allows you to:
To find out if an error occurred you must check the info(“error”) function.
This simple example performs a calculation using a Checkbook database, which may or may not be the current database (it does have to be open). There will be no window flashing or other visual artifacts even if another database is currently on top.
local myFormula,lineBalance
myFormula="Debit-Credit"
formulavalue lineBalance,"Checkbook",myFormula
The formulavalue statement is especially useful in situations where the user can enter their own formula. This example prompts the user to enter a formula. The result of the formula is calculated and displayed, unless there is an error, in which case that is displayed.
local myFormula,myAnswer,myError
gettext "Enter Formula:",myFormula
formulavalue myAnswer,"",myFormula
myError = info("error")
if myError=""
message "The answer is: "+ constantvalue( myAnswer)
else
message info("error")
endif
The formulavalue statement is also useful for validating user supplied formulas. If the formulavalue statement can process the formula without an error, then so can other statements like select or arraybuild. In this case we don’t really care what the resulting value from the formula is, as long as it is a number (integer) and there is no error.
local myFormula,myAnswer,myError
gettext "Enter Formula:",myFormula
formulavalue myAnswer,"",myFormula
myError= info("error")
if myError <> ""
message info("error")
rtn
endif
if datatype( "myAnswer") <> "Integer"
message "Formula must calculate a true/false answer"
rtn
endif
execute "select "+myFormula
Sometimes you may want to reference fields in the current database. For example, suppose you have an Invoice database that contains customer names in a Name field, and a Customer database that contains First and Last name fields. This formula can be used in the Customer database to check whether the current customer name is the same in both the Invoice and Customer databases.
formulavalue isSame,"Invoice",{Name = ««First»»+" "+««Last»»}
Notice that the First and Last fields, are enclosed in double chevrons. The use of double chevrons specifies that a field is in the current database, instead of the database that is being used for the rest of the calculation. There are three fields referenced in this formula:
Name ☞ in Invoice database
First ☞ in Customer database (because of double chevrons)
Last ☞ in Customer database (because of double chevrons)
Advanced Note: Internally, Panorama actually converts double chevrons into the fieldvalue( function. So the example above is internally converted into this:
formulavalue isSame,"Invoice",{Name = fieldvalue("Customer",First)+" "+fieldvalue("Customer",Last)}
The double chevron notation is a convenience, making this formula much easier to write, and to understand.
See Also
History
| Version | Status | Notes |
| 10.0 | Updated | Carried over from Panorama 6.0, but now allows the use of double chevrons to specify that a field is in the current database, rather than the database being used for the rest of the calculation. |