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 Insert variable except you will need to define the variable beforehand using Add/edit variables [2].
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 Data > Undo edit or press Ctrl+Z to undo the entire formula editing session (from the point you opened the formula editor until you pressed OK).
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 OK. The function and sample usage will be inserted into the formula at the cursor position. This will typically introduce errors which will be highlighted, since you must change the sample argument/parameter names for real values, references or expressions. More... [3]
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 Record in data spinner to select a record. Providing your formula has no errors, the result will be shown to the right, along with the data type. If you move the formula editor to one side, the same record will be highlighted in the views.
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.
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