Expression Builder

Expression Builder is a powerful intuitive Point & Click UI for creating Expressions which calculate a scalar (single) value. This value can then be used for Calculated Fields and for Defaults - a similar interface is also available in other places such as Field Checks.

If you know SQL you can type directly into the Expression box - if you do you do not have to type Select - this is added automatically.

You can add expression by pointing and clicking on fields in the right box - COMPONENT FIELDS - Sister Fields from the Entity are immediately displayed, Ancestor Fields are also easily accessible by simply expanding the > preceding a referenced Entity, their Ancestors in turn can also be accessed ad infinitum.

100’s of powerful Postgres Functions are available in the right search box aswell as any User-defined Functions.

Aggregates

To Add an Aggregate of a Sub Component Field (see Components below)

click on AGGREGATE FUNCTIONS select an Aggregate Function e.g. sum then select Child Component e.g. Invoice Lines then double click on the Filed you want to aggregate it will then show in the left window is Function input. The click on Add expression the expression should then be populated with the corrected expression for this aggregate.

e.g. for Calculated field Sales Total for Entity Sales Invoice

pg_catalog.sum (invoice_items.line_sale)

CRM example get last opportunity stage

last (opportunity_stages.opportunity_stage order by opportunity_stages.date_from)

Calculated Fields

Calculated Fields are for Values that are automatically calculated (see below) e.g. in Sales Invoice the Sales Total is calculated by summing Invoice Lines.

Virtual Calculations are calculated on the fly whenever a Report or Enquiry is run.

Materialized Calculations store the result in the Database so they can make queries and reports faster because they do not have to be calculated whenever these are viewed.

See Expression Builder above for more detail on defining calculations.

See screenshot below for a simple calculation definition for Sales Total

SQL Editor

Expression Builder, View Designer Code mode uses a powerful SQL Editor which can access the full capability of Postgres syntax SQL.

Code >> Right Click >> Command Pallett (F1)

To access Editor Options e.g.

Command F = Find (Control F in Windows)

Command Option F = Replace

Command / = Toggle line comments

Component

Component-based expression building.

Constraints

Defining constraints using expressions.

Calculations & Defaults

Setting up calculations and default values.