Have you ever tried to run a report in Virtuous but quickly got lost in the query tool? Queries can be intimidating if you’re not used to working with them, but they allow you to do some really powerful reporting.
Understanding Query Types
When you go to build a new query in Virtuous, you’ll notice that there are many query types. The reason for this is that Virtuous allows you to query on any type of object that exists in the database, and each object has its own set of parameters. For example, a contact might have an address, tags, and a relationship. That is very different from a project which has a location and a project code. It’s not always possible to get all of the things you want out of a single query. You first have to find the query type that’s associated with the thing that you’re searching for.
Nested Queries
Nested queries allow you to use different query types in combination with each other. For example, you might want to know all of the dollars that were brought in last year and how many unique contacts gave those dollars. You can accomplish this by running two queries: one to find the dollars and one to find the unique contacts.
Example 1: Finding Dollars and Unique Contacts
To find the dollars, you would run a gift query for the last calendar year. This will output a single row for every gift that came in during that time period. To find the unique contacts, you would run a contact query for all the contacts that were in your last year gifts. By nesting the contact query within the gift query, you will get one row per unique contact.
Example 2: Finding Active Recurring Supporters
Another example of using nested queries is to find active recurring supporters. On a gift, you can tell if someone is an active, recurring supporter if they have a recurring commitment on their contact record. You can use a recurring gift query to find all the unique recurring commitments in the system. Then, you can use a contact query to find all the contacts that are associated with those recurring commitments. By nesting the recurring gift query within the contact query, you will get the exact result you’re looking for.
TL;DR
Nested queries can be complex, so it’s important to be careful not to nest too many times. By nesting one or two levels deep, you can ensure that your queries run quickly and efficiently. Nested queries are powerful tools for reporting, building automations, and distribution lists. With a little practice, you’ll be able to use nested queries to connect data and get the exact results you’re looking for.
