Query by Formula Overview
The user should note that the Sapphire Tools menu from Query by Formula, through to Custom List Report has the same tools on it throughout SapphireOne when any list is being displayed. These are referred to as Common tools.
The SapphireOne Query by Formula tool allows the Sapphire user to execute a query on a transaction inquiry subset using a specified custom formula. Query by formula is similar to a Detailed Query (Control Y on Windows Command Y on MacOS ).
Keyboard shortcuts are designed to make routine computer functions quicker and easier. The problem is, most users don’t know them! Shortcuts serve to eliminate additional steps that might involve your mouse or selecting extra menus.
Shortcuts
(Command Shift H) Hide/Show Toolbar.
(Command Shift Y) Advanced Find.
(Command Shift ?) Search Function.
Use the Control key for PC. Use the Command key for Mac.
| KEY | Function |
|---|---|
| A | Select All |
| B | Show Last |
| C | Copy |
| D | Actions |
| E | Show All |
| F | Find |
| G | Show Subset |
| H | Hide App |
| I | Detailed Order |
| J | Omit Subset |
| K | Sapphire Tools |
| L | Look |
| M | Modify |
| N | New |
| P | |
| Q | Quit |
| R | Layout Query |
| T | Sapphire Query |
| V | Paste |
| W | Close Screen |
| X | Cut |
| Y | Detailed Query |
| Z | Undo |
| / | New Line Item, Contact etc |
| FUNCTION KEY | Pages on the Page Drop Down Menu |
|---|---|
| F1 | 1st Page (Usually Details) |
| F2 | 2nd Page (Usually More Details) |
| F3 | 3rd Page |
| F4 | 4th Page |
| F5 | 5th Page |
| F6 | 6th Page |
| F7 | 7th Page |
| F8 | 8th Page |
| F9 | 9th Page |
The screen below is a simple example asking SapphireOne to display all transactions with a sequence number greater than 24200.
- Save and Load – Any queries set up may be saved on your local drive using the
button. We recommend you create a folder for these queries within your SapphireOne Application folder. These same queries at a later time may be utilised using the 
button. Once either of these buttons are selected the standard options for saving or opening a file are displayed. - Transaction Selection – SapphireOne has the ability to query transactions in any table.
- Query In Selection – Selecting this checkbox forces SapphireOne to perform the current query using the displayed transactions only.
Query by Formula is designed for writing search conditions that involve operations such as:
- Performing operations on alphanumeric strings and making evaluations of them.
- Searching the results of date computations.
- Searching the results of arithmetic computations.
Rules and Restrictions
- if you want to start a new query de-select (remove the tick) from the Query in Selection checkbox. SapphireOne will not perform your query in the currently displayed transactions if this option is selected.
- The formula must be a Boolean expression, i.e. returning a true or false value.
- Formulas can only be one logical line long without carriage returns. Do not press the Return key at the end of each line. The editing area will automatically wrap if the statement extends beyond the viewport.
You can review our Blog and YouTube channel for additional information and resources on SapphireOne ERP, CRM and Business Accounting software.