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.