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 meant to "look up" data from the other table. Once the variable has been defined, you can not change the value. All expressions (if based on Lookup Variables) are evaluated and refresh instantly when the cursor moves from field to field. Prior to version 7.x, this was not the case. You had to issue the RECALC command every time you wanted to refresh your variables. If you wish to achieve the same results, then do not use Form Variables, but rather EEPs, which can fire when you want and provide the freedom to change the variables accordingly. Or, you can define the Lookup Variable value before the form is launched. The easiest way around this obstacle is to define the lookup variable before you launch the form. You can do so in the startup command file or in an EEP before the form is launched.

 

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 like this: (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".