Showing results for 
Search instead for 
Do you mean 

Creating and Saving Advanced Queries #SageACT

by on ‎11-05-2010 11:50 AM (8,474 Views)

banner Sage UniversitySubmit your Tip

In the last several weeks, we have looked at a variety of predefined lookups that may be used to locate recently modified or synced data. This week you’ll learn how to build a custom query (or lookup) and save it. Saving custom queries is a really powerful feature because you can re-use them in so many ways. Saved queries can be used as the basis of:


  • Group or Company membership
  • Smart Task filter criteria
  • Custom menu/toolbar commands
  • Sync sets
  • Expediting common lookups

This week’s tip will focus on creating and saving an advanced query, and then over the next week or two you’ll see just how useful a saved query can be!


To Create an Advanced Query:


1.  From the Lookup menu, point to Advanced, and then click Advanced Query.




2.  In the Search for box, select Contacts, Groups, Companies, or Opportunities.


3.  To specify the criteria, do the following:

        a.  From the Type list, select a record type. (This option may not be available depending on your starting point).

        b.  From the Field Name list, select a field to search.



        c.  From the Operator list, select an item, such as Equal To. Operators are logical conditions, such as Starts with, Greater than, or Does Not Contain Data, used to limit or focus the data in the search. The Operator list values change depending on the Field you selected.

        d.  In the Value field, type or select from the list the specific data you want to locate

        e.  Click Add to list.


4.  To add another criteria statement to the query, repeat steps 3b – e. If you are building a query with multiple criteria, then complete the following steps. Otherwise, skip to step 5.



        a.  To change the And/Or setting for criteria statements: In the middle pane of the dialog box, click in the And/Or column for the first criteria statement. Select "And" between two criteria statements to indicate the records you are looking for must match both criteria. Select "Or" to indicate the records must match one of the criteria statements.



         b.  To group criterion together, enclose criteria statements in parentheses: Click in the left parenthesis column [  ( ] and select an item from the list. Click in the right parenthesis column [ ) ] and select an item from the list. Statements enclosed in parentheses are processed first in the query search.


5.  To see the list of records that meet your selected criteria, click Preview. Previewing the results lets you see the number of records before you run the query and exit the query dialog box. If the results do not seem correct, change the criteria.



6.  To run the query, click OK.


7.  If you would like to save the query for future use, click Yes when prompted “Do you want to save the query?” or No to run the query.


8.  If prompted, select a lookup option (Replace, Add To or Narrow) for the query. Click OK. A message prompt appears if you have previously run a lookup and that set of records is the active set.


  • Replace lookup is the default setting. This means replace the records in the existing lookup.
  • Add to lookup appends records to the existing lookup.
  • Narrow the lookup applies the new search criteria to the existing lookup.

9.  The appropriate list view displays the records.


To Save Your Query:



1.  Click the Save tool.


2.  In the Save As dialog box, type a file name.


3.  Make sure that the file type is correct:


  • QRY - contact query
  • CRY - company query
  • GRY - qroup query
  • ORY - opportunity query

4.  Click Save.


Queries are saved to the Queries folder in the database supplemental files. For more information about database supplemental files


To Open a Previously Saved Query:



1.  In the Search for field, select the query's record type (Contacts, Groups, Companies, or Opportunities).


2.  In the criteria dialog box, click File, Open.

Click the Open tool.


3.  In the dialog box that appears, select the query. Click Open.


4.  Do one of the following:


  • Click Preview. to see the list of records that meet the criteria.
  • Save the query with a different name.
  • Edit the criteria.
  • Remove criteria.
  • Click OK to run the query.


by Jackie Olson
on ‎06-30-2014 09:03 AM

Is there a way to save a Companies look-up as a dynamic group (in my case, our one day shipping zone customers)? 
Our customers are all imported into the Companies tab, not the Contacts. The groups section only seems to allow the addition of contacts, and contact lookups. Thank you!