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, as well as 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 several 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 Member 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.
1.Click Add to create a new criteria row.
2.The editing area will enable, and it contains three main sections:
•From Previous - shows how the current criteria will link to the previous, if applicable.
•This Criteria
oData Type - the general type of data you want to query. This setting will determine the available Field Name options.
oField Name - the specific field to query
oOperator - the way the stored value in the selected field will be compared to the Value. The operator choices will vary based on the selected Field Name
oValue - the value to compare to data stored in the selected Field Name. The way a Value is entered will vary based on the selected Field Name. Dates have special options for choosing a specific or relative date; lists will display the relevant list to select a value; numeric fields will only allow numeric values, etc.
•To Next - this shows how the current criteria row will link to the next, if applicable.
3.After making your selections, click OK to add the new criteria to the list.
4.You can also Edit an existing criteria row, Copy it as a new row, or Delete it, using the other icons below the list.
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 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 your criteria together.
Consider this set of criteria:
... the search will not find anything. This makes sense if you think about it, because no member has a city set to two different things at the same time (in this case, Dallas and Houston). There is only one city field on an address and it only accepts one entry.
If the criteria was changed to:
...the search will work, because now it can find members in Texas with a city of Dallas OR a city of Houston -- it doesn't have to match both cities, but still has to match the state.
Using Parenthesis
You may notice that the second example also includes parenthesis around the city rules. Parenthesis are very important if you use an OR to join your criteria rows, especially when there are other parts of the criteria besides the part using the OR.
If, in the above example, we removed the left and right parenthesis, the query would now produce unpredictable behavior, because it would essentially "read" as "state of Texas and city of Dallas or city of Houston without regard to the state". Thus, records could be returned for Dallas, Missouri; Dallas, Texas; Houston, Florida; and Houston, Texas. Parenthesis avoid ambiguity. A good rule to follow is, if there is an or anywhere in the criteria, use parenthesis around it to make sure it is treated as one unit.
Wildcards and Search Examples
Click to see several other Sample Searches
Address Criteria
When using MemberTies Professional, if you use address fields as part of your criteria, i.e., "City = My Town", and your membership records often include multiple addresses, there are two fields that may be important:
•Active Address - by default, the search assumes you only care about the active address. Use this field to tell it to use the inactive address.
•Address Type - if you are only looking for matches on addresses marked as "Work" or "Summer Home" or whatever, use the address type to specify that.
If you don't use this criteria, the system will automatically assume you want to use the active address whenever it needs to look for address data. That means if a member has a Home Address in "My Town" and a Summer Address in "Your Town", the record won't be displayed unless the Home Address is marked as Current on the membership record.
Include in Mailings, Include on Reports
By default, any mailing label report will automatically honor the "Include in Mailings" flag, and other reports will do the same for the "Include on Reports" flag. This means if a record has both of these options turned off, it will not appear on reports. If you want to override this behavior, use the appropriate flag in your search criteria and specify exactly which setting is needed.