Detailed Query - Boost Your Data Analysis with SapphireOne Detailed Query

Detailed Query Overview

SapphireOne Detailed Query enables the execution of sophisticated queries on your SapphireOne tables and fields. This query editor is the third of the four query tools SapphireOne provides for searching your Inquiries. The feature is accessible from ‘Options’ menu > Detailed Query. When the Detailed Query function is selected the below dialog search box will be displayed, allowing a results list to be filtered to an advanced level.

Detailed Query looks for records matching the criteria specified in your query and returns a selection of records for a given table query changes the current selection of a table for the current process and makes the first record of the new selection the current record.

When setting up a Detailed Search Query, the user must first make some selections from three menus in the toolbar at the top of the screen.

• Options (Gear Icon) – When the Options (Gear Icon) button is selected, SapphireOne will display a drop down menu with the following options:
  • Save – The user may save any detailed queries they have created.
  • Load – Re-load the detailed query at a later date. 
  • Copy Formula To Clipboard – The user may temporarily copy any formula to the clipboard for retrieval at a later time. 
  • Reset – Selecting the Reset option simply resets the query to where it started from.
• Selection – This menu has a number of options, including:
  • Create New Selection (default action) – SapphireOne searches in all the records of the tables and replaces the original current selection to display the records found. 
  • Search in Selection – SapphireOne only searches in the records of the original current selection and then replaces this selection with the records found.
  • Add to Selection – SapphireOne searches in all the records of the tables and adds the records found to the original current selection. Any records found that are already part of the current selection are displayed but not duplicated. 
  • Remove from Selection – SapphireOne searches in all the records of the tables and removes any records found from the original current selection.
• Recent Queries – When this button is selected SapphireOne will display a menu allowing access to any recent queries. This will enable users to quickly run commonly used queries.

Building a Detailed Query

To create a standard query in the Detailed Query editor, simply set up a line in the form of “field, operator, value”.

1. To designate the field, use the hierarchical list found to the right of the editing area. SapphireOne will display a list of all tables in the data file with the current table detail on screen opened ready for the user to make their selections. This is shown in the example image on the left below.
   • By collapsing the currently expanded folder, the user will be able to view all of the tables within the data file and select from any desired area to be queried upon. This is shown in the example image on the right below.

3. Type the value you want to search for.
   • In a Text or Alpha field you can use the wildcard character ‘@’ at the end of the value to specify a “Begins with” search.
   • If the field you selected is associated with a choice list, SapphireOne displays the list and prompts you to select a value. If the field you selected is a Boolean field, SapphireOne displays a pair of radio buttons.
4. If you want to specify a multiple query, click on the  button to add a line. SapphireOne duplicates the contents of the row corresponding to the button. If you want to add a query line using a formula, hold down Alt (Windows) or Option (MacOS) while you click on the add button. This point is detailed in the Query by formula section.
   • If you want to add a query line using a formula, hold down Alt (Windows) or Option (MacOS) while you click on the add button. This point is detailed in the Query by formula section.
5. Select the desired conjunction (And, Or, Except).
   • By default, SapphireOne sets the And conjunction next to the line that is added. 
6. Repeat steps 1 through 3 in order to specify new criteria.
   • When you build a compound query, SapphireOne evaluates the simple queries in the order in which they appear in the Query editor (i.e. from top to bottom). There is no precedence among the conjunctions. In other words, And does not have priority over Or. Therefore, if you use more than two simple queries when building the compound query, the order in which you enter the simple queries can affect the results of the query. As you build the compound query, you can modify existing parts of the query by clicking the line you want to change and clicking a new field or operator, or typing a new value. You can remove a simple query by clicking on the   next to it. Be careful, deleting lines cannot be undone.
7. Choose the destination of the query using the Selection action menu: Create new selection, Search in selection, Add to selection, Remove from selection (see previous section). 
8. (Optional) To save the query to a disk file, select Save… from the edit menu.   
9. Click on Query to launch the search.

Simple and Compound Queries  

You can search on one or more fields. A query on one field is called a simple query. For example, the search “Last name is ‘Jones’ is a simple query. When you do a simple query, SapphireOne examines the contents of one field when searching the database. 

A query on two or more fields is called a compound query. When you do a compound query, you combine separate queries using a conjunction operator. The conjunction operator tells SapphireOne how to combine the results of the individual queries. There are three conjunction operators:

• And – This operator finds all the records that meet the two conditions simultaneously. For example, the query “Find all the employees who work in the engineering department and who make over $50,000” will find the records of only those engineering employees who make over $50,000.
• Or – This operator finds all the records that meet either of two simple queries. For example, the query “Find all the employees who work in the engineering department or who make over $50,000” will find the records of all the people in the engineering department, as well as all the people who make over $50,000 regardless of the department in which they work. 
• Except – This operator is the equivalent of “not.” The query “Find all the engineers except those who make over $50,000” will exclude the engineers making more than $50,000.

To perform a query combining several criteria, you can click as many times as needed on the add line button .

The conjunction operators let you create compound queries such as “Find the plumbers in Sydney and who have commission rates in excess of 30 per cent and who had sales volume less than $20,000.” The figure below shows this query being specified in the Query editor:

When this query is executed, SapphireOne finds all the plumbers in Sydney who may be getting high commissions for low volume sales. Additional examples of the uses of comparison and conjunction operators are given for each search method.

• If additional lines are to be added to the query select the + button to add a line and inversely, the – button to delete a line. Please note that when a line is to be deleted using the – button there is no alert displayed prior to deletion. 
• When further lines are added to the query there is an additional drop down menu option at the start of each line to enable the establishment of relational rules. The options are And, Or, or Except.

You can review our Blog and YouTube channel for additional information and resources on SapphireOne ERP, CRM and Business Accounting software.