eTapestry Query Cheat Sheet, Translations for Common Terms
Queries aren’t the easiest Blackbaud® eTapestry® feature to learn and master, but we’ve found that writing an eTapestry query narrative simplifies the query building process. An eTapestry query narrative is a blueprint that describes how a query is built and what outcome you hope to achieve with the query. The hard part of the eTapestry query narrative process is translating your query requirements into eTapestry’s language and vocabulary.
I was working with Harmony Project (one of our clients) on query development and they mentioned, “wouldn’t it be great if there was Google Translate for eTapestry queries.” We all chuckled at this, but realized that eTapestry queries have their own language. Understanding this language is the key to simplifying eTapestry queries. Unfortunately there is no Google Translate for eTapestry queries, but this post offers basic definitions for common eTapestry query terms.
Amounts
Amounts are used in transaction-based query criteria.
Amounts are found in both the “Individual Transaction” and “Cumulative” query criteria. Individual transaction criteria look for a single transaction that matches an amount and cumulative transaction criteria sum transactions to find where the aggregate total of the transactions matches an amount.
-
1. Received
Will find pledge payment and gift transactions (recurring gifts are included as a gift — each as a specific gift). Received is the equivalent of revenue.
-
2. Raised
Will find pledge and gift transactions but will exclude pledge payments (recurring gifts are included as a gift — each as a specific gift). Raised is the common metric for large-scale campaigns.
-
3. Pledged
Will find pledge transactions only (no gifts, pledge payments, or recurring gifts). Pledged is the total promised contributions that have been raised.
-
4. Gifted
Will find outright gifts only that have not been received as a pledge payment (recurring gifts are included as a gift — each as a specific gift).
-
5. Soft Credit
Will find soft credit transactions only where the soft credit amount is a separate amount from any linked hard credit transaction.
Journal Entry Type
There are eighteen different values in the journal entry type query criteria (depending on the features activated in your eTapestry database). The common journal entry types are easy to identify, but there are three journal entry types that are worth noting because they may affect how you build a query.
-
1. Recurring Gift Schedule
The recurring gift schedule is different than a recurring gift. A recurring gift is the actual transaction that is counted as a received, raised, or gifted amount and the recurring gift schedule processes a recurring gift on a defined frequency. The recurring gift schedule identifies whether the recurring gift processing schedule is active, when the next processing date will occur, and the installment amount. The recurring gift schedule is not a received gift.
-
2. Soft Credit
The soft credit journal entry type will include soft credit transactions as well as the hard credit linked transaction. For example, if Wayne Enterprises gave $500 and $500 of that transaction is soft credited to Bruce Wayne, the soft credit journal entry type query criteria will return both the gift transaction from Wayne Enterprises and the soft credit transaction for Bruce Wayne.
-
3. Soft Credit w/o Hard Link
The soft credit without hard link will not return the Wayne Enterprises gift in the example above. This journal entry type query criteria will only return the soft credit transactions in the database, which are separate journal entries on the journal of a soft credit account. This journal entry type query criteria is useful if you don’t want to double-count soft credit amounts and need to separate those transactions from linked hard credit transactions in reports.
Data Return Type
Data return type dictates what type of records a query will produce as rows in a report. Every eTapestry report is like an Excel spreadsheet containing columns and rows. The columns are specific fields (i.e. data points) and the rows are specific records. Most queries have a data return type of accounts or journal entries.
-
1. Accounts
The query will return accounts in a report. Each row in the report will be a distinct account that matches the criteria of the query. This data return type should be used when a report only displays account level data, as fields on a journal entry will not display in a report with a data return type of accounts. This is also the best data return type when you need to produce an unduplicated list of accounts (assuming you cleanup duplicates from your database).
-
2. Journal Entries
The query will return journal entries in a report. Each row in the report will be a distinct journal entry that matches the criteria of the query. This data return type may return multiple journal entries for a single account if those journal entries match the criteria of the query. A journal entry data return type will display both the journal entry data in a report and the account data for that journal entry.
Criteria Matching
The match setting applies to the query criteria. This setting generally applies to queries where there is more than one criterion, but it may also apply in situations where a multi-select field (select multiple options from a list) is used as query criteria. The match option combines the criteria in a query with logic.
-
1. Match each criteria
Selects records that match every criteria listed in the query. Commonly referred to as AND logic.
-
2. Match at least one criteria
Selects records that match one of the criteria on its own or all of the criteria listed in the query. Commonly referred to as OR logic.
It is important to note that there are no exclusionary match conditions in eTapestry. Build a compound query with subtraction if you need to exclude data from a query.
Persona Options
The persona option is something to pay attention to, especially if your query criteria contain persona fields. The persona query feature identifies the persona eTapestry will look at when applying the criteria of the query. This doesn’t mean eTapestry will display that persona in a report (that is a different feature), it means eTapestry will look for accounts that match the criteria for the persona selected in the query.
-
1. Any Persona
As it states, this condition looks for the criteria of the query on any persona of an account. If the criteria are listed on the account, it will select the record for the query.
-
2. Primary Persona
This condition looks for the criteria of the query on the primary persona only. Even if the criteria are matched by data on a different persona, this condition will ignore that data and only look at the primary persona.
-
3. Specific Persona
(e.g. Personal, Business, Joint) – This condition acts like the primary persona option by forcing the search of a query on a particular persona. Even if the criteria are matched by data on a different persona, this condition will ignore that data and only look at the specific persona that is selected.
Translating eTapestry Queries
Translating common language into eTapestry queries can be a challenge. Building a narrative that translates your query requirements into eTapestry’s language is the best way to simplify eTapestry queries. Use the definitions in this eTapestry query cheat sheet to build queries that pull the data you need for eTapestry reports.
Free Download
The Essential Kit to eTapestry Best Practices
- 4x PDF Guides
- 1x Template/Worksheet
- 1x 60-minute Webinar
- Here we can add a benefit, or we can delete one.
Join the 700+ users that already got their kit!
DOWNLOAD NOW