The Most Powerful Query You May Ever Build
Published
Queries and reports are a major part of eTapestry, but they can also be one of the more complex areas of the database. Today I’m going to show you a basic, yet very powerful query that you can build and use as a stepping stone for other queries. For some you may know how to build this query, but you may not have thought about why you build it that way.
I always picture building queries and reports like a brain teaser puzzle. Some people like to do crossword puzzles, some do Sudoku. But for me, I enjoy building queries and reports. If I think of it like a game it definitely makes building queries more fun.
A query is simply just asking, “what/who are you looking for?” On the eTapestry Support Team, one of the biggest things we do is help customers build queries. We are always asking what is your end goal? What are you trying to find? This is us trying to determine how the query should be created. One of the most common responses we get goes something like this:
“I’m trying to find all donations from last month.”
So just by that one sentence, we can break down some, what I call, verbal criteria:
So we know we need at least two criteria in our query. Donations are typically money that your organization has received. As a user you would enter Gifts, Recurring Gifts, Payments, etc. to track money being given (received) to your organization. In a query this means we need to use the Individual Transaction Received criterion to pull transactions.
When you look at Individual Transaction Received, there is a drop down that defaults to Greater Than Or Equal To. This is good because we want to pull all received amounts. So we would want to set it to Greater Than Or Equal To $0.01. This means pull anything equal to a penny. Some organizations do anything greater than or equal to a dollar ($1.00) because most constituents don’t give under a dollar.
But we only want donations made during a particular month and we haven’t told the query what month yet. When you create journal entries there is always a Date field that is filled in on every entry. We need a criterion that allows us pull transactions in a date range based on that particular Date field. This means we need to use Journal Entry Date.
This field is a bit more self-explanatory. It defaults to a Custom Range which allows you to type a start and end date. This gives you the control you need to ensure you are only pulling donations in a specific range.
So now if we go back to the list that showed our two criteria, we can make it look more like this:
Verbal Critiera = Query Criteria
One other major field we want to look at is the Data Return Type in the upper right corner of the query. By default it is always set to Accounts. In order to select the proper Data Return Type, you have to think ahead a bit and ask yourself “what am I doing with this data?” Do you just need to see who gave donations in a particular date range? If so, then leave the Data Return Type on Accounts. Do you need to see what or how much was given? Then you’ll want to change the Data Return Type to Journal Entries.
When you create transactions in eTapestry you add Journal Entry to the Journal page of a constituent account. Gifts, Recurring Gifts, Payments, etc. are all different types of Journal Entries. The amount, date, fund, etc. is all recorded on those Journal Entries. So if you need to see details such as the amount, date, fund, etc. in a report or letter, then your query needs to be set to Journal Entries.
You very well may just want to see who gave and pull a list of names and/or address information. What they gave isn’t so much as important as just who those people are. In this situation you can leave the Data Return Type on Accounts. Just because we are using criteria that are journal entry specific doesn’t mean we have to have a Data Return Type of Journal Entries. So keeping the end goal in perspective really helps you know which Data Return Type to select.
So even though the query above is only using two criteria, you’ll find yourself building itYour needs change from one day to the next, so you may start to add more criteria or less depending on the situation. For example, you may only want to see donations from a date range that have Fund A. You would simply build the query above and add the Funds criterion so you can check Fund A. Or you might use the query to isolate a group of donors/donations, and then use additional queries to add/remove them from a larger group of results.
Here is a video that walks you through setting up the query above.
Here is a link to a Knowledgebase solution that contains the written steps: How To Query On All Donations Within A Specific Date Range (https://www.blackbaud.com/kb/index?page=content&id=BB739914)
Here are some resources on creating different types that are similar to the one above, or uses the one above to find more complex results:
I always picture building queries and reports like a brain teaser puzzle. Some people like to do crossword puzzles, some do Sudoku. But for me, I enjoy building queries and reports. If I think of it like a game it definitely makes building queries more fun.A query is simply just asking, “what/who are you looking for?” On the eTapestry Support Team, one of the biggest things we do is help customers build queries. We are always asking what is your end goal? What are you trying to find? This is us trying to determine how the query should be created. One of the most common responses we get goes something like this:
“I’m trying to find all donations from last month.”
So just by that one sentence, we can break down some, what I call, verbal criteria:
- Needs donations
- Donations have to be in a particular date range
So we know we need at least two criteria in our query. Donations are typically money that your organization has received. As a user you would enter Gifts, Recurring Gifts, Payments, etc. to track money being given (received) to your organization. In a query this means we need to use the Individual Transaction Received criterion to pull transactions.
When you look at Individual Transaction Received, there is a drop down that defaults to Greater Than Or Equal To. This is good because we want to pull all received amounts. So we would want to set it to Greater Than Or Equal To $0.01. This means pull anything equal to a penny. Some organizations do anything greater than or equal to a dollar ($1.00) because most constituents don’t give under a dollar.
But we only want donations made during a particular month and we haven’t told the query what month yet. When you create journal entries there is always a Date field that is filled in on every entry. We need a criterion that allows us pull transactions in a date range based on that particular Date field. This means we need to use Journal Entry Date.
This field is a bit more self-explanatory. It defaults to a Custom Range which allows you to type a start and end date. This gives you the control you need to ensure you are only pulling donations in a specific range.
So now if we go back to the list that showed our two criteria, we can make it look more like this:
Verbal Critiera = Query Criteria
- Needs donations = Individual Transaction Received
- Donations have to be in a particular date range = Journal Entry Date
One other major field we want to look at is the Data Return Type in the upper right corner of the query. By default it is always set to Accounts. In order to select the proper Data Return Type, you have to think ahead a bit and ask yourself “what am I doing with this data?” Do you just need to see who gave donations in a particular date range? If so, then leave the Data Return Type on Accounts. Do you need to see what or how much was given? Then you’ll want to change the Data Return Type to Journal Entries.
When you create transactions in eTapestry you add Journal Entry to the Journal page of a constituent account. Gifts, Recurring Gifts, Payments, etc. are all different types of Journal Entries. The amount, date, fund, etc. is all recorded on those Journal Entries. So if you need to see details such as the amount, date, fund, etc. in a report or letter, then your query needs to be set to Journal Entries.
You very well may just want to see who gave and pull a list of names and/or address information. What they gave isn’t so much as important as just who those people are. In this situation you can leave the Data Return Type on Accounts. Just because we are using criteria that are journal entry specific doesn’t mean we have to have a Data Return Type of Journal Entries. So keeping the end goal in perspective really helps you know which Data Return Type to select.
So even though the query above is only using two criteria, you’ll find yourself building itYour needs change from one day to the next, so you may start to add more criteria or less depending on the situation. For example, you may only want to see donations from a date range that have Fund A. You would simply build the query above and add the Funds criterion so you can check Fund A. Or you might use the query to isolate a group of donors/donations, and then use additional queries to add/remove them from a larger group of results.
Here is a video that walks you through setting up the query above.
Here is a link to a Knowledgebase solution that contains the written steps: How To Query On All Donations Within A Specific Date Range (https://www.blackbaud.com/kb/index?page=content&id=BB739914)
Here are some resources on creating different types that are similar to the one above, or uses the one above to find more complex results:
News
ARCHIVED | Blackbaud eTapestry® Tips and Tricks
11/12/2014 9:00am EST
Leave a Comment