Welcome Query Explorers! Now that you have determined whether to
centralize your Query-writing, or distribute it among your users here is a guide to help as you build your query library. Writing efficient queries that give you the right results can be tricky, but with these tips in your back pocket, Information library in Blackbaud CRM™ 4.0 can be a powerful tool.
The Query Question
Before building a query, consider the information you want in your results. One way to organize this thinking is to start by asking the database a question. For example: “Who donated to a Fund last year, but not yet this year?” Break down the question based on its parts, and it will help you determine how best to write the query:
Who: Looking for constituents; individuals and/or organizations
Donated: Constituents with revenue
To a Fund: The designation associated with the Revenue Application
Last year: Date range on the revenue
...but not this year: Where the most recent date on the revenue is prior to this year
A Window into the System: Source Views
Now that you understand the parts of your question, you can select the Source view that will give you the answer you expect. A Source view is a window into a specific part of the system. Since we are looking for individuals or organizations who have given, select the Constituent source view in this example.
Skip-ahead tip: Reviewing the topmost node in the query tree is a good way to understand the perspective each source view offers.
Most common source views
Constituents – Use when you need information about individuals or organizations and their actions
Revenue – Use when you need information about the accounting or transaction of a donation
Other common source views
Revenue Application – Use when you need information about how donations were split among designations
Revenue Recognition Credits – Use when you need information about the recognition donors (Hard Credit or Self Credit, soft-type credits, etc) as well as the dates and amounts for which the donors will be recognized
Appeal – Use when you need information at the Appeal level, including the designations, revenue, and events associated with each Appeal
Event – Use when you need information about the Event details, including invitees and registrants
Climb the Tree
When exploring the query tree to familiarize yourself with the way the nodes and fields are organized, begin by focusing on the topmost node in the query tree. This node contains information about the Source view you selected. Click on the topmost node in the left column, and review the fields available to you in the center column.
It is also a good idea to review the values available in the Type field. Many nodes include a Type field, which contains values specific to the type of node. Reviewing those values can give you insight into what information will be returned from that node.
As you build your query, you may find yourself searching for a specific piece of information. It is a good idea to use a record page as reference when building a query. Identify one of the records that you expect to see in your results (e.g. go to a constituent record if you are writing a Constituents query; go to a transaction record if you are writing a Revenue query). Open a second CRM window with that record, and find the information on the page that will help you filter in your query. Take note of the tab, sub-tab, and section where this information can be found on the record, and match them up to one of the nodes in the query window.
Shallow Nodes (no diving!)
Once you understand where to find the information you need in the Query Tree, it is important to remember not to “deep dive” into a particular node. You may find that you can click into a node’s sub-headings to retrieve information that is not within the primary node. Be careful not to go too deep into the sub-headings; you could be accessing information from the wrong perspective, or you might need to select a different Source view.
Stay close to the top
Try to take query filters and output fields from the primary nodes as much as possible
If you click through the node into its sub-headings, be sure to close the node before selecting another field; this will help you retrieve information from the most relevant node in the query, and not get lost in the tree
Watch for circular references
When you are within a node’s sub-headings, you may see the topmost node listed again. For example, in a Constituent query, you will see a Constituent heading again under the Revenue node.
Even though it looks the same as the topmost node in the query, this Constituent heading is referring to the constituent recorded on the revenue transaction, and might not match the constituent for whom you are querying.
To avoid getting trapped too deep in a query tree, be sure to scroll up to the node and click the (-) to close it up before selecting your next field for filter or output
If you do select a field under a sub-heading, it will appear with the full path name in the Filters or Output fields sections (i.e. Revenue\Constituent\Name); this will help you understand from where the information is pulling
Filter Topology
The order of the Filters is important to insure that the query runs efficiently.
Include filter criteria from the topmost node first
General filters should appear first, then specific filters (i.e. filter for Constituent type first, then for Address (Primary)\City)
Group filter criteria together if they are taken from the same node
When the node includes an End date field, use it as a filter to select the most current information
Skip-ahead tip: Add parentheses around the field filter and its End date filter to treat them as a single filter criteria
Try to avoid using exclusionary ask operators when specifying a filter criterion: rather than using Does not contain as an ask operator, try adding multiple Contains criteria with “Or” to separate them
That said… (Parentheses)
Parentheses can be very useful to indicate the order of operations for query filter criteria (think back to middle school math equations). When filters are grouped within parentheses, they are treated as a mini-query inside the larger query. The filters inside the parentheses will process together as a single filter.
Skip-ahead tip: If you filter twice on the same field, it’s helpful to include that field in your Results field to display for reference
Use parentheses whenever you filter twice on the same field (e.g. Constituency is equal to “Prospect”; And Constituency is equal to “Volunteer” – for all constituents who are both fundraising Prospects and Volunteers)
Use parentheses whenever you use the “Or” clause (e.g. Constituency is equal to “Prospect”; Or Constituency is equal to “Volunteer” – for all constituents who are either fundraising Prospects or current Volunteers)
Collect Output and Record the Results
Add output fields in the Results fields to display section to facilitate how you read the query results.
It’s a good idea to always include the Lookup ID for reference within the system if available (e.g. Constituent Lookup ID, Revenue ID, Event Lookup ID, etc.)
If you filter twice on the same field, add that field to Results fields to display as an indicator of which filter criteria returned the results
Rename output fields
If you plan to use the output for a report in Excel, or for an Export process, it is a good idea to rename the Results fields to display to be more readable.
In the query window, click on the output field to highlight it
Right-click and select Change column header, or click the (abc) pencil icon in the header of the Results fields to display box
Type the name of the field (it is helpful to indicate the node or type of field in the name)
The new field name will appear as a column heading in the query results
Avoid (the dreaded) duplicate rows in results
When there can be more than one result returned for a given output field, the query results will show each as a separate row. For example, a constituent may have more than one address, or more than one phone number. When Phones\Number is added to query output, you may see multiple rows for a single constituent; one per phone number.
Use Address (Primary) fields in output instead of those found under the Addresses node
Use the Primary email address or Primary phone number field to select only the primary Email or Phone Number
Add OR Phones\Number Is Blank to include any Constituent with a Primary Phone Number or no Phone Number at all
Add OR Email addresses\Email address Is Blank to include any Constituent with a Primary Email address or no Email at all
Use a Type filter in the query to select only constituents who have Addresses where Type is equal to “Business,” for example. The output address will then reflect their Business address
If duplicate rows cannot be avoided, consider using an Export Definition for output: use query with limited output fields to identify the constituents, revenue records, or events that you need, and then use an Export Process to output predefined information about those records
Check your work!
When you click the Preview results tab in query, the query runs and returns results before you save it, allowing you to tweak your filter criteria until the results are just right - voila!
For results of 10,000 rows or less, click Export to Excel at the bottom of the results window to open the results directly in Excel
For results of 10,001+, an Export process is required to view all results in Excel
Catalog and File: Save a Selection
The primary use of the Query tool in Information Library is to filter for and save a group of records (a Selection) to be used in another part of the system. Selections can become the base parameter for a report, a list of constituents to receive a communication or solicitation, a list of individuals to be invited to an Event, or a group of revenue transactions to be adjusted. The intention is to select the records, and then take action in the system.
When you Set save options in the Query window, you have the option to Create a selection. Most of the time, you will create a dynamic selection, allowing the system to automatically run the query for the most up-to-date results every time you use that selection in another process. Only save a static selection if you want to preserve the results from this moment-in-time; you do not want the results to update or change, or if you are using the selection in a Marketing Effort. (The Marketing Efforts in CRM require static selections so that the system can queue up all of the selections used in a single effort, to refresh in a particular order. Marketing Efforts will allow you to recalculate segments and selections.)
Source views, filters, dynamic, and static… Armed with a go-to field guide and a few tips and tools, you are ready to explore your Information Library. Navigating your data, maximizing filter efficiency, and getting your results quickly are the tools you need to experience the power of Query.
If you’re up to the challenge, the trailblazers at BrightVine are here to help get you started. Connect with us today for more information.