Using Queries

Queries give you the ability to find the data you are looking for. You can create a query and save the query definition so that the query can be run again with a single click. You can also save the results of a query to a list for further processing, such as for mail merge.

Some screens, such as the Lead and Contact Home screens, have a simplified query interface with a few of the more common fields available to be searched. For example, the main Contact screen shown below allows you to query based on first name, last name, and city.

For more query options, you can click on the "Advanced" link next to the "Run/Save Query" button. This will give you the ability to search on all available fields. There are two different query modes, standard and advanced. The default query mode is controlled by a user preference setting. The standard query screen looks like screen shown below:

This screen allows you to search using query by example. You fill in the values you want to match and then run the query. The query will find any results where the specified fields start with the specified value. For the screen shown above, the query would find all contacts with a last name starting with "Smith", a city that starts with "Dallas", and a type that starts with "Alumni". The query will only return results where all the conditions are met.

The advanced query screen looks like the screen shown below:

This screen allows you to perform more complex queries. You have the option of using the following conditions:

• STARTS WITH - finds records where the field value begins with the specified value
• = - finds records where the field value exactly matches the specified value
• CONTAINS - finds records where the field value contains the specified value anywhere in the string
• ENDS WITH - finds records where the field value ends with the specified value
• < - finds records where the field value is less than the specified value
• > - finds records where the field value is greater than the specified value
• <= - finds records where the field value is less than or equal to the specified value
• >= - finds records where the field value is greater than or equal to the specified value
• NOT EQUALS - finds records where the field value does not match the specified value
• BETWEEN - finds records where the field value is between two specified values. When using BETWEEN, you should enter two values separated by a comma.
• IN - finds records where the field value is in a list of specified values. When using IN, you should enter a list of comma separated values
• NOT IN - works the opposite of the IN condition

In addition to being able to enter different condition operators, you also have the ability to search for values in related tables. For example, the query shown above lets you search for contacts with a first name that starts with "SMITH", who live in "Dallas", who are "ALUMNI", and who have given gifts greater than $500 between January 1, 2000 and December 31,2003.

When you run a query, you can save the query definition so that it can be reused easily. If you enter a name in the "Save Query Definition" field, the query definition will be available in the Query drop down field on the associated list screen. Query definitions are dynamic, meaning that the next time you run the query, you may get different results if records have been added, deleted, or modified.

You can also specify that the results of the query should be saved to a list, which can be used in further processing, such as mail merge. This is done by filling in the "Save Results To List" field before running the query. Saved lists are static, meaning that the results are stored in the list when the query is run. Any inserts, updates, or deletes to the database will not affect the results of a saved list.