How Queries Work
UNDER REVIEW
Simple Query Construction
For any query, you need to first answer two questions:
- What is the "criteria" that will determine what the records have in common?
- What type of records do you want in your results?
Criteria
A query’s criteria are the filters that are applied to separate the records you want from all the other records in the database. There are many different ways to apply these filters, depending on the type of query you have chosen to create. By choosing the query type, you are actually applying the first filter to your query.
Query Types
Aegis CRM offers over twenty different query types to help you in retrieving the results you need. It is very important that you understand how these different types operate to ensure that you receive the results you are expecting.
For example, let’s say you have a partner named Joe Smith in your database that has given three gifts in the current year. If you create a contact query for current year donors, Joe Smith will appear once in the results. However, if you create a gift query for gifts in the current year, Joe Smith will appear three times in your results.
For now, we will concentrate on the most commonly used types; Contacts, Activity, and Gifts. To view definitions and descriptions for all of the query types available, click here.
Contact Query Type
The contact query type is used to group together partners that have something in common. Sample criteria for this type of query would be:
- Partners who live in the state of Maine
- Partners whose average gift is $25 or higher
- The date and/or amount of their last gift
Activity Query Type
The activity query type is used to group records together based on their inbound activities, which would include any responses that did not include a donation. Criteria for this type of query would be:
- Partners who responded to a specific mailing
- Partners who responded to any mailing over a specific time period
- Partners whose gifts were recorded in a specific batch
- Partners who made a pledge
Gift Query Type
The gift query type is used to group gift records together based on something they have in common. Criteria for this type of query would be:
- Gifts for a specific appeal
- Gifts for a specified time period
- Gifts in a specific batch
Operators
Before we begin creating a query, it is important to understand how "Operators" work. This is a term to describe to your database how the questions you are asking should be interpreted. As an example, let’s use our first question:
"How many of our partners have given a gift of $100 or more in the past twelve months and live in California?"
The word "And" in this question is an operator. We are telling the database that we only want to see those partners who match both sets of criteria. Think about how different your results would be if the question were asked like this:
"How many of our partners have given a gift of $100 or more in the past twelve months or live in California?"
Most likely, you would receive many more gifts in your results using the "Or" operator, since the question would be answered by providing all of the partners meeting the giving criteria, as well as any partners living in California, regardless of how much they have given.
In addition to the "And" and "Or" operators, our sample criteria also contains a value operator. The phrase "have given a gift of $100 or more" is interpreted by Aegis CRM’s Query Engine as "Greater than or equal to". Depending on the type of the field being evaluated, Aegis CRM offers many different types of evaluation operators. Most of these values are self-explanatory. However, especially when creating queries with multiple criteria, you need to make sure that you are including all available values. For instance, if you choose to only include records where the gift amount is "Greater than $100", instead of "Greater than or equal to $100", people who have given exactly $100 would not be included.
Copyright ©2017 Aegis Premier Solutions |