Type system and expressions

In EasyMorph imported and calculated values can be of one of these types:

  • Number
  • Text
  • Boolean
  • Error
  • Empty

A table column in EasyMorph can contain a mix of values of any type. For instance text values and numbers can be mixed in the same column.

In tables text is always aligned left, while numbers are always aligned right. That's how you can distinguish if a number is indeed a number, or a text value consisting of digits.

Numbers

Numbers are either integer or decimal numbers with fixed decimal point. Floating point numbers are not supported. The effective range is approximately (-7.9 x 1028 to 7.9 x 1028) / (100 to 28) which is 28-29 significant digits with or without decimal point between them.

Note: unary minus (i.e. single preceding minus) is not supported in expressions. Instead of numeric negative constant use zero minus the constant value. For instance, write 0-5 instead of -5.

1000 * (0-1)

Text

Unicode text that can be of any length. To concatenate text strings use ampersand (&). In expressions, text constants should be wrapped either in single or in double quotes. For instance:

'Easy' & "Morph"
replace('03-2016', '-', '/')
To deal with single quotes use double quotes and vice versa.
replace('"ABC"', '"', "'")

Dates

Notice that there is no dedicated type for dates in EasyMorph. Instead, as in Excel, dates are numbers, but only formatted as dates. Such number date represents a number of days since January 1st, 1900. For instance 42680 corresponds to November 6th, 2016, because there are 42680 days from January 1st, 1900 to that date.

Because dates are numbers, there is no need in using a special function for adding days. To get the next day just add 1. However, there is a special function for adding months — addmonth().

EasyMorph Scheduler

Boolean

Boolean values can have only two values — TRUE or FALSE. Most often they are a result of a comparison or logical function.

Empty values

If a table cell has no value — it's a so called empty value. Empty values are somewhat similar to nulls in relational databases but not exactly. For instance, an empty value always equals to another empty value, and equals to an empty string. All these comparisons return TRUE:

<empty value> = <empty value>
<empty value> = ''

You can check if a column value is empty using isempty() function. The length of an empty value is always zero. To create an empty value use empty() function. For example:

if( [Column 1]='AAA', 'Triple A', empty() )

In tables, empty values have light grey background (might be unnoticeable on some monitors). That's how you can visually distinguish cells with empty values from cells with empty text strings — the latter have white background.

Errors

Errors are special values that indicate incorrect calculation. An error can only be a result of an expression, it can't be defined as a constant. You can check if a value is an error using iserror() function.

if( iserror([Column 1]), empty(), [Column 1] )

Note that referencing an error value is also an error. Also, calculated parameters must not evaluate to errors. If a calculated parameter evaluates to error then project execution will halt.

Field references

To reference a field (column) in an expression, wrap the field name in square brackets. Note that field names themselvs must not contain square brackets. Spaces, punctuation and non-Latin characters are allowed.

[Amount] * [Price, USD]

Field names are case sensitive — e.g. [Total], [TOTAL] and [total] are 3 different field names.

References to parameters

To reference a project parameter in an expression, wrap the parameter name in curly braces. Note that parameter names must not contain curly braces. Spaces, punctuation and non-Latin characters are allowed. In the example below FX Rate is a parameter:

[Amount] * {FX Rate}
Parameter names are case sensitive as well.

Functions

EasyMorph has more than 80 various functions that can be used in expressions.

  • Number functions
  • Text functions
  • Logical functions
  • Date functions
  • Special functions

The full list of functions with examples is available in the web-help. See Help: functions.

Advanced topics

Referencing values in another row or another table

In a table, an expression is calculated for each row. It's not possible to directly refer field values in another row or another table. Although you can use "Shift column" transformation to bring values from a previous or next row into current row. Also "Peek" transformation can bring in a particular value from another table, making it a new column in current table.

Conditions

These four transformations require conditions:

  • Rule
  • Filter by condition
  • Halt on condition
  • Trim by condition

A condition is an expression that must return only TRUE or FALSE for every row. If a condition returns error (or other non-boolean value) for some row, than a transformation that uses the condition will fail. Use "Clean up" transformation or iserror() function to detect and handle errors in a column, if needed.


Read next: Project parameters