Query Builder Overview in Harbortouch Retail

Description: Within the Harbortouch Retail system, you can build reports to show information that you specifically need to run your business effectively. To help you do this, review how to build and load a query, as well as some specific query examples to help understand how to use queries in the system.

• Query Overview
• Specific Query Examples

---

Query Overview

There are three main components to every query: Category, Conditions, and Values.

• The Category used to refine your query, where the values differ depending on the type of query
• Conditions for the Category selected above:
  • = EQUAL TO
  • != NOT EQUAL TO
  • < LESS THAN
  • > GREATER THAN
  • <= LESS THAN OR EQUAL TO
  • >= GREATER THAN OR EQUAL TO
  • $ CONTAINS
  • !& DOES NOT CONTAIN
  • B BEGINS WITH
  • !B DOES NOT BEGIN WITH

Note: You can determine the formula by hovering over these symbols or selecting the drop-down in the Current Query section on the right-hand side of the screen.

• Values can be typed in manually, or you can press the Load button to populate the field with a list of all available values.

Building a Query

To build a query for reporting, log in to the system using the Admin credentials and select the BUILD QUERY button situated in the following locations based on the type of query you are looking for:

• From the Inventory Menu (or for Purchase Orders and Special Pricing Rules)
  1. Select Inventory from the home screen or along the top navigation bar.
  2. Open the Inventory tab.
  3. Press Build Query.
     
     
• For Reporting Queries
  1. Log in to the POS system and select Reports from the home screen or on the top navigation bar.
  2. Select the report you wish to query from the Select Report box on the left side of the screen.
  3. Once the report is highlighted, press Build Query in the middle of the window.
     
• Age Verification
  1. Select Setup from the home screen or along the top of the navigation bar.
  2. Choose the General Settings tab and press Age/ID Verification in the lower right corner.
  3. The Minimum Age Rule Editor will populate, and the Build Query button is located at the bottom of the screen.
     

Once you have selected the values for your query, click the Add >> button to add it to the list of chosen options on the right. This section can also be used to edit existing queries.

Note: In this example, the query from the previous step is ready to be added to the list. Already added to the list is a query for all items whose quantity on order is less than 77.

With multiple options selected, use the AND/OR drop-down box to select whether you would like to have your items meet all the added options at once or any single one of them.

Important: You can have multiple selections and/or rules within a query, but too many may cause an error. If you receive an error, you will need to rebuild your query in a different manner.

You can save your query for future use by entering a name into the Query Name field and pressing Save Query. This will place the query into the field labeled Pre-Saved Queries.

Loading a Query

You can load previously saved options by selecting a saved query from the list and pressing Load Pre-Saved Query.

Once you have entered one or a number of query options, the Count Matching Records button will display the number of items that match your selection, without actually displaying the items themselves. Press Done.

Specific Query Examples

Below are examples of queries, such as Age Verification, Reports, Purchase Orders, and Special Pricing Rules.

Age Verification

A store may only need to apply to a specific department, but for a business such as a Liquor Store, everything may need to have age verification applied. An easy way to set up that application to all is Sku!=(Not EQUALTo)0. To access the Query Builder for Age Verification, log in to the POS system using management credentials, then:

1. Select Setup from the home screen or along the top of the navigation bar.
2. Choose the General Settings Tab and press Age/ID Verification in the lower right corner.
3. The Build Query button is located at the bottom of the screen.

Note: If Age Verification is not yet enabled on the POS system, follow the steps found in the Enable Age Verification article for a full walkthrough.

Reports

Queries can be used to pull a Transaction & Payment Details report filtered by Payment Type. The below example will show how this can be accomplished for Cash ONLY transactions. To build the query:

1. Log in to the POS system and select Reports from the home screen or on the top navigation bar.
2. Select the report you wish to query from the Select Report box on the left side of the screen.
3. Once the report is highlighted, press Build Query in the middle of the window.

If you would like to set up the report and query as a Quick Launch button under Favorite Reports,  enter a Query Name and click Save Query under the Current Query section of the Query Builder.

On the Reports page, go to Set up Favorite Reports to edit your Quick Launch Report code. This is set up as ExactReportName{Query}SavedQueryName. For this example, the code would be Transaction & Payment Details{Query}Cash Trans. Make sure to test the button! The query will show as successfully pulled in the report:

Purchase Orders

If you would like to auto-add items to the Purchase Order, you may use the Query Builder to target specific quantities (ex: when the QOH is less than 2 for a specific Vendor).

Special Pricing Rules

To manage grouped Special Pricing (2 for $1, 10 for $10, etc.), build a query based on a Discount. The below query is being built for the items that are grouped under “2 for $5.00”.