R:BASE 11 Help

The Expression Builder is a utility that allows you to create an expression and assign it to a variable. An expression is a calculation used to determine a value and is usually made up of operators and operands.

 

The operators are listed below:

 

 

Operands, which are the part of the expression the operators are acting on, can be variables, constant values, or column names.

 

An example of an expression to calculate the total charge for a purchase (quantity multiplied by price), would be the following expression:

 

operator

 |

quantity * price

 \  /

  operands

 

You can assign a variable to an expression and then place the variable on the form. When the form is used, it will display the value of the variable. A variable is a name you create that will contain the answer to an expression. For the example, vQuantity = (12 * 10), the variable vQuantity contains the value 120.

 

Lookup Variables

Another example of an expression would be to create a lookup variable to capture the value within a table. For example, if you wanted to know the credit limit for a customer that is stored in another table, and you already know the customer's ID#, then you can create an expression for a variable and display that variable on the form. The variable may look like this: vCrediLimit = CreditLimit IN Customer WHERE CustID = CustID.

 

Recalculating Lookup Variables - IMPORTANT!

Lookup variables are designed to retrieve data from other tables and, once defined, their values cannot be changed. Expressions based on lookup variables are evaluated and refreshed instantly as the cursor moves between fields. This behavior differs from versions prior to 7.x, where the RECALC command had to be manually issued to refresh variable values. If the form requires the ability to update variable values, avoid using the Expression Builder for the lookup variables and instead use EEPs (Entry Exit Procedures), which allow variable value updates as needed. Alternatively, the lookup variable may be defined before the form is launched, either within a command file or through an EEP executed prior to form initialization. This approach is the simplest way to work around static lookup variable values during form execution.

 

Multi-table Expression Execution Order

During the form's startup, when tables are opened, each "open table" action triggers a recalculation of variables. These variables are calculated on a per-table basis, starting with the main table and then moving on to the slave tables. This means that variables associated with the main table are evaluated first, followed by those tied to the slave tables. In short, form variables are evaluated in the order they appear in the Expression Builder list, but within the scope of each table. While the form is running, variables are recalculated in the "On Exit" EEP of the controls. Only the variables linked to the control's table are recalculated, and their evaluation follows the order specified in the list.

 

Aggregate Functions

An aggregate function can be used to provide summary data about a group of rows in a table or for all rows in a table. Aggregate functions may be used as follows:

(MAX(QuoteReceivedDate)) in QuoteHeader where PartID = .vPartID

 

 

* Selecting aggregate functions, such as MIN and MAX, requires that R:BASE keeps an accumulator and choose to only use the first 80 characters for NOTE values. This matches the fact that if you sort on NOTE fields, the sort will be based on the first 80 characters only.

 

R:BASE Functions

Expressions can contain one or more R:BASE functions. A function differs from an operator in that a function provides a predefined complex expression to evaluate standard mathematical, trigonometric, financial, or logical functions without requiring the user to enter the formula in a complete R:BASE expression. Function names are reserved words.

 

To define an expression in a form:

 

 

The "Expression Builder" dialog box is displayed.

 

 

 

At the top, the "Variables" panel displays a list of defined variables. Values stored in this panel include the variable name, data type, expression, and table name the variable is associated to.

 

•Move Up - move a variable up the order in the list. The button would be used if a newly added variable must be moved up the order list so that another variable lower in the list can use it.

•Move Down - move a variable down the order in the list

•Copy Selected Expression(s) to Clipboard - copies the selected variable(s) to the clipboard, to be placed in another form

•Paste Expression(s) from Clipboard - pastes the variable(s) from the clipboard to the current form

•Roll Back to Original Declaration - reverses changes made to the selected variable definition

•Delete - deletes the selected variable. When doing so, R:BASE will ask if you wish to remove that object from the form as well.

 

The "Define Expression" panel allows you to create new variables, as well as edit any existing variables. Available fields include options for the variable name, data type, expression, and table name. An option is available to edit the "Expression:" memo field font, which is helpful to clearly view all characters, such as commas, parenthesis, etc.

 

MDI Variable

Between the Name and Type fields is a toggle button to assign a variable as MDI. By default, a variable definition is global. Either variable type (global or MDI) is displayed in the variable list with a image next to the name, which designates the variable type.

 

The Expression memo field can also accept curly brace comments "{}" within expressions, to document the purpose of the variable, and perhaps the logic behind a complex expression.

 

 

•Add - adds a new variable to the list

•Change - changes the data type, expression, table name, and/or order for a selected variable

•Global - adds a variable to the list of global variables within R:BASE. After closing the Form Designer, the variable definition may be verified at the R> Prompt with the SHOW VAR command.

 

Note: For more information about expressions, see "Expressions".