Tips for Blackbaud eTapestry® Queries

Everyone who uses eTapestry needs Queries and Reports. I don't know of any user at an organization who uses eTapestry on a regular basis, particularly users who do day-to-day functions in the database, who wouldn't need Queries and Reports. When it comes to Queries, they're the backbone of eTapestry. In order to generate thank you letters, send a mass email, do a mass update to make some universal changes, or create a report, you must have a Query. So, these are pretty important. I think it's best to start with a few tips around queries since they are so critical to using the database.

The most important thing to remember about Queries is that they are not the end-all, be-all of data. That's more up the alley of the other feature I'll give tips for later this week—Reports. If we were to look at the definition of the word "query," we would see that the Oxford Dictionary says, as a noun, it's a question. As a verb, it's to ask something a question, especially to check validity or accuracy. So, that's what the eTapestry Query is. Using criteria selected by the user (i.e., who gave a donation within a date range), it can then search through the database to pull the accounts or the journal entries that match the criteria queried. Thus, Queries validate data based on your criteria.

Most important, it provides those accounts or entries needed for whatever it is you need to use it for (i.e., report, communication, etc.).

So, that's my first tip for Queries. Remember that Queries are used to search the database and return the information you want to return. Because they use criteria, there are a couple of other things to take into consideration as well. Within the Query's Criteria Options section, you will find "Criteria Matching" where you will find two options: Match ALL Criteria and Match AT LEAST ONE Criteria. Think of this as setting the Query's criteria selected to be separated by the word "AND" or "OR." For example, selecting Individual Transaction Received and Journal Entry Date with the option of "Match All Criteria" selected, you are telling the system that the donors have to meet the criteria of "Giving at least X amount per transaction AND that the gift entry had to come within a date range." What if you picked those exact same criteria, but you've selected "Match At Least One Criteria"? In that instance, you are asking eTapestry to return results for entries that have "at least X amount per transaction OR that there was some sort of journal entry in the entered date range."

Think about how that changes the results. In this scenario, you are going to end up with every transaction in the database as well as journal entries dated in the range you provided. Those entries from that date range will be any type of journal entry, not just the gifts. A much more common use of the "Match At Least One Criteria" option would be if you wanted to find accounts who marked with at least one of two different attributes. For example, you could find people who are marked as either volunteers or current board members. It's not always possible for people to be marked with both, so that setting can help you find one or the other.

My last tip for queries concerns the amount of criteria selected for your query. With Match All Criteria selected in that matching section, the MORE criteria selected, the FEWER results you will see. The best way to explain this is with an example: If you are looking for people who gave last year (Journal Entry Date + Individual Transaction Received), you might see 1,000 transactions. But if you add the criteria that you want to see the gifts to a specific FUND, now you might cut that number down to 300 results. Transversely, if you have Match At Least One Criteria, the MORE criteria you select, the MORE results you will see. So, if you are ever running a query and you think the results are too few or too many, it might have everything to do with the Criteria Matching selection and the number of criteria you've chosen.

These are my tips for you today. For more information about Queries and Reports, specifically how each one differs and what you do with these features, I recommend checking out the webinar I hosted earlier this year regarding Querying and Reporting in eTapestry. You'll learn more about these tips, as well as a few more!

Keep your eyes peeled here in Community later this week for a post with some tips around Reports. Until then, keep on eTapping on!