Choosing The Right Data Return Type
Published
Have you ever wondered why the option you choose for the Data Return Type in queries changes your results? If so, you're in luck because today we're talking about how you make the right choice! Let's start by breaking down exactly what it does to a query.
The Data Return Type is how you tell the query what kind of information you want to see in your end results. There are two basic options, Accounts or Journal Entries, plus a few others that you may use for specific purposes. You can see a full list of Data Return Types and their definitions in our help documentation.
I would like to see a list of constituents that donated in 2015 but did not donate in 2016. I’m calling these “lapsed donors” and I’m going to focus some time on them so that my organization can try to recapture them in 2017. In the end result, I want to see details about the gifts the lapsed donors gave in 2015, such as how much they gave and to what Approach, as it may be different for each donation they gave in 2015. For this project, I will need 2 queries and 1 compound query.
The best part is, I don't even need a Report for this project! I can customize my query preview to include the columns I want to see about the gifts they gave and then export to Excel.
Do you have any tips to share on how you pick the right Data Return Type for a query? Share them in the comments!
The Data Return Type is how you tell the query what kind of information you want to see in your end results. There are two basic options, Accounts or Journal Entries, plus a few others that you may use for specific purposes. You can see a full list of Data Return Types and their definitions in our help documentation.
The information that your query returns is integral to the other processes that use queries, such as Communications, Reports, and Mass Updates. The right Data Return Type will depend on how you’re going to use the query. For example, if I want to send a thank you letter to everyone that gave last year the query should return Journal Entries. That way, we can personalize each letter and merge the amount each person gave into their letter. When a query returns Journal Entries, you get detail about the journal entry that meets your criteria plus basic account information for the person that gave. If my query only returns their Account the letter won’t be able to show the amount my organization received from them because that information lives in the Journal.
As another example, let's say I need to pull a list of all constituents that primarily live in the state of Indiana so that I can email them about a special event in their state. In that case, I will need to return Accounts because I don't necessarily need to see any specific entries in their Journal.
As another example, let's say I need to pull a list of all constituents that primarily live in the state of Indiana so that I can email them about a special event in their state. In that case, I will need to return Accounts because I don't necessarily need to see any specific entries in their Journal.
Data Return Type also plays a big part in Compound Queries. If we're going to subtract, add, or intersect two queries they need to be able to work together to get the results that you need. There are some combinations of Data Return Types in Compound Quereis that will likey work, sometimes work, and never work which we've outlined in our help documentation. You can use that as a guide to figure out what Data Return Type is best depending on your scenario. Let’s talk through an example!
I would like to see a list of constituents that donated in 2015 but did not donate in 2016. I’m calling these “lapsed donors” and I’m going to focus some time on them so that my organization can try to recapture them in 2017. In the end result, I want to see details about the gifts the lapsed donors gave in 2015, such as how much they gave and to what Approach, as it may be different for each donation they gave in 2015. For this project, I will need 2 queries and 1 compound query.
- Query 1 should pull all donations that we received between 1/1/2015 – 12/31/2015. Since I want to see details about the donations, like the amount and Approach, I need this query to return Journal Entries.
- Query 2 should pull all donors that gave between 1/1/2016 – 12/31/2016. The important part here is that I need this query to return Accounts because these are the people that DID give and I don’t want to see them in my end result. Also, if this query had Journal Entries and we wanted to subtract it from Query 1, nothing would be subtracted because they contain Journal Entries from two competely separate time frames; they wouldn't have anything in common to subtract! They may, however, have Accounts in common which is why we chose that Data Return Type.
- The Compound Query (pictured below) is going to take Query 1 minus Query 2 and will return Journal Entries. This will leave me with the Journal Entries from 2015 for donors that did not give in 2016.
The best part is, I don't even need a Report for this project! I can customize my query preview to include the columns I want to see about the gifts they gave and then export to Excel.
Do you have any tips to share on how you pick the right Data Return Type for a query? Share them in the comments!
News
ARCHIVED | Blackbaud eTapestry® Tips and Tricks
04/25/2017 9:11am EDT
Leave a Comment