• Expressions
  • Concept
    • Defining values
    • Manipulating existing values
    • Complex conditions
  • Elements of an expression
    • Functions
    • Fields
    • Numbers
    • Strings
  • Pretty print
  • Chaining conditions
  • Good to know

Expressions

An expression is a combination of functions, fields and values that produces another output. They are very useful for calculations, textual changes and logical operations.

Concept

Typically, you use expressions for one of the following purposes:

• Defining values that aren't present in your data set.
• Manipulating existing values so that the output is different.
• Creating more complex conditions than the wizard allows.

Defining values

The simplest way to use an expression is to define a value that is not present in your data set. For instance, here we define the string Mr:

This will simply output the string Mr.

On its own, defining a value isn't that useful. In combination with functions, however, it becomes more powerful. For instance, here we check whether the type field's value is equal to the string black marker:

This will return a value that can then be checked in a condition.

Manipulating existing values

Changing an existing value so that it outputs something different is done by using functions. For instance, here's a table that shows the effect of a few functions on some fields' values:

• Value of field A: 3
• Value of field B: 6
• Value of field C: 'Experlogix'
• Value of field D: ' John'

This page shows all of the available functions.

Complex conditions

Instead of using the wizard to define your conditions, you may also use expressions. We always encourage you to use the wizard as much as possible, but there are a few things the wizard won't let you do. For instance, you can't insert multiple conditions in a condition through the wizard.

There are a few functions that return 1 (true) or 0 (false); these are called logical functions. The eq function (to check whether a value is equal to another value) is a good example:

This expression will return 1 if the type field's value is equal to black marker. This expression can also be defined through the wizard:

But more complex conditions require expressions. Here is an example of a condition that checks whether the type field's value is equal to either black marker or red marker:

Here, we use the or function that checks whether one of the arguments is true (which means that it returns 1). So, if either of the eq functions is true, the entire expression is true and the condition is thus true.

Elements of an expression

An expression can contain the following elements:

• Functions
• Fields
• Numbers
• Strings

Let's go over each of them.

Functions

Functions are shown in pink (although they may appear in black the second time you open the expression):

Adding a function to an expression can be done in 2 ways: either you type it in or you use the function wizard.

To use the function wizard, follow these steps:

1.

Select the Insert Function button:

2.

A window will pop up, asking you which function you want to include.

3.

Choose your function (you can search for it or browse by category) and select OK. This opens a new window where you can choose the function's arguments:

4.

You can type in your argument, use the expression button to bring up another expression editor (which you can then use for inserting fields if you like), or you can select the green plus to add another argument (if the functions allows a variable number of arguments). Select the red cross to remove an argument.

5.

Select OK and your function, with all the arguments you provided, will be inserted into the expression:

Fields

Fields are shown with ${} around them:

To insert a field, follow these steps:

1.

Set the mouse cursor where you want to insert the field.

2.

Select the Insert Field button, select your field and select OK.

The field is now inserted into the expression.

This expression will now output the value of the field.

Numbers

Numbers are shown in brown (when used in a function):

To insert a number, simply type it. To use a decimal separator, use a dot, like this:

Strings

Strings are shown in green:

Strings will only be recognized as such when they are put in between apostrophes. Otherwise, the text won't be green and thus it won't be a valid string.

Pretty print

The expression window features a Pretty print button.

Clicking it will tidy up your expression according to various formatting rules, such as added indentation, parameters on a separate line etc. Here's an example:

The sum of two fields and 50000:

It is easier to discern the three arguments of the sum() function in the prettyprinted version.

Another example:

Checking whether the following expression is true: is the type a pencil or a marker with the description red?

Pretty cluttered without pretty print.

As you see, for multi-function expressions, pretty print is a great feature as you get a better view of the whole expression. It is especially useful for chaining conditions.

Chaining conditions

A more advanced use of expressions is to chain conditions together to create a condition chain. This makes it possible to evaluate many conditions one after another, until one is true. You can use this to check for multiple things at the same time.

For instance: you want to change a number field to text, as follows: 1 is Monday, 2 is Tuesday, 3 is Wednesday etc. So when your field contains the number 4, you want Thursday to appear. You could do this with the Text replace feature, but using an expression for this has the advantage that you can easily copy and adjust it if necessary.

Here's how we've set up the condition, using the warehouse field:

1. Check whether the warehouse field equals 1. If so, output Monday.
2. If not 1, check whether the warehouse field equals 2. If so, output Tuesday.
3. If not 1 or 2, check whether the warehouse field equals 3. If so, output Wednesday.
4. etc

We'll use the if(cond,a,b) function for this, which returns a if cond is true and b if cond is false. Also eq(a,b) to check whether a is equal to b. So basically, it'll look something like this: if (eq(warehouse,1), monday, if (eq(warehouse,2), tuesday, if (eq(warehouse,3), wednesday...

Here's the result, after pretty printing:

We added 'invalid day' at the end for when the input is not 1, 2, 3, 4, 5, 6 or 7.

Good to know

A few things that are good to know when using expressions:

• A function that takes multiple arguments as its input will work with only one argument when that argument is recurring. For instance, the sum function can be used to return the sum of all the quantity fields, like below
• This is often used in quotes and invoices, where each line item has its own subtotal. You can then just use the sum function on the subtotal field to find the grand total.
• 

• You can use the Validate button to check whether you made syntax errors. It's always a good idea to select Validate before clicking OK.

  

  Your expression may be valid syntactically, but make no sense whatsoever in the context of your template. Use your best judgment.

• When using pseudo-fields, be sure to select the right type. Some functions won't work on text and some won't work on numbers.
• 
• 0 will always be considered false, but everything else is true. So if your condition returns 2, or 5.4, it will be considered true.