Entering Advanced Search Criteria


Table of Contents   Previous Page  Next Page    

An advanced search can be an extremely powerful way of extracting very specific data from the database.  An advanced search is available on membership, group, and activity lists, and is the only search used for reports and documents.

Unlike a basic search that gives you a set of predefined fields to pick from, a search using advanced criteria is built one field at a time, with each field represented by a new row on the advanced search criteria list.

Each row represents a specific criteria or "rule" that a record must meet in order to be displayed. If you create membership criteria that says Last Name = "Smith", then only membership records with "Smith" in the Last Name field will be found.

This is an example of the advanced search on the Membership List:

This search will require four things from every record displayed:

The member must have a positive dues balance (i.e., greater than zero)
The End Date on the record must be prior to today's date
The city (on the current address) must be Chicago.

 

Building Criteria

 

Criteria is created row-by-row, with each row representing another criteria (you can think of it as a "rule") that must be true for a given record to appear on the report. For example, if you are running a membership report and use a criteria like, Last Name = Smith, then only membership records with "Smith" in the Last Name field will be on the report.

 

1Start a new criteria line by clicking the Add icon. A new blank row will appear.

 

2Click the dropdown arrow in the Field Name column to open the selection window. Then, choose the field whose data you want to check with this criteria. For example, if this criteria will restrict the report to members in a certain city, expand the Address section and choose "City."
 
Note: Custom Fields will only appear in this list if they have been defined as available to be used as Criteria.

 

3Click the dropdown arrow in the Operator column to open the selection window. Then, choose the method that will be used to compare the data to the selected field.  For example, if you want the city value to be equal to, "MyTown", you would choose "Equals" ( = ).

 

4Click the dropdown arrow in the Value column to open an appropriate data-entry window. Then enter the appropriate value to be compared to the selected Field.

 

More than One Criteria

 

When you have several criteria rules you want checked the system needs to know if you mean "this criteria AND that criteria" or whether you mean, "this criteria OR that criteria" since there is a big difference. To control this, there is an option to specify AND or OR at the end of each row of criteria, to tell the system how to link that criteria rule to the next.

Also remember that when using multiple criteria, every piece of the criteria (at least those with "and") must be true for each record being shown on the report.  Therefore, if you use criteria like:

 City = MyTown AND
 City = YourTown

... the report won't show anything. This makes sense if you think about it, because no member has a city set to MyTown AND YourTown at the same time.  There is only one city on an address.

If the criteria was changed to:

 ( City = MyTown OR
 City = YourTown )

...the report will work, because now it can find members with a city of MyTown OR a city of YourTown -- it doesn't have to match both at once.

Using Parenthesis

 

The advanced criteria list includes options for left and right parenthesis. Parenthesis are very important if you use an OR to join your criteria rows. Consider this criteria:

 

State = Texas AND

(City = Mytown OR

City = YourTown)

 

This will require a state of Texas, and either a city of Mytown or a city of YourTown, because the AND refers to all of the criteria grouped by the parenthesis.  However, if the parenthesis were omitted, the criteria would look like this:

 

State = Texas AND

City = Mytown OR

City = YourTown

 

This can produce unpredictable behavior, because now it essentially says, "state of Texas and city of MyTown, or the city of YourTown without regard to the state".  Thus, records could be returned for YourTown, Washington, and YourTown, Oregon, as well as YourTown, Texas.

 

 

Special Expressions using LIKE and NOT LIKE Operators

 

% The percent sign is used in conjunction with the word "LIKE" to search for data that begins, ends, or contains specific text or numbers. For example, if you selected Last Name as the field, and LIKE as the operator, you might enter any of the following in the Value field:

 

"Smith%" to get "Smith", "Smithe", or "Smithington.
"%burg" to get "Sederburg", "Frankenburg", "Hidelburg"
"%der" to get "Sederburg", "Fender", and "Derston"

 

You can also use the opposite of LIKE, which is NOT LIKE, to reverse the search requirement.

 

Complex Expressions Using LIKE and NOT LIKE Operators

 

If you are comfortable with the concepts of LIKE and NOT LIKE, the following table provides several advanced examples of ways to use wildcards and expressions for even more precise queries. Note that each expression below would be preceded with LIKE or NOT LIKE.

 

Expression

Searches for...

Sample Return Data

Mc%

Names that begin with the letters "Mc"

McEvoy

%er

Names that end with "er"

Brier, Miller, Weaver, Rayner

%en%

Names containing the letters "en"

Pettengill, Lencki, Cohen

_ish

Four-letter names ending in "ish"

Fish

Br[iy][ae]r

Brier, Bryer, Briar, or Bryar

Brier

[M-Z]owell

Names ending with "owell" that begin

with a single letter in the range M to Z

Powell

M[^c]%

Names beginning with "M" that do not

have "c" as the second letter

Moore, Mulley, Miller

 

As illustrated above, the % (percent) symbol will be replaced by any number of characters, whereas the _ (underscore) symbol can only be replaced by one character.

 

Text within brackets ( [ ] ) will be required unless preceded by the carat (^) symbol, in which case the text is disallowed.

 

Sample Searches - Click to see several examples of search criteria.

 

Address Criteria

 

When using MemberTies Professional, if you use address fields as part of your criteria, i.e., "City = MyTown", and your membership records often include multiple addresses, you may want to use a special criteria field called "Address to Use".  With this criteria, you can tell the report which address you want it to look at when matching the City value.

 

If you don't use this criteria, the system will automatically assume you want to use the Current Address whenever it needs to look for address data.  That means if a member has a Home Address in "MyTown" and a Summer Address in "YourTown", the record won't be displayed unless the Home Address is marked as Current on the membership record.

 

Include in Mailings, Include on Reports

 

These two checkboxes are available on membership records in case you want to use them as report criteria.  You may select either of these fields for criteria if you wish to use them.  If you do not use them, i.e., if you don't include the "Include on Reports" criteria, it won't matter whether the checkbox is selected or not on a member record.

 

To Save and Reload Search Criteria

See Saving Advanced Criteria.