We’ve all been there.  You’re creating an eTapestry query, but the results of your query don’t match your expectations.  The criteria and specifications of your query are set, but the data in your final output aren’t what you’re looking for.  Whether the data in your final output is significantly or slightly off from your expectations, any error will force you to go back to the beginning and try again.  However, if you know common causes for query errors, you can fix your queries quickly instead of redesigning them from the ground up.

This is a list of five common eTapestry query challenges and how to fix them, but this isn’t a comprehensive list.  Each query you build will have little nuances that may not match this exact list.  Keep in mind that not every challenge mentioned in this post can be corrected by the recommended solutions, but they are good places to start a query troubleshoot if and when the output from your query doesn’t match your expectations.

Challenge #1 – No data returned in results

A query that returns no data is the ultimate “head-scratcher.”  This generally occurs when the query you’ve built is too restrictive.  This means the criteria and specifications you’ve set for your query are so specific that no data in your database match those criteria and specifications.

Solve this query challenge by checking your “Match” criteria selection.  When no data is returned in the query, it is most likely caused by the selection of “All of My Criteria” when your intention is to return data for “Any of My Criteria.”  “All of My Criteria” is AND logic and looks for accounts or journal entries that match all criteria you set for the query, whereas “Any of My Criteria” is OR logic and looks for accounts or journal entries that match any one of the criteria you set for the query.  If you’ve loaded your query with three criteria and select “All of My Criteria” you are asking eTapestry to find data that matches all three of the criteria you set.  No data is returned because the criteria of the query are too restrictive.  Flipping the “Match” criteria selection may solve a query that returns no data.

If your query has many criteria, returns no data, and the “Match” is set to “Any of My Criteria,” consider simplifying your query criteria.  Loading a query with multiple criteria can make the query too restrictive and return no data as well.

Challenge #2 – Missing data in results

What can you do when query results are missing data?  This outcome underscores the importance of testing the results of a query no matter how simple the query is.  Data missing from query results means your criteria isn’t inclusive of the data you want.  You may be expecting to see a certain account or journal entry in your query results, but can’t find it.  When this happens, you need to test the application of your query criteria and query specifications to the piece of data you’re expecting to see.  This test should be completed manually with a small sample set of test data (i.e. search for the record you hope to see in your results and check to make sure it matches the criteria and specifications of your query).  The answer to “why” that piece of data isn’t included in the query results can lead to the required modification for the query criteria and specifications.

The most common reasons to miss data in query results are making improper translations from your intended query design and the actual criteria listed in your query in eTapestry.  If you are missing inclusive criteria for the set of data you are expecting to see in the query results, then add the query criteria that are missing.

Also check to make sure your starting criteria are inclusive of the data you are looking for.  If your starting criteria don’t include the data you are looking for, then it doesn’t matter what criteria you add to the query, that data won’t be included.

Challenge #3 – Transaction data is blank in a report

The data return type in an eTapestry query can be a forgotten element of query building for new eTapestry users until you run a report and all of the data for journal transactions, contacts, or notes are blank.  Your report may show the name, address, phone number, and email address of the accounts you are looking for, but the received amount, date, subject, or note fields in your report are all blank (no data is showing for journal entries in your report, at all).  This outcome is caused when the data return type of a query is set to “Accounts” when the intention of the final report is to show both “Account” and “Journal Entry” data.

The solution to this challenge is to flip the data return type to “Journal Entries,” but it is important to understand why.  The data return type impacts the query by returning certain types of data in a report, hence the term “data return type.”  Accounts and Journal Entries are the two primary data objects in eTapestry.  As a result, you need to tell eTapestry which object you want to see in your report.  This inherently dictates the type of information you will see in the final output.

Challenge #4 – Report shows the same accounts multiple times

On the flip side of Challenge #3, you may find that your report shows the same accounts more than once.  If you are exporting data from eTapestry for a mailing list, duplicate names need to be removed.  You only want to send one piece of mail to a single account (or household).

Duplicate accounts are created because your query is returning “Journal Entries.”  Multiple accounts are shown because there are actually multiple Journal Entries being shown in your report output.  Each row where a duplicate exists is actually your query returning a unique journal entry.  To fix this, go back to your query and select a data return type of “Accounts.”  This change will return the accounts that match your criteria as unduplicated rows of data.

Challenge #5 – Can’t exclude or remove data from query results

Except for limited situations, you cannot exclude results from a query within the standard query (i.e. New Query) builder in eTapestry.  This sometimes comes as a shock to users and can be especially frustrating if you don’t know this is the case.  Many users spend hours trying to figure out how to include one set of data while excluding a different set using standard queries, to no avail.

So how do you exclude data that you don’t want in your query results?  Exclusions are needed when within an inclusive set you want to exclude a sub-set of that data.  The standard query is an inclusive query builder only.  It relies on inclusive logic to produce an output.  You must use a combination of inclusive standard queries in a compound query to produce an exclusion.  Compounds operate on formulas, so use a “Subtract” formula to exclude data from your intended query results.

A query of new donors in eTapestry is an example of an exclusion using a compound query.

Mastering eTapestry Queries

While this list is a good place to start, there are many more scenarios in the eTapestry query and report framework that will challenge and test your abilities.  As a result, the best way to learn the “ins and outs” of eTapestry queries and eTapestry reports is to learn by doing.  The more you dive into the query and report tools the more capable you will become with eTapestry reporting.