Worldsoft Documentation Search

Formula Editors

Formula Editors

 

There are multiple places within the Information Management Platform that have a formula panel. These formula editors are fairly consistent and generally have the following layout and options:

 

1 – Tabs

This allows you to switch between the Main Tab and the Functions List.

2 – Column Variables

This lists the columns references/variables available for use in formulas. Note, in some formula editors there may be no variables available and instead all columns will be listed as parameters. Double-clicking will insert the selected variable into the formula.

3 – Param Variables

This provides a listing of parameters available for use in the formula. Double-clicking will insert the selected variable into the formula.

4 – Preview

The preview window will provide a sample formula result if Test Formula is clicked.

5 – Test Formula

This button checks the syntax of the formula and executes the formula if possible. Results will show in the Preview window.

6 – Formula

This is the edit/entry section for the formula.

7 – OK/Cancel

OK will save the current formula. Cancel will exit the formula window without saving.

 

The Functions List will vary depending on where the formula editor is called – not every function is available in each editor.

 

Functions are available in the top grid. Double-clicking on a function will insert it into the formula. The ‘Function Description’ section contains documentation on what the function does and any inputs used.

 

Syntax

 

Formula syntax is based on SQL Server syntax. Formulas are never complete queries – you won’t find SELECT, UPDATE, CREATE, or DELETE available – but instead are small scripts that always set the value of a single column. Note, traditional CASE statements are not available. Data cannot be regrouped (SUM(), MAX(), etc.) at the formula level, with the exception of data in the report writer.

 

Logical Comparisons

 

If you need to make a logical comparisons, IIF(,,) is the most common function you’ll use. Its standard syntax is:

IIF(test condition, value/function if true, value/function if false)

 

This function can be nested if you need to test multiple conditions. As for the test condition, the following comparison operators are supported:

=

Equal to

Greater than

Less than

>=

Greater than or equal to

<=

Less than or equal to

<> 

Not equal to

 

User Defined Functions

 

In many formula editors you will find functions that begin with ‘UDF_’. These are functions created by GoWSI. These functions are either specialized to the purpose of the particular formula or internal replacements for standard SQL functions that would otherwise not be available.