- Features by Edition
- Latest Features
- Getting Started
- Data Sources
- Server Topics
- Integration Topics
- Video Tutorials and Reference
- Featured Videos
- Demos and screenshots
- Online Error Report
- Legal-Small Print
- Why Omniscope?
Defining and Editing Formulae
Using the interactive, visual Formula Editor
The formula text box
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 references can be inserted using Insert variable except you will need to define the variable beforehand using Add/edit variables.
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. 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...
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 for full documentation of all functions.
Formatting your formula
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.
Seeing the results
While editing your formula, you can see the results in two ways:
Previewing the result of the whole formula
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.
Previewing the result of parts of your formula
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.