RE: Query Part 2: Working with Queries in The Raiser’s Edge®
Querying is such an important function in The Raiser’s Edge® that we are dedicating a three-part series of blogs to the topic to ensure your success. In Part 1, we reviewed Taking Control of Your Raiser’s Edge Queries. This entry focuses on getting the most from working with Query as the versatile tool in RE that allows you to group together similar records based on the parameters/criteria that you define!
If you’ve been working with Blackbaud’s The Raiser’s Edge 7 (RE) for any length of time, you probably know that the most efficient way to quickly and easily access bits of information in your database isn’t by manually sifting through hundreds, if not thousands, of records – but rather, by using a query. Query is the versatile tool in Raiser’s Edge (RE) that allows you to group together similar records based on parameters/criteria that you define. In addition to specifying the criteria, query management also enables you to control which fields to output, as well as the actual order that your results are displayed. With query, the possibilities are virtually endless and you have a great deal of control over the data you obtain and the format of that data when you view or export it.
QUERY TYPES
RE has several types of queries to help you narrow your results based on what type of records you are grouping together. These query types include Action, Appeal, Campaign, Constituent (which includes both Individuals and Organizations), Event, Fund, Gift, Giving Score, Individual, Job, Membership, Organization, Participant, and Relationship. As their names would imply, each query type is designed to give you criteria and output options that are most relevant to the type of data you are seeking.
In addition to these query types, you can also choose whether you’d like your query to be Dynamic – one that one that is refreshed each time it is run, or Static – one that is a snapshot in time. For example, you might choose to use a static query if you are grouping together new memberships as of a certain date for an event invite. However, you might choose to use a dynamic query for ongoing processes, such as including last month’s new donors in a monthly report. You can also change a query from dynamic to static and back again through Tools>Query Options.
Once you decide which type and format to use, you’re ready to begin defining your query parameters.
When you open a query, you’ll notice four tabs – Criteria, Output, Sort, and Results. The Criteria tab is where you’ll define specifically which records you want included in your results. The Output tab allows you to select what information is displayed on-screen after the query is completed. The Sort tab allows you to sort your results however you would like and includes the ability to multi-level sort. The Results tab actually displays the records that fit the criteria you chose and in the order you specified.
When you are building a query, you are defining a set of requirements (criteria) that each record must meet in order to be included in the results. These are referred to as filters when you are working in Query.
Each filter is very specifically defined using what’s called an operator. The available operators are: equals, does not equal, greater than, greater than or equal to, less than, less than or equal to, one of, not one of, between, not between, blank, is not blank, contains, does not contain, begins with, does not begin with, like, not like, sounds like, and ask. Keep in mind each operator might not be available or appropriate for every field. The query operators are each defined in greater detail in RE Help.
In the example below, we are building a query of donors from 2008 who made gifts of at least $100 or more and also live in the Charleston, SC area. The criteria tab is where we select this information and choose the operators of “between” for Gift Date and “greater than or equal to” for Gift Amount. If we decided we only wanted to see cash gifts, we could limit the results further by including a filter of Gift Type and use the “one of” operator to select all of the types of gifts we consider to be cash.
In addition to criteria operators, you can also use the wildcard characters of the question mark (?), asterisk (*), or brackets ([ ]). The question mark can be used to replace a single character, the asterisk can be used to replace a series of characters, and the brackets can be used to query for a range or to locate several characters.
When you have multiple filters in your query, you will notice that RE inserts what is called a combining operator between them. A combining operator tells RE if a record must meet ALL of the conditions specified (And), or if a record can meet any one of several conditions specified (Or). The default combining operator is “And”, but you can change this to “Or” by highlighting the filter and clicking the “Or” button at the bottom of the criteria tab.
Examples of using combining operators:
- You want to include gifts that are greater than or equal to $100 AND were given in the year 2008.
- You want to include all donors who have given more than $100 OR any donors from a particular state, regardless of their giving level.
Parentheses can be used in the event that you’d like to combine separate pieces of criteria into one filter – just be sure to close them out appropriately! In my example above, I used parenthesis to combine the first three filters into one. This makes RE look for records in this fashion (in plain English):
Give me everyone who gave a gift in the year 2008 AND their gift in that year was greater than $100 AND their gift in that year, which is over that amount, was one of the cash gift types. Once those people are gathered together, I only want to see the ones who have a preferred address with a city of Charleston in the state of South Carolina.
The fields available for both the output and the sort tabs are the exact same ones that are available on the criteria tab. You can choose to display as many, or as few, columns as you desire, and you can sort the rows in almost endless fashions. Playing around on these two tabs won’t impact the records included in the results, only how they are displayed for you, so tinker with it until you’re happy!
The results page displays all the records that qualified for your query, based on the criteria you chose. You can choose to print this screen, or you can export the results in a number of formats such as Access, CSV, Excel, MS Word, or even Lotus.
You may notice you have duplicate records in your results, which is perfectly fine, and actually unavoidable with some criterion selections. If you would prefer to not see the duplicates, you can choose to suppress them by going to Tools<Query Options<Record Processing<Suppress duplicate rows. Note that this option only works under some conditions – for a full explanation of when it will and will not work, please consult RE Help. There is also a detailed explanation of how to best avoid duplicates in your results in the first place.
There are some additional Soft Credit, and Matching Gift, processing options available in Query Options, on the Gift Processing tab.
In addition to using queries as stand-alone grouping tools, you can also use them to limit your results in the Mail, Reports, Export, and Admin modules of RE. This is helpful if you want to be sure only a specific grouping of records is included in your process. It is also very helpful if you have a large database, as using a query can speed up the processing of other RE functions by limiting in advance the number records the function is evaluating, rather than having it sift through your entire database. Narrowing down the records to be included in a process is often of critical importance if you are working in the Global Change or Global Delete functions in RE: Admin. To select a query for inclusion in most places in RE, you select: Include<Selected Records. You will be presented with the standard RE search screen you know and love. Once you locate the desired query, highlight it and choose “Open query.”
TIPS WHEN USING QUERY
- First, consider your end goal. What are you hoping to achieve? Which particular constituent type, or information, are you trying to gather? Identifying this information before you get started will ensure you select the appropriate type of query, and make it more likely you’ll extract the information you need on the first try.
- When building a query, it helps to start with the broadest piece of criteria first and work your way down to be more specific. For example: You’d like a query of all 2013 donors above $100 from Charleston, SC so you can send them a thank-you card. “Gift Date between 1/1/2013 AND 12/31/2013” would be your largest piece of criteria and therefore should come first, followed by “Gift Amount greater than or equal to $500,” finished by “Preferred City/State equals Charleston, SC.”
- For clean query results, be careful combining inclusions with exclusions (also called “negative operators”). It’s more logical to specify “one of” and “equals” versus “not one of” and “equals.” If you do need to combine inclusions with exclusions, using parentheses will help direct your criteria. If using parentheses, be sure to close them out or RE will generate an error. Also, be sure that you have your parentheses around anything you’d like grouped together (gift data, address information, etc.). In some cases, you may need to create two queries, and merge them, to get the correct results when mixing inclusions and exclusions. It can take some trial and error!
- A query is NOT a reporting tool; it is merely a grouping tool. If you are looking for fundraising totals or results, you should consider running or building a Report instead. You can always use a query to limit the results you want to see in the report. See “Reports: You Must KNOW In Order to GROW” for a list of the most helpful RE reports.
- If you are already a seasoned user of RE: Query and have hundreds of queries that need organization, consider QueryOmatic
QueryOmatic can help you identify, organize, and refresh, the queries that are currently being used as well as eliminate unused or outdated queries.
