Running a Basic Query

Published: 25th July 2017

Queries that will be used to actively contact constituents e.g. mailing lists, email lists, should be built by the Development Services team for quality assurance purposes. To request this service, please fill in this form.

There may be times you can build simple queries yourself. Below are the steps involved in running a basic query.

Step 1

Choose the Query option from the left menu bar and then select the option for New Query.

Step 2

Select the Query type you wish to include in your results. This determines what kind of record to search (e.g. Constituent, Gift, Organisation). Then select whether your Query format will be Dynamic (refreshes each time you run the query) or Static (like a ‘snapshot’ of the database at the time of creating the query).

Step 3

You will then be presented with four tabs. The first is the Criteria tab and this determines which records will be included in your query result.

Step 4

Clicking on the + sign will expand any option and allow you to further narrow your search. Double click on an item to add it to your search criteria.

Step 5

Before your selected criteria is added to the query, you will see a pop up called Edit Field Criteria. This allows you to further narrow down your search by selecting Operators (see below for a full list of these). In the example below, the operator one of has been selected, in order to add a number of computer related industries. In this situation, you just need to double click from the list on the left, in order to add it to the selected list on the right.

Ensure you de-select the boxes to include deceased and inactive constituents, if you do not wish to include these. These are located just below the tab headings.

Step 6

Once you have added all Criteria, you need to decide which fields of the selected records you would like to see in your results. Click on the Output tab in order to start adding these fields. These are added in the same manner as you added your Criteria.

Step 7

The third tab is Sort. This may not be as used as frequently as Criteria and Output but can be a useful way of ordering your results. Here, you can select the fields by which you wish to sort your query results, by choosing from the left hand menu, and then use the buttons in the bottom right to determine sorting by ascending or descending order.

Step 8

The final step is to see your results. You can find these by either by clicking on the fourth tab, Results, or clicking Run Now at any time. What is displayed in the Results tab is determined by the fields you entered under the Output tab. For example, if you forgot to choose any fields in Output, you will simply see the number of results, without any information about the records.

Step 9: Explanation of Query Operators

Step 9.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 9.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.