The Query Windows in Connected are powerful search windows that combine aspects of reports, a ledger screen, and a search window into a single screen. They can be customized, saved as templates, printed, and exported to a spreadsheet. The information displayed can be drilled down to see the source transactions. The Query windows can be used to supplement or in many cases replace some commonly viewed reports.
These are the Query windows available to use in Connected:
- Customer Query
- Vendor Query
- Inventory Item Query
- Job Query
The following information are detailed examples of how to use the Customer and Inventory Item Query windows. However, all Query windows work in a similar way.
TABLE OF CONTENTS
- User Access for Query Windows
- Example: Using the Customer Query Window
- Example: Connected Views with the Inventory Item Query Window
- Customizing the Inventory Item Query Display Columns
- Using the Advanced Find
- Other Actions
User Access for Query Windows
User access to these search windows needs to be granted via User Privileges, as shown below using the Customer Query. Each Query is separate and needs to be checked in its respective module. See Creating Connected User Accounts and Access Privileges for more information.
Example: Using the Customer Query Window
To open a Query window, either select from the drop down menu in each module, or use the Icon on the Connected Desktop, as shown in the following examples with reference to the Customer Query window:
TIP: All Query windows in Connected work the same way
When the Customer Query window is first opened, it will show a list of all customers, sorted by the current balance owning, as shown in the following screen. This is the "Customer Query Default View". Learn more about Connected Views
The Customer Query window has a series of quick lookup options, as detailed here.
- Customer Range: Select from All Customers, Range of Customers, Customers with Analysis Code, or Range of Sales Reps.
- Status: Select from Active, Hold, and/or Inactive customers. This setting can be used in conjunction with all other quick lookups.
- Balance: Select from Any Amount (default selection), Equal To, Greater Than, Less Than, or Not Equal To and enter a dollar value.
- Last Payment: The last payment can be set to a preset range or custom one.
- Added: The date a customer was added can be set to a preset range or a custom one.
- Last Sale: The date of the last invoice.
The following screen shows a sample quick lookup query of "Active" Customers, Balance greater than "$5,000", with a Last Sale date "This Year".
The following actions can be performed on the records displayed in the Customer Query.
Drill Down: A user can drill down to any Customers window by double-clicking that line. This will open up the corresponding Customers window, and display the information in the Activity Ledger. From there, further drill down is available to the source transactions.
Sort: Sort the displayed data by a specific column by clicking on the column heading. An arrow will appear on the top of the column heading, indicating the sort. Click the same column again to invert the sort.
Refresh: As data is changed (customer invoices entered, posted, or paid) the Refresh button will update the query display window with the most current information. The window can also be closed and reopened to refresh the display.
Example: Connected Views with the Inventory Item Query Window
Connected Views is a feature that is available in all Query windows. Learn how to use Connected Views
There are a number of ways to use the Connected Views feature in the Inventory Item Query Window.
Example: A saved view, filtering for a specific item, showing all purchases within the last 90 days.
Customizing the Inventory Item Query Display Columns
The columns in any Query window can be customized to suit individual user needs. There are many different columns to choose from. The following steps walk through how to change the columns displayed.
Select Change Columns from the Action Menu, as shown in the following screen.
The following screen will open up with all the columns that are available to choose from. Check the columns that are needed to appear in the query window.
TIP: The order of the columns in the Query window can be changed by clicking on the desired column option, holding the mouse button, and dragging the column name into the new position in the list.
NOTE: When Connected is closed, the columns will be saved in the exact order they were in when the user logged off. Each user can have their own unique column settings based on their user name and password in conjunction with Connected Views.
Using the Advanced Find
The Query windows are equipped with a powerful Advanced Find option which allows multi-tiered queries to help filter large lists for very specific results.
Below is an example using the Inventory Query Window Advanced Find
To use this feature, select the Use Advanced Find checkbox as shown in the following screen.
To define a query:
- Choose a field to query on. In the following screen, three fields have been selected, "Item No", "On Hand Qty", and "Analysis Code". More fields can be added by selecting the + sign to the right of the query entry.
- In this example, the Item No can be queried by selecting "Begins with", "Equal to", or "Contains". For example, if you query the Item No for any code that beings with 'E', all items that have codes that begin with 'E' in the Item No field will appear in the results window.
- To drill down into an item, double click on it to open the Inventory Items window.
- The following screen shows the query results of: Begins with "E", On Hand Qty Greater Than '100', and Analysis Code Is "DIST". This Query filtered the list from 19 items to only 2.
Exporting Query Results
To export the results of the Item Query window, select Text File or Spreadsheet from the Print menu as show below.
Pinning an Inventory Item
To pin an inventory item click the "Pin" icon when the Inventory Item line is selected, as shown in the following screen.
If an inventory item has already been pinned, the "pin" icon will display this when the inventory item line is clicked on.
Learn more about how Connected Pins work.
Adding/Viewing Notes using the Query Window
Using the Customer Query as an example, you can add or view any Notes entered in a Customer window, within the Query window. If a customer has a note, it will show as below. You can click on the Note icon and view the Note, without having to open a separate window. You can also create, or modify existing Notes from within this window.
Viewing Inventory Item Record Info from the Query Window
To view the record info for an inventory item, click on the inventory item line in the query window and then click the Info (i) icon, as shown in the following screen.