Published on *Visokio* (http://kb.visokio.com)

The Formula Editor dialog in Omniscope is a powerful tool for creating and editing an Omniscope formula. For more general information about adding formulae to your files, see **Formula Fields [1]**.

The large white box is the formula itself. This box can be edited just like a text field in a form. You can type your formula in from scratch, or use the buttons underneath to help you insert elements of the formula.

Field and variable references are shown inside the text as little embedded buttons, but these behave like characters in the text, and can be selected, cut, copied, pasted and deleted just like other text. Additionally, you can click each embedded button to change the field or variable.

Field references can be inserted using the ** Insert field** below the formula box. Variable [2] references can be inserted using

Alternatively, you can type the field/variable name directly (but if it has punctuation or spaces, you will need to enclose the name in [square brackets]).

Colours are used to identify different parts of the formula. For example, green indicates a function such as SUM(...), blue indicates a field reference, and cyan/turquoise indicates a variable reference.

During a session with the formula editor, use Ctrl+Z to undo your edits, and Ctrl+Y or Ctrl+Shift+Z to redo edits you have undone. Accidentally deleted half an hour's work? Ctrl+Z will bring it back.

Once you have clicked ** OK**, the edited formula will be applied and your fields will recalculate. At this point, from the main Omniscope window, you can use

If you know the function, simply type its name followed by an open bracket, e.g. "SUM(". Then provide any arguments/parameters to the function, separated by commas, ending with a close bracket. For example: "SUM(1, 2)".

If you are unsure how to use a particular function, type the function name and the open bracket, then move your mouse over the function. A tooltip will appear with guidance.

To browse and insert functions from a list, click * Functions list* [3]. In the dialog that appears, browse through the functions and read their syntax. Find the function you want and click

All standard spreadsheet functions are provided where appropriate for Omniscope, so you can expect POWER() to work in the same way as Excel, for example. Additional Omniscope-specific functions such as SUBSET are also provided.

See the Knowledge Base **Functions Guide** [4] for full documentation of all functions.

Long and complicated formulae can be hard to read and work with. Use spaces, tabs, line-breaks and comments to space-out and describe sections of the formula, without affecting calculation.

Comments must be surrounded with "/*" and "*/" as in the following example:

(1 + 1) /* adding some numbers */

/ 2 /* dividing in half */

The formula editor window can also be resized as large as needed when working on a very large formula.

While editing your formula, you can see the results in two ways:

In the section ** Example evaluation**, use the

Move your mouse over the formula. As you move, different parts will become highlighted, and if you hold the mouse over a particular part, a tooltip will appear containing the result of that part of the formula when evaluated against the record chosen in ** Example evaluation**.

For example, in the formula "(1 + 1) * 2", moving your mouse over the "+" will highlight "1 + 1" and the tooltip will show "1 + 1 = 2". Moving over the "*" will highlight "(1 + 1) * 2", and the tooltip will show "(1 + 1) * 2 = 4".

If your formula contains errors, these will be reported in red text in the ** Errors section. Guidance text deemed useful for a function** in the formula will be shown. The relevant part of the formula will be highlighted in pink.

*Incomplete expression*- for example, "1 + 2 +" is incomplete, because it lacks the last value. Also, "(1+1 / 2" is missing a close bracket.*Function "..." has the wrong number of parameters*- for example, "SUM()" is wrong because SUM requires at least 1 parameter. The usage guide for the function will be shown.*Function parameters must be numeric*- you are using a non-numeric value as a parameter to a function which requires numeric parameters.*No such field or variable "..."*- You have entered a reference that does not exist. Use the*Insert field*button to insert field references, rather than typing them. Or use*Add/edit variables*then*Insert variable*to insert variable [2] references. If you are trying to insert a function, you have forgotten to add open/close brackets after the name.

Omniscope contains an extensive list of standard and specialised functions you can use to define Formula fields. In addition to the standard functions (with the same syntax) found in spreadsheets like Excel, Omniscope also contains a library of powerful DATASET functions that operate on one or more columns and can define the subset of data for which calculations should be be done based on values in other columns.

In the Formula Editor [5], choose *Functions list* to see the following dialog.

Each function is documented briefly in the functions list. For online documentation of available functions, including more detailed documentation of Omniscope-specific functions such as SUBSET(), consult our Functions Guide [6].

If you have questions about how to define formulae for specific tasks, consult the KnowledgeBase Useful Formulae [7] section. If you would like to propose additional functions you would find helpful, please post your ideas in the Forums [8] page.

**Links:**

[1] http://kb.visokio.com/formula-fields

[2] http://kb.visokio.com/defining-variables

[3] http://kb.visokio.com/formula-editor-functions-list

[4] http://kb.visokio.com/kb/functions-guide

[5] http://kb.visokio.com/formula-editor

[6] http://kb.visokio.com/node/152

[7] http://kb.visokio.com/node/185

[8] http://forums.visokio.com/categories/ideas