Using Connected Query Windows

 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

Another detailed example of a Query window is here Using the Item Query Window - An Example

The following information is a detailed example of how to use the Customer Query window. However, all Query windows work in a similar way.

Some common uses of the Customer Query window include:

- Creating a dynamic Customer Aging report that is refreshed in real time, with drill down options to view individual records.

- Making a Sales Rep listing, showing which sales reps are assigned to different customers. 

- Exporting Customer Contact information to be used for various tasks such as email lists.

- Checking to see what tax codes and ship to addresses have been entered

TABLE OF CONTENTS

• User Access for Query Windows
• How to Open a Query Window
• How to use the Quick Lookup Options in a Query Window
• What Actions Can be Performed on the Records in a Query Window
• Adding Columns to a Query Window and Saving a View
• Using the Advanced Find 
• Other Actions
  • Exporting Query Results
  • Pinning a Record
  • Adding/Viewing Notes using the Query Window
  • Viewing Record Info from the Query Window

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.

How to Open a 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

How to use the Quick Lookup Options in a Query Window

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".

Note:  The yellow symbol indicates that the search criteria has changed from the last saved view.  You can save a new "view" here, or save over an existing one.  Connected "Views" allow users to save up to 99 different searches or versions, and they are user specific.  

Learn how to use Connected "Views" for Ledger and Query Windows here

What Actions Can be Performed on the Records in a Query Window

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.  

In this example, the Balance column is sorted Highest to Lowest.

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.

Adding Columns to a Query Window and Saving a View

In this example, a column for "YTD Sales" is added to the existing Customer Balance by Aging Period "View"

Step 1 - Select the "Views Menu" icon on the top right hand side of the Ledger/Query window and select "Change Columns"

Step 2 - Check off the "YTD Sales" field in the available column listing

Step 3 - Drag the YTD Field up to the required position. In this example, it was moved so that it was the first column after the customer phone number. Click "Change" to save the changes.

You will now see the YTD Sales column in place.  

Since this was a change to the previous "View" note the Yellow Symbol on the left side.  

This change can be saved to the previous "View" or a new one created.

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 Customer Query Window, using a saved "View" called "Customers with Invoice Surcharges".  

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, "Invoice Surcharge", "YTD Sales", and "Price Level".  More fields can be added by selecting the + sign to the right of the query entry.

In this example:

•  Invoice Surcharge can be queried by selecting "Is" or"Is Not".
• YTD Sales can be queried by selecting from "Equal to", "Greater than", "Less than", or "Not Equal to"
• Price level can be queried by "Is" or "Is Not"

The following screen shows the query results of: 

• Invoice Surcharge is "Yes"
• YTD Sales "Greater than" 7000
• Price Level is "1"

This Query filtered the list from 34 Customers to only 3

To drill down into a customer record, double click on it to open the Customer Window

Other Actions

Exporting Query Results

To export the results of a Query window, select Text File or Spreadsheet from the tool bar on the right side as show below.

Pinning a Record

To pin a record, in this case a Customer, click the "Pin" icon when the Customer line is selected, as shown in the following screen.

If a Customer has already been pinned, the "pin" icon will display this when the Customer 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 Record Info from the Query Window

To view the record info for a customer, click on the customer line in the query window and then click the Info (i) icon, as shown in the following screen.