Expression Builder

The Expression Builder allows you to create formulas and manipulate data.

An expression is something commonly thought of as a “formula”. It is a combination of three things; (1) starting values, (2) manipulation of those values, and (3) a resulting value(s). Each of these properties of an expression is explained in greater detail below.

Starting Values

Starting values for an expression, if they exist, could come from the database, the interface, the session, or run time values. It is very important to note that you may have a static expression whereby you don't start with any starting values. In a static expression you simply type in the result that you want to see output. When an expression starts with data from the database, interface, session or run time values, it is referred to as a dynamic expression, because the expression results are variable based on the starting data pulled in.

Manipulation

Manipulation involes taking the starting data and transforming it into something else. Manipulation can involve the data itself or merely the appearance of the data (for example adding “bold” or increasing the font size). Manipulation may include the use of some combination of text or numerical functions, mathematical functions, application of html markup, and looping expressions and subexpressions.

In static expressions, manipulation simply involves entering the static values you wish your expression to output and, possibly, formatting them.

Resulting Value

What comes out of an expression is its resulting value. This is always the product of your starting values and the subsequent manipulation.

Your results can be either plaintext or html-enabled. Enabling html will attempt to present your results with html formatting enforced. Plaintext, on the other hand, will simply display all characters in the result, including any html tags that may be present.

Subexpressions

A subexpression is a complete expression (including a Query Builder) that is evaluated independently, and whose result is inserted into an expression. You can nest as many subexpressions as you like into your expression, or into each other.

Expression Builder Tools

Introduction to the tools in the rich text area

Looping Control

You may toggle the Loop Control function to allow this expression to loop through its results for those functions which do not inherently use looping.

Sometimes you will develop data in your Query Builder that returns multiple records. You will need to understand how you want your expression to loop over each of these records to achieve your desired result.

For example, you may want to sum the “amount” field for each record. Or, you may want to concatenate the names of each record in the result set, separated by commas.

Some functions are inherently “looping” functions, meaning by default they will loop over each record in the result set. The “Sum” function is a good example of an inherently looping function.

Other functions are designed to operate on a single result from a single record and therefore will select a random record when multiple records are returned from the result list.

Turning on the Loop Control will tell the Expression Builder to loop over any results. Turning off the Loop Control will return a single record, however, if there are multiple resulting records, the Expression Builder will choose a random record. Build your queries carefully to ensure that you are returning the number of records you require.

HTML Editor

This section of the Expression Builder allows you to add a variety of HTML formatting to the output of your expression. Use this section to cut, copy or paste text into and out of the Expression Box, manipulate fonts and styles, change alignment, and create lists.

These controls insert actual hypertext markup language (HTML) into your Expression, however the Expression Box itself does not display the raw HTML, only the formatted results of it. To see the full expression as its HTML source code click the HTML button in the upper right hand corner of this section.

Functions

In the Expression Builder, you can use a variety of pre-defined math and text based functions. For example, you can capture the current date easily by using the Date function NOW(). Each function below contains a description and example of its use.

List of Expression Builder Functions

Text Functions

Math Functions

Arithmetic
Counting
Minima/Maxima
Rounding
Miscellaneous
Trigonometry

Currency

Dates

Geolocation

expression builder.txt · Last modified: 2016/09/14 14:19 (external edit)
Copyright WorkXpress, 2019