Tips for Compound Queries in Blackbaud eTapestry®

Geoff Arbuckle

Last week, I published two blogs about some basic tips I always want to make sure users of eTapestry know when it comes to Queries and Reports. This week, I will be covering more specific topics within those two spaces. First, let's talk about a couple of tips for Compound Queries.Compound Queries can be moody. They can be complicated to think about and complete. They can even be scary to some. While it is sometimes difficult to know exactly when a Compound Query is needed, I hope that these tips will help you understand how to work with them.



Let's start by explaining what Compound Queries are. Compound Queries take two different Queries within eTapestry and will either add them together (Add), compare them to find the similarities (Intersect), or subtract the results in one Query from another (Subtract).



Take, for example, this scenario: I have a list of accounts who gave last year in one Query. In another Query, I have a list of accounts who gave this year. Adding these two Queries together will show all the accounts who gave in either year or both years. Intersecting these two Queries will show you who gave in BOTH years (thus leaving out those who only gave in one or the other). Subtracting the two queries depends on what is selected as Query #1 or Query #2. If Query #1 are the accounts who gave Last Year, subtracting Query #2, the accounts who gave this year, will leave you with those donors who gave last year, but not this year. If reversed, and Query #1 is set to show which accounts gave this year and you subtract those accounts who gave this year, it will show you who gave this year but didn't give last year.



The same concepts can be applied to Queries that find people with certain User Defined Fields selected. Let's say you have a field that marks someone as a volunteer and you have another that marks that Blue is their favorite color. If you have two Queries created, with one having the list of those volunteers, and another having the list of "Blue" folks, then adding them together will compile all these people into one list showing they are either volunteers, or fans of the color Blue, or both. Intersecting will find the people who are both volunteers and fans of the color blue. Subtracting one from the other will remove anyone who is not both.



When it comes to subtraction, the key is to strategically select what will be listed as Query #1. The idea is that subtracting out results form Query #2 will leave the results from Query #1 that do no have the characteristics of Query #2. So in the two examples above, it very much matters which Query is selected as Query #1 and which one is selected as Query #2 based on your desired end goal with the Compound Query's results.



One more thing to remember is what the Data Return Type is of each query in use, especially in intersecting or subtracting scenarios. These two functions of a Compound Query require EXACT matches to be able to either find the similarities or remove them. This means you need to be very mindful of using two different lists of journal entries in a Compound Query. Let's say you have a list of Contact journal entries from accounts your fundraisers entered to track conversations around major gifts. Then you want to compare that to a list of recent donations of a certain amount to see if those large donations came in through those efforts your fundraisers took in those conversations. You will be looking at two completely different sets of journal entries. There will be no way for you to use the Intersect or Subtract options. However, if both queries were set to Accounts in their Data Return Types, then this will work, as it can match the accounts as a whole.



Using the previous example of lists from "last year" and "this year," the same applies. If these are not lists of accounts but lists of the actual gifts within those date ranges, being able to intersect the two lists or subtract one list from the other will not work. So there has to be exact matching information in both queries. Mixing the Queries' Data Return Types can work but could also cause some headaches based on what the Compound Query's Data Return Type is set to and how you are asking these two Queries to interact. Subtracting an Account return type Query from a Journal Entry return type Query can work regardless of the Compound Query's Data Return Type. Intersecting mix return type queries can be a bit of a headache, and I would, frankly, advise against you doing this.



Generally, I recommend a majority of your Compound Queries use two queries that have the same Data Return Type. The Compound Query can then later be used as the Starting Query for another query to narrow own exact journal entries that you wish to see if you so wish. The one exception is if you select Subtract, then choose Query #1 to be a Journal Entries return type Query and Query #2 has its return type set to Accounts. Those types of mixed return type Compound Queries work, but will typically be a minority of Compound Queries.



Those are my tips for you today. I recommend you check out a recent webinar I did about Compound Queries called Combining and Comparing Query Results with Compound Queries in Blackbaud eTapestry. I go into a much deeper dive into these above concepts. Keep your eyes peeled here in Community as I will be back later this week with another blog post talking about working with Aggregates in eTapestry Reports. Until then, keep on eTapping on!