Query Operators

Published: 17th August 2017

Step 1

Equals Selects records that match exactly what is written in the field. For example, if you choose Constituency
Code “equals” STAFF, only records with the exact entry of STAFF in the Constituency Code field are selected
Does not equal Selects records that do not match exactly what is written in the field. For
example, if you choose Constituency Code “does not equal” STAFF,
the program selects every record that does not have STAFF entered as
the constituent code.
Greater than Selects records that are of a higher value/more recent date than what is written in the field. For example, if you select
Gift Amount “greater than” £100, only records with gift amounts of more
than £100 are selected.
Greater than or equal to

 

Selects records that are of the same or higher value/more recent date than what is written in the field.  For example, if you select Gift Amount “greater than
or equal to” £100, only records with a gift amount of £100 or more are
selected. This operator includes the value you selected, in this case £100.
Less than Selects records that are of a lower value/older date than what is written in the field. For example, if
you select Gift Amount “less than” £100, only records with a gift amount of £99.99 or less are selected. Records with a gift amount of £100 are not included.
Less than or equal to Selects records that are of the same or lower value/older date than what is written in the field. For example, if you select Gift Amount “less than or equal
to” £100, only records with a gift amount of £100 or less are selected. This  operator includes records with the value you selected, in this case £100.
One of Selects records that match one or more of what is written in the fields. For example, if you select Subject of Study “one of” Church History, Hand Surgery, and Railway Studies, records selected must contain one of the campaigns chosen.
Not one of Selects records that do not match any of what is written in the fields. For example, if you select
Subject of Study “not one of” Church History, Hand Surgery, and Railway Studies, records
selected must not contain any of the campaigns you defined. 
Between Selects records that fit between the first and second values written in the fields. For
example, if you select Gift Amount “between” £100 and £300, records with gift amounts between £100 and £300 are included. Amounts equal to £100 and £300 are also included.
Not between Selects records outside of the first and second values written in the fields. For
example, if you select Gift Payment Amount is “not between” £100 and
£300, only records with gift payments of less than £100 and more than£300 are included. Gift payments between £100 and £300 are not included.
Begins with Selects records where the beginning of the relevant field matches the value written in the field. For
example, if you choose Surname “begins with” Bell, only constituents whose surname begin with “Bell” are selected (for example, Bell, Bellmont, or Bellingham). You can use wildcard characters with this operator.
Does not begin with Selects records where the beginning of the relevant field does not match the value written in the field. For example, if you choose Surname “does not begin with” Bell,
only constituents whose surname does not have “Bell” at the beginning are selected. You can use wildcard characters with this operator
Contains Selects records that contain what is written in the field. For example, if you select Surname
“contains” G, any records with the letter “G” anywhere in the Surname field are selected. You can use wildcard characters with this operator.
Does not contain Selects records that do not contain what is written in the field. For
example, if you select Surname “does not contain” G, only records without a “G” anywhere in the Surname field are selected. You can use wildcard characters with this operator.
Like Selects records that are similar to what is written in the field (requires wildcard characters). For example, if you
select Surname “like” B?rd, the program selects all records with a similar spelling for example, Berd, Bird, or Byrd).
Not like Selects records that are not similar to what is written in the field (requires wildcard characters).
Blank Selects records where the relevant field is unpopulated. For example, if you choose Constituent ID is “blank”, the program selects records with an empty Constituent ID field.
Not blank Selects records where the relevant field has been populated. For example, if you select Constituent ID “is not blank”, the program selects all records with an entry in the Constituent ID field. 
Sounds like Selects records that sound like what is written in the field. For
example, you want to add a note about a conversation you had over the phone with a constituent who says their name is Smith. However, their name is actually spelled Smyth, which sounds like Smith. When you query, if you search for “sounds like” Smith, the program will find their record. You can also use wildcard characters with this operator.
<Ask> The Ask at Runtime operator lets you postpone the selection of specific values for
your criteria fields (filters) until the time you actually run the query.

Step 2

Combining operators allow you to narrow your query even further by combining
two separate sets of filtering criteria to make one. Combining operators are
characters that provide a link between selected criteria and define the records
included in the query.

 

  • And. You can use the And button between criteria fields to indicate that records
    must meet both criteria to be selected. For example, if you use the field
    criteria Surname “equals” to Smith And Constituency Code “equals” to
    STAFF, the program locates all constituents whose surname is Smith
    AND who are Staff members. The records selected have to meet both criteria
    to be included in the query. The default combining operator used in a query
    record is And.

 

  • Or. You can use the Or button between fields to indicate records can meet either
    criteria to be selected. For example, if you use the field criteria Surname
    “equals” to Smith Or Constituency Code “equals” to STAFF, the
    program locates all constituents whose surname is Smith OR constituents
    with a constituency code of STAFF.

 

  • Parentheses ( ). You can use the parentheses buttons to make two pieces of a
    criteria a whole. If you select the following criteria: City “equals” Sheffield And (Constituency Code “equals” STAFF Or Gift Amount “greater than or equal to” £1,000) the records selected must first have Sheffield as a value in the City field, and must have either a
    constituent code of STAFF or a gift amount of £1000 or more. In this
    case, records must meet the first criteria and at least one of the criteria within
    the parentheses.