Further Exploration of the "Lookup" Function

There is little doubt that ACT! is a great tool for collecting information. During the normal course of any given business day, just by using the various functions of the program, histories of our activities and correspondences are created. Now that all this information has been collected, how do we locate what is meaningful to us at any particular moment? This ACT! Tip of the Week will explore the "Lookup" function and some methods for retrieving information.

The first step in the process of searching an ACT! database typically starts with a "Lookup." From the "Lookup" drop-down menu many data fields (i.e. Company, Contact, State, etc.) are available with a click of the mouse. From the dialog box that then pops up, the item selected from the drop-down menu may be changed to any data field in the database. Also from this dialog box, by clicking one of the radio buttons, your search may replace the existing group of contact records, refine the existing group (narrowing the current group of contacts), or broaden the contact group (adding new contact records to the current group).

This same "Lookup" dialog box will even allow you to "Lookup" contact records with, or without, data in any specific data field. Selecting the radio button "Empty field" will search for contact records wherein the selected data field is empty, or, conversely, contains any data ("Non-empty field"). When preparing for a mail merge these functions can be quite handy.

While the functions available from the "Lookup" dialog box are very useful they may not return the specific information you are looking for. So let's look at another area of functionality within ACT!.

One of the drop-down menu items under "Lookup" is "By Example." Selecting this item starts the more powerful "query" process within ACT!. Not to worry, you won't have to write any code. Not yet anyway. But there are a couple of things to keep in mind when using the "By Example" lookup.

A "By Example" query allows you to lookup contact records using one, or several, data fields for each search. This type of query also allows you to lookup contact records by multiple items within one data field. Combinations of any of these criteria are also possible. Further, from the "Query" drop-down menu, you can "Specify Query Sort" to indicate the order in which the contact records are displayed after your query is run.

Remember, if you enter search criteria in more than one field the "&&" (and) operator is automatically used between these fields. For example, if you enter "Boston" in the "City" field and "Prospect" in the "ID/Status" field, the query will return all prospects in Boston. If you want the relationship between fields to be "Or" (||), you'll have to create an "Advanced" query. Now we are getting close to writing code so we'll leave "Advanced Queries" for another time.

When using "By Example" to search for multiword items always enclose the words in quotation marks. For example, to find New York, enter "New York".

To find all contacts that have any data in a specific field, type an asterisk (*) in the field.

Queries are not case-sensitive. Therefore you do not have to enter text with the correct capitalization. You can enter either "XYZ COMPANY" or "xyz company" and the search will find all contacts at XYZ Company.

When you enter a value in a date or numerical field, the default operation is "=" (is equal to). For example, if you enter 12/14/00 in the "Last Reach" field, ACT! will lookup contacts who have that exact date in the "Last Reach" field. To find all contacts who have been reached after 12/14/00, enter ">12/14/00" in the "Last Reach" field. Date range lookups were discussed in the Last ACT! Tip of the Week (12/19/01).

Finally, if you think that you may want to run your query with some regularity it may be saved for access later. Before running the query (green exclamation point) pull down the "File" menu and do a "Save as." After launching the "By Example" lookup, you can open your saved query from the File menu. Or, if you are really adventurous, create a "Macro" and place a "Custom Command" to run it on your toolbar. Hey, there's another topic for another time!