formulavalue
RESULT
,
DATABASE
,
FORMULA

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

Referencing Fields in the Original Database

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

VersionStatusNotes
10.0UpdatedCarried 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.