As of July 12, 2021, this "Learn Veracross" site has been deprecated. It will remain live through December 2022, but will no longer be updated. All knowledge content has moved to the new Veracross Community. Please update your bookmarks.
Overview
A query is a structured question used to retrieve information from data in Veracross. It can be modified and filtered as necessary to produce a specific set of results. This article provides an overview query design and results in Axiom, highlighting the query template, query fields, and any criteria applied to the fields.
Read more about:
- Building Basic Queries
- Advanced Display Options
- Advanced Search Options
- Query Management and Action Options
- Query Properties and Visualization Options
Query Templates
The data on various records (e.g., person, household, class) in the system exists in tables. Query templates are preset queries that allow users to interact with the tables in the system to find data. Each template represents one table in the system, and so determines which fields can be used in the query.
Query templates are made up of a series of fields that structure which data the query should pull from the system. A field represents a place in which to store a piece of data associated with a record (for example, the “last name” field from a person record).
For example: to pull data on a person, a user would use the corresponding person query template to retrieve data from the person table.
Query Fields
A table is a collection of all of the data associated with a record type in the system. Each record type, such as a person or a household, has its own table. Data are arranged in tables to make it easier to query and to link to other records.
A field is a place in which to store a piece of data associated with a record (for example, a “first name” field from a person record). Fields are what make up the structure of a query and are used to specify which data should be pulled from the system.
See below for a list of different field types.
Folders and Fields
The available fields in a query template are organized into main folders and subfolders that can be found in the left column of the query design. To expand a folder, click on the triangle to the left of the folder name. Conversely, click on the triangle a second time to collapse the folder.
Folders that contains fields added to the query and the fields themselves will be bolded.
Linked Folders
Some folders may have a link icon to the right of their names, which indicates that they are “linked” folders. A linked folder contains fields associated with another record in the database, i.e., it pulls data from a different table in the system. Axiom automatically adds the Query Template description for new Query Fields from Linked Query Templates.
For example: “Household Info” is a linked folder within a person query. Though a household and a person are two separate record types in the system, users can simultaneously query a person’s household data while querying data from the person record. Any fields added from the “Household Info” folder will be prefixed with “HOUSEHOLD:” to clarify where that field is coming from.
Searching for Additional Fields
Although the fields in a query template are organized into folders, a user can quickly search for a field by typing a keyword into the “search fields” box above the list of folders. Any available fields containing that keyword will appear in a list below. The search will also include fields that already appear within the query design.
Keyboard Shortcuts
Shortcut | Description |
| ( \ ) key on keyboard | Puts the cursor in the “search fields” box. |
| (UP/DOWN) arrows on keyboard | When searching for fields to add to a query, scroll through the available fields using the up/down arrows. |
| (ENTER) key on keyboard | When searching for fields to add to a query, adds the highlighted field to the query. |
| (ESC) key on keyboard | After searching for fields, returns to the main list of folders and fields |
Real and Calculated Fields
Fields can be either real or calculated. A real field is an updatable field; its value is whatever a user enters. A calculated field’s value is determined from other data in the system and cannot be manually updated.
For example: a person’s birthdate is a real field; whatever the user enters as the date will be the value for that person. A person’s age field is a calculated field; its value is based solely on the birthdate listed in the system, and therefore can only be changed by changing the birthdate value.
Field Types
Queried data can be displayed in a variety of ways, depending on the type of fields to which the data are tied. The following list explains the different field types:
Field Type | Description |
| Single Line Text | Data displayed on one line (e.g., a person’s “full name” field). |
| Multi Line Text | Data displayed on several lines (e.g., a household’s “multi-line mailing address” field). |
| Rich Text | Data displayed in HTML formatting such as underlined or bolded text (e.g., a student comment for review). |
| Data displayed as a hyperlinked email address (e.g., a person’s “email 1” field). | |
| URL | Data displayed as a hyperlinked web address (e.g., a person-enrollment’s “preview contract” field). |
| Number | Data displayed in numeric form (e.g., a person’s “age” field). |
| Boolean | Data displayed as “yes” or “no” (e.g., a person’s “has F/A application” field). In the results, these data can also be displayed with a check for “yes” and no check for “no.” |
| Date | Data displayed in date form (e.g., a person’s “birthday” field). |
| Time | Data displayed in time form (e.g., a group event’s “start time” field). |
| Checkbox List | Data displayed as a predefined set of values that come from another table (e.g., a student’s “homeroom” field) |
| Join Field | Data displayed from fields that join explicitly to another record, noted by the ellipses button (…) adjacent to the criteria value text box that can be clicked to search that joined record. (e.g., a class “course” field) |
Query Criteria
Criteria are used to modify the content of a query by filtering the information within a field. Adding query criteria narrows the number of results to provide users with more specific data. Query criteria has two parts: the operator and the value. The operator is how a field should be filtered and the value is which data should be used as the filter.
Types of Operators
Different field types have different available operators. The following list details the different operator types:
Operator Type | Description | Available Operators |
| Text Operator | Filters based on words or digits (or the lack thereof). | is, is not, is blank, is not blank, begins with, does not begin with, contains, does not contain, contains word, ends with, does not end with, is greater than, is less than, is greater than or equal to, is less than or equal to |
| Number Operator | Filters by numbers or number ranges. | is equal to, is not equal to, is greater than, is less than, is greater than or equal to, is less than or equal to |
| Date Operator | Filters by dates or date ranges. | date is, date is not, date is blank, date is not blank, date is after, date is before, date is on or after, date is on or before |
| Checklist (GKC) Operator | Filters based on selections from a checklist associated with that field. | is one of, is not one of, contains all of, contains exactly |
Adding Criteria to a Query
To add criteria to a field in a query, first click on the field within the query design. The field properties section will appear to the right. The drop-down menu beneath the field name contains all of the available operators for that field (based on the operator type).
Select the appropriate operator from the list, and then enter the desired criteria value(s). Depending on the field type, values can be added as text, as a date, from a checklist, etc.
For example: To search for people with the last name of “Adams,” apply criteria to the last name field on a person query. The operator would be set to “is” and the value would be set to “Adams,” which would then filter the results to display only those people with a last name of Adams.
System Parameter Criteria Values
A system parameter is a value based on calculated settings in the system that adjust automatically. System parameters in queries are most often associated with dates or times and allow users to regularly query certain information without the need to update the criteria each time.
For example: To query group events scheduled for the current date, apply the system parameter {today} as the criterion, which will automatically pull in today’s date. The {today} criterion always reflects the current date, so queries using it run in the future will reflect information for that day.
System parameters must be contained in curly brackets { } when used as criteria values. Read more about system parameters for a full list of available parameters that can be used in queries.
Other Field Criteria Values
Other fields in a query can be used as criteria values, which can be helpful when data from two separate records should be checked against each other. To enter another field as a criteria value, click the “use another field as criteria value” link, located below the criteria value text box, and select the field to be used as the criteria from the list that appears. Only fields of the same data type will be available.
For example: To find cases where a person has the same email address listed in the email 1 and email 2 fields, run a person query where the “email 1” value is equal to the “email 2” value.
“Popping Out” a Query
Many screens throughout Axiom show results grids that can be “popped out,” or viewed as a separate results grid that can then be designed like any other query. “Popping out” queries and then designing them can be a helpful for two reasons:
- It shows what data are being displayed. If you are not sure why certain records are being shown and not others, pop out the query, design it, and look at the criteria.
- Use the popped out query as a starting point for a custom query that can then be saved.