The ability to assign tracking codes would be worthless if there were no way to select the people identified. Accordingly, the Advanced Search button on the main menu screen opens up a Record Selection Tool for this purpose.

This tool lets you select records based on membership status, city, state, precinct, issue interests, activities, etc. It also lets you omit records with bad addresses, no ZIP code, etc.

For instance, to send an email to everyone in Massachusetts:

1. Type 'MA' into the state field.
2. Click the Search button.
3. Click the OK button to pull the search results up to the main menu.
4. Click Copy Emails.
5. Compose a new message in your email program and paste the email addresses into the "BCC:" field. (Blind carbon copy is good etiquette for mass email.)

To send a paper mailing to all your Campaign Supporters identified as "1's" or "2's", you would:

1. Click VID.
2. Enter "1,2" in the Campaign Supporters field and click OK.
3. Click the Omit Bad/Expired Addresses button to omit people with addresses marked as incomplete or expired.
4. Click Search.
5. Click OK to pull results up to the main menu.
6. Click Print, choose the Laser Labels option, and click OK to print.

The date fields on the search screen allow you to specify ranges of dates, like 1/5/02...2/5/02 (note the three ellipses to indicate a range.) If you type two slashes, as in //, and press the tab key, ODB understands that it should replace the components of the date with the current day, month, and year.

For instance, if you want to send customized donation slips to members who expire before December 1, 2004:

1. Enter "<12/1/03" in the Expires field.
2. Click Omit Bad/Expired Address.
3. Click Search.
4. Click OK, then click Print and choose the Donation Slips option, and click OK.

Note that the Donation Slips first need to be customized to your organization, a process that is described in section B of this manual.

The query language used is known as the "Jet" dialect of Structured Query Language (SQL for short). This language is a web standard, for details you can go to http://tinyurl.com/GENERATEIT .

The Logic of Queries. One general note about Record Selection for those of you who are familiar with other databases: the search that you generate is a logical "AND" of the criteria that you have turned on by checking or filling in various search boxes. This means that if you fill in several boxes simultaneously, your search may return no results at all. If your membership list includes ten people from Springfield, but none of your six board members are from Springfield, you could check off "Board" under activities, then enter "Springfield" in the "City" field, click Search, and get no results.

The most powerful feature of the Record Selection Tool can be seen when you use it in conjunction with tracking codes. Each of the four kinds of tracking codes may have several checkboxes turned on within, and ODB combines these together (as a logical "OR") into a single logical "criterion." What this means in plain English is that if you check the Activity code boxes for "Board Member" and "Staff" and "Advisory Board Member" within the Advanced Search Tool, the program will look for people in your database who match one or more of these activities. And if you also check the "Constituency" code boxes for Women and Youth, the program will restrict the search, limiting the results to people identified as belonging to Women or Youth constituencies, but who also happen to be a member of your Board, Advisory Board, or Staff.

Payment Searches. You can select people based on their donation history so that you can generate reports, send mailings, or export donation or payment data to your spreadsheet program for further analysis.

[an image will be inserted here]

1) If you select the "Payments" choice from the Search For list in the Advanced Search Tool window, you can then enter a donation range or a date range. By doing so, you can easily generate a list of everyone who gave between $50 and $100 in the month of December 2001, for example.

2) Payment selection also allows you to specify a `example, you can search for all web donations of $50 or more since 1/1/01.

There are five ways of viewing the results of your donation search, available in the "Generate Report of" pull-down menu of the Advanced Search Tool window. You can view the payments sorted by payment amount, by date, or by the donor names. Or you can view payers by total paid sorted by the total amount they gave (highest to lowest), payers sorted by the last name, or payers sorted by the number of payments they have given.

Payment search results can be saved to a text file and exported to a tab or comma-separated file by clicking the Save This Data button. The fields available for export include name, title, suffix, address, city, state, ZIP, etc. to generate acknowledgements.

Advanced Queries. If you need to do a more advanced query than the preset options provide, you can select the "View Custom Query" checkbox to reveal a large text box showing the underlying "structured query language" or SQL. Taking cues from the computer code that ODB displays in the large text box in the custom query area, it is not too hard to further customize your ODB queries.

For example, to broaden a search to include additional records, select the first box: "Add SQL to broaden your search." If you already have 40 people selected but you also want to include all 300 people in your database who live in California, you could type State="CA" in this box. This will augment what is already selected above.

If you want to restrict your search to narrow down your results, you can select the second box: "Add SQL to further restrict your search." If you already have 50 people selected and you only want to see the 5 who are from California, you would type exactly the same SQL in this box: State="CA".

You can restrict to specific donor IDs. For example, let's say you have a set of 10 donors on the Main Menu (returned from an advanced search), and you want to exclude one of them from a mailing you plan to do (so you can write a handwritten note instead). To do this:

1) Click the donor's record in the Main Menu to highlight it.

2) Right-click on the Advanced Search button (or click the triangle drop-down arrow).

3) Select the choice: Advanced search, excluding selected item. (The Advanced Search Tool appears.)

4) Enter additional query information as needed.

5) Click Search. The ID of the person you first selected will not be included in the results.

The list of field names that can be used in your custom SQL is provided under section 7 of the Help button for this screen. To hide the View Custom Query boxes, simply click the check-box again.

More examples of SQL:

If you want to restrict your search to people who do not want "money calls," just type in the box: Nocall.

Or if you want to restrict the search to people who do want those fundraising calls, just type: Not nocall.

If you type: lastgift > #5/25/02# in either the restrict or broaden box and leave everything else blank, you will get all the people who have donated since May 25 of 2002. This makes it easier to send those people thank-you notes.

If you type: comments like '*picnic*' in one of the boxes, you search for everyone in the database who has a comment that says something about their involvement in a picnic you have held. Note that you can use the built-in comment search field in the Record Selection Tool as well.

If you are searching within the 'Groups' in your database, you could type: Name like '*latino*' or Name like '*hispanic*' to find groups with one of these words in their name.

If you have set up a user-defined field (which has a text format) and wish to search based on it, you will need to query using the custom name you gave the field (in the userfieldname setting). You might type something like: position like '*Executive Director*' if you called that field "position".

Saving and Loading Queries. You may also save a query to a text file and load it back in, using the Save Query… and Load Query… buttons that are visible in the bottom right when 'View Custom Query' is checked. (Sometimes you have to click this check box twice to see them.) If you want to find donors from New England to gauge the results of a regional membership drive, you will not have to type in the query twice!

ODB provides you with twelve built-in queries for use in database maintenance. These are found when you click Load Query. For example, you could select everyone with a bad address who has an email address, and then send those people a request to update their address. The built-in queries are:

• Former good addresses to research.
• People who have email addresses.
• People whose address could be looked up.
• People whose ZIP codes could be looked up.
• People with a phone number.
• People with a state but no city.
• People with incomplete mailing addresses who have email addresses.
• People with no phone number.
• People whose email addresses have expired.
• People whose addresses were marked as expired in the last three months.
• People who have been members but don't have email addresses.
• People who have been members and have had their email address expire.

ODB also automatically saves a copy of the last query that you ran, either from the Advanced Search Tool (after you clicked OK) or as a result of the Search button on the Main Menu. Just look inside your queries folder for a file labeled lastquery.txt.

Sorting Results. When you do a search in ODB, the results are initially sorted by Last Name and then by First Name. To change the sort order, click the button labeled Last Name where it says: Displaying xxx items, Sorted by: [last name] and change the sort order to anything you like, or even remove last name entirely from the sorting specification. Or you can sort your list in reverse alphabetical order, or by the order of the donation "Ask Amount", by the date people joined, or by the date someone's membership will next expire. The sort feature is extremely flexible: you can sort by up to four levels. Try it!

When you sort by a category, if any of your records are missing that piece of information, the records with blank fields will appear first followed by the others in alphabetical order.

To sort by the date of last donation:

1. Select your donors.
2. Remove First Name from the sort order by clicking on "First" and changing it to 'nothing'.
3. Then click "Last" and change it to 'lastgave'.
4. The order will change automatically without pressing the Search button again.

Note that you can adjust the width of the columns of the grid by clicking on the dividers in the top row, and dragging left or right.

Shortcuts for Sorting. The columns of the grid that shows the results are labeled Name, Email, Address, ZIP, and ID. If you click the top of any of these columns, the results will be resorted by this column as described below:

Name - Sorts by first name, and then last name.

Email - This shortcut currently does an alphabetic sort. Eventually we hope to implement a feature to sort email addresses by domain name.

Address - Address sorting allows you to group records by geographic unit. First the records are sorted by state, then by city, then by street name, and then by street number. It should be able to generate meaningful results even with complex street numbers like 232B and 101-11. However there is currently no provision to automatically normalize street names in this version of ODB. If someone lives on "Broadway St." s/he will appear to ODB to be on a different street from someone who lives on "Broadway Street."

ZIP - This is the most common way by which people sort information, as it is helpful in doing bulk mailings. However, the Post Office now requires bulk mailings to be sorted by Area Distribution Center (not ZIP) so we recommend exporting the ODB data to a third party program, as described in section B of this manual, if you want to print your own bulk mail labels.

ID - ODB also lets you sort by ID number. This provides you with a list of the records in the order in which they were originally entered into your ODB database.