Using Connected Query Windows

Knowledge Base · Ledger and Query Windows

Applies to: All Supported Versions  |  Platform: Connected Desktop and Connected on Demand

ARTICLE CONTENTS

• 1. Overview
• 2. User Access for Query Windows
• 3. How to Open a Query Window
• 4. How to use the Quick Lookup Options in a Query Window
• 5. What Actions Can be Performed on the Records in a Query Window
• 6. Adding Columns to a Query Window and Saving a View
• 7. Using the Advanced Find
• 8. Other Actions
  • 8.1  Exporting Query Results
  • 8.2  Pinning a Record
  • 8.3  Adding/Viewing Notes using the Query Window
  • 8.4  Viewing Record Info from the Query Window
• 9. Related Articles
• 10. Reference Links

1. Overview

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

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

3. 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:

When the Customer Query window is first opened, it will show a list of all customers, sorted by the current balance owing, as shown in the following screen. This is the "Customer Query Default View". Learn more about Connected Views

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

5. 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 Refreshbutton will update the query display window with the most current information. The window can also be closed and reopened to refresh the display.
  

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

1. Select the Views Menu icon on the top right hand side of the Ledger/Query window and select Change Columns.
   
2. Check off the YTD Salesfield in the available column listing.
   
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 Changeto 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.

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

8. Other Actions

8.1  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 shown below.

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

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

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

9. Related Articles

• Using Connected Ledger Windows
• Connected "Views" for Ledger and Query Windows 12.X
• Use Case: Sales Ledger - Customer Sales by Location
• Connected 11.1 Update Change Log
• Using the Inventory Item Query Window - An Example
• Analysis (Category) Codes

10. Reference Links

• Using the Item Query Window - An Example
• Creating Connected User Accounts and Access Privileges
• Connected Views
• Connected "Views" for Ledger and Query Windows
• Connected Pins