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.

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 [[Expression Function - Now|Date function NOW()]]. Each function below contains a description and example of its use.