Which Operator Should I Use for my Query? – Virtuous

Each parameter in a query contains three fields: Field, Operator, and Value.

You can learn more about fields and values here, but the operator can be tricky. Like the value field, the options you will find in the operator field (which include custom fields if there are any activated for the object) depend on the data type of the field. So if the custom field is Boolean type, then you will have Boolean operators.

Numerical Operators

These are for fields that are numerical, meaning values are number-based characters (1, 5, etc.). Numerical fields include IDs (contact, gift, etc.) and even statistics like Life to Date Gift Count and Financial Score.

Is: Generates a list of records that have this exact value in the selected field.
Is Not: Generates a list of all records that do not have this exact value in the selected field.
Less Than: Generates a list of records that are numerically less than the value you have entered in the value field.
Less Than or Equal To: Generates a list of records that are numerically less than OR EQUAL TO the value you have entered in the value field.
Greater Than: Generates a list of records that are numerically greater than the value you have entered in the value field.
Greater Than or Equal To: Generates a list of records that are numerically greater than OR EQUAL TO the value you have entered in the value field.
Between: Generates a list of records with the field value being in a particular range of your choice of values.

For example: You want to pull a list of all contacts that have a Financial Score of 90 or above. On a contact query, you can set this parameter:

Boolean Operators

These are binary fields/options, so there can only be two values:

True
False

The exact options for your operator may present differently depending on the exact field, but there are only two options as opposed to other data types where there could be hundreds, thousands, or even millions of options. Examples include whether or not a contact has an address (Address - Is Known/Is not known) or whether or not an individual has an email address (Has Email? - Is True/False).

You'll notice when you select your operator that the field will show as N/A. This is because there is no other information to add here!

For example: You want to pull a list of contacts who have an address to get an idea of how many letters to send out. In a contact query, you can set this parameter:

Date Field Operators

These are fields where you specify a date (month, day, year). These include fields like Gift Date and Birthday. For date fields that are not required for the object, like birthdays on individual records, you will also see the "is set" and "is not set" operators.

Some operators are based on fixed dates and some are based on relatives dates.
A static date value will be a specific day (month, day, year) that is fixed.
A relative date value will be a time range (like yesterday, today, etc.) from the time the query is run. These values are particularly handy for ongoing uses such as automation workflows (e.g., pulling first-time donors that have given within 30 days of when the workflow runs).

Is: Generates a list of records that have this exact date listed in the selected field.
Less Than: Generates a list of records that have the static date set as some time before the date you selected, so this does not include those that have this date selected.
Less Than or Equal To: Generates a list of records that have the static date set as some time on or before the date you selected, so this does include those that have this date selected.
Before: Generates a list of records that have the relative date set as some time before the time range you selected in relation to when the query is run, so this does not include those that have this exact dynamic timing selected.
On or Before: Generates a list of records that have the relative date set as some time on or before the time range you selected in relation to when the query is run, so this does include those that have this exact dynamic timing selected.
Greater than: Generates a list of records that have the static date set as some time after the date you selected, so this does not include those that have this date selected.
Greater than or equal To: Generates a list of records that have the static date set as some time on or after the date you selected, so this does include those that have this date selected.
After: Generates a list of records that have the relative date set as some time after the time range you selected in relation to when the query is run, so this does not include those that have this exact dynamic timing selected.
On or After: Generates a list of records that have the relative date set as some time on or after the time range you selected in relation to when the query is run, so this does include those that have this exact dynamic timing selected.
Between: Generates a list of records with the date field value being in a particular range of your choice of static date values.

Note: The Between operator in Virtuous is inclusive. This means, for example, if you select a query for gifts between 01/01/2024 and 05/15/2024 it will return gifts received on those dates as well as in between them.

For example: You want to pull a list of gifts from within the last 30 days of when you pull the list. On a gift query, you can set the parameter:

Text Field Operators

This includes any text-based field that is not a dropdown or multiselect list - in other words, you can type anything in these fields, from number to letter characters. Many name-based fields are text fields, so First Name, Project Name, Campaign Name, etc. would all be text fields.

Is: Generates a list of records that have this exact value in the selected field.
Is Not: Generates a list of all records that do not have this exact value in the selected field.
Contains: Generates a list of records whose field values contain all of the characters you enter for the value, but this does not have to match exactly with the whole value assigned to the record.
Does not contain: Generates a list of records whose field values do not contain all of the characters you enter for the value, and this does not have to match exactly with the whole value assigned to the record.
Starts with: Generates a list of records whose field values start with all of the characters you enter for the value, but this does not have to match exactly with the whole value assigned to the record.
Ends with: Generates a list of records whose field values end with all of the characters you enter for the value, but this does not have to match exactly with the whole value assigned to the record.

For example: You have multiple staff entering gifts in various imports. For batch codes, you use a naming convention that uses gift type, date, and staff initials. You have 3 employees entering gifts today, with their batch codes as follows:

CH05182022JS
CA05182022RG
CH05182022PT

You want to see all the check gifts that were entered today. In this example, 'CH' represents check batches, so to see all checks from today's date, you can create a gift query and enter the following parameter:

This way, you can see all the gifts entered from JS and PT.

Dropdown Field Operators

In contrast to text-based fields, you are limited to selecting only one of the options available from a dropdown list, which has a set list of values. Type-based fields are often dropdown lists, so this includes Gift Type, Project Type, Communication Type, and so on. You will sometimes have the same number of operators as a Boolean data type, but you can have multiple options for values. Ultimately, you could end up with any number of the following parameters:

Is: Generates a list of records that have this exact value in the selected field.
Is Not: Generates a list of all records that do not have this exact value in the selected field.
Contains: Generates a list of records whose field values contain all of the characters you enter for the value, but this does not have to match exactly with the whole value assigned to the record.
Does not contain: Generates a list of records whose field values do not contain all of the characters you enter for the value, and this does not have to match exactly with the whole value assigned to the record.
Starts with: Generates a list of records whose field values start with all of the characters you enter for the value, but this does not have to match exactly with the whole value assigned to the record.
Ends with: Generates a list of records whose field values end with all of the characters you enter for the value, but this does not have to match exactly with the whole value assigned to the record.
Is set: Generates a list of records who have any value assigned to the field.
Is not set: Generates a list of records who do not have any value assigned to the field.

For example: You want to pull a list of gifts that aren't stock. Create a gift query and enter the following parameter:

Multiselect Field Operators

Like dropdown fields, there is a set list of values (with the exception of being used with the "starts with" or "ends with" operators for this data type, which will have more infinite text-based field options, as well as the "Has any" or "Has none" operators, which will have Boolean-based field options). Unlike dropdown fields, you can select more than one value. Tags are a common multiselect field because a contact can have multiple tags!

Is any of: Generates a list of records that have any of the exact values you select for that field. So, a record would only have to have at least one of those values selected in order to make it to this list.
Is none of: Generates a list of all records that do not have any of these exact values in the selected field. So, a record would only have to have at least one of those values not selected in order to make it to this list.
Has any: Generates a list of all records that have any value assignment at all.
Has none: Generates a list of all records that don't have any value assignment at all.
Starts with: Generates a list of all records that have at least one value assignment that starts with the value you indicate.
Ends with: Generates a list of all records that have at least one value assignment that ends with the value you indicate.

For example: You want to pull a list of contacts that are Major Donors or Sustaining Donors. Create a contact query and set the parameter as follows: