Query By Formula - Apply Custom Formulas to Your Transaction Inquiries

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.

Query by Formula Overview

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 a valuable tool for increasing productivity. They are designed to make routine functions quicker and easier by eliminating the need for additional steps that might involve your mouse or selecting from menus.


Use the Control key for Windows. Use the Command key for MacOS. 

KEY Function
(CNTRL / CMD Shift H) Toggle Show Toolbar
(CNTRL / CMD Shift Y) Advanced Find
(CNTRL / CMD Shift ?) Search Function
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
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 Print
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

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.

Was this helpful?

Previous Article

Apply Formula

Next Article

Quick Report