Creating and editing your own queries

What is a query in short answer?

A query can either be a request for data results from your database or for action on the data, or for both. A query can give you an answer to a simple question, perform calculations, combine data from different tables, add, change, or delete data from a database.

In vega, the custom query tool is a powerful functionality to set a range of criteria and find an accurate list of matching contacts. A vega preset query or your own query is used when creating a campaign action to send an email, create an excel list, print PDF, or print labels.

The query tool can be accessed in a few ways:

• From the Home tab, click the 'Add Query' button underneath the search bar
• From the Contacts tab, click the 'Add New Query' button to the right of the search bar

Once you have accessed the query tool, your screen will look like the picture below:

On the left-hand side, you can click Existing Queries to find a saved query to edit.

If creating a new query, you can start by giving your query a name.

Along the top you will notice different tabs, these are how you set the criteria for your query.

Output Columns

When the first criteria of your query have been added the default output columns will display on the left-hand side:

You can have any number of output columns but this can impact the speed to generate the query so it is best to only use those that are required.

To add more output fields, click the field name and you will see a pop-up box 'Add Output Column', click the blue 'Add' button. If the dropdown is left as Equal then this will simply include the output with no additional criteria.

To remove any Output Columns, select and click the blue 'Remove' button in the pop-up box. In addition to the default output columns, there are other group of output columns available in a general query. They are grouped as Contact Outputs, Other Telecom Outputs, Payment Summary Outputs and Membership Outputs. 

• • • Contact Outputs
      
      
      
      
    • Other Telecom Outputs
      
      
      
      
    • Payment Summary Outputs
      
      
      
      
    • Membership Outputs
      
      
      

Additional output columns are available when using attribute, payment, or recurring payment criteria in your query:

• • • Attributes:

• • • Payments
  •  
  • 
  •  
    • Recurring payments

The output columns can also have filter criteria added to them:

These can be used to find all contacts with an address or an email for example when creating campaign lists.

• Equal will simply include the output with no additional criteria and show blank if the field is empty on a contact.
• When using the Like or Not Like options, the % can be used as a 'wildcard' in place of any characters, for example, Email Like %@% will ensure that the contact has an email value with the @ sign and it has not been used in error for another telecom.
• The IN or NOT IN options can be used to list possible match values. For example, Postcode IN '0612', '0620', '0600' will return results for all contacts who have either one of the three postcodes. Please note ' ' are required around each value and they need to be separated by a comma.
• The options for IS NULL and IS NOT NULL are used to filter out blank values or find blank values. For example, to find all contacts who have a blank salutation, IS NULL can be added to this output column.
• Output columns can also have multiple filter criteria added to them. An example use of this is when finding contacts who are in the same country but could have a range of values. First, we can add Country IN 'New Zealand, 'NZ' and click 'Add'. Next, click the Country output again and click 'Add More Values', this time we select IS NULL. This will return all contacts who have a country field matching New Zealand, NZ or blank. 

• These filter options should not be used on output columns for attribute, payment or campaign values. You are able to set the specific criteria for these areas on the other tabs within the query tool and we will discuss these further in the linked articles.
• The options for Active an OK to Contact only will be selected but these can be un-ticked to view results for all contacts.
• The Select top option can be used by adding a value to only show the number of contacts added here in your population, the full amount will still be calculated. This can be used in conjunction with the Sort function.
• When an output column is selected, there are the options to apply a Sort and a Sort Order:

This will sort your result list based on this output value, either Ascending or Descending. If you have multiple sort values then the Sort Order can be used to determine which sort comes first. This sort function is useful when looking at a payment value, for example, to find your top 100 donors.