When building a query in Virtuous, you’ll see three fields. On the left, you can select a system field that you want to use in identifying results for your query. On the far right, you can enter or select a value that you might be looking for in the specified system field. Between these two, you’ll need to specify an operator.
Here’s we’ve selected Contact Id as our system field, and you can see the list of possible operators. Select “Is” to find a specific Contact, as in “Contact Id is 24601.” Select “Is not” to generate a list of all Contacts Except Contact 24601. Since Ids are numeric values, you can also use operators like “Greater than” or “Less than” or even select “Between” to specify a range of values.
Other fields, like State, will show different operators. Use “Is set” or “Is not set” to find all records that have a value for State, or that DON’T have a value for State, respectively. You can also use the “Is In” or “Is not in” to enter a list of values, separated by a comma, to use in your query. For example, you could use State is in TX, NY,CA to find all Contacts where the State field is TX, NY, or CA.
Selecting a date field to use in your query will display a different list of operators. These operators fall into two basic categories. The first group includes:
- Less than
- Less than or equal to
- Greater than
- Greater than or equal to
These will allow you to to use exact calendar dates in your query. For example, you could query for all the Contacts created on February 21st, 2019 using the “Is” operator, or find all Contacts created before February 21st by building a query stating “Create date Less than 2/21/2019.”
Use “Between” to specify a range of dates, entering a start and end date.
The “Before” and “After” operators will allow you to select from a list of relative dates, like “Yesterday” or “30 days ago.” These can be used in saved queries that are run frequently, like those used in powering an automated workflow, without having to continually modify the query. For example, to run a Gift query for all of the Gift imported today, you could create a query using “Create Date After Yesterday” and run it each day for receipting or other financial reporting.
One other set of operators you’ll see is specific to data with a one-to-many relationship. The most common examples of these would be Tags or Organization Groups, since Contacts can have one or more value for each. In these cases, the operator list will include:
- Is any of
- Is none of
- Has any
- Has none
- Starts With
- Ends With
Use “Is any of” to enter one or more values for inclusions in your query. For example, you could find all Contacts that are Tagged as “Major Donor,” “Peer-to-peer fundraiser,” or “Large giver.”
Use “Is none of” to exclude records with specific values. You might want to find Contacts who are not in the “Northeastern Donors” or “Western region” Organization Groups.
Use “Has none” to find records that have no values at all, for example, all Contacts with no tags, or you can use “Has any” to find all; Contacts with at least one Tag, regardless of the actual Tag value.
If your organization has a large number of tags or Organization Groups, you can simplify queries by taking advantage of naming conventions and using the “Starts With” or “Ends With” operators. For example, if all of your major gift officers have an Organization Group that has a prefix of “MGO,” you can quickly find everyone who is in one of those groups by querying for Contacts where “Organization Group Starts With ‘MGO.’”
Now that you understand query operators, you’re ready to move on and learn even more about working with queries in our other videos.