Building Basic Queries

Overview

A query is a structured question used to retrieve data, or records, from tables. A record can be a person, a household, a class, etc. Queries can be built in Axiom to retrieve many different kinds of data. This article covers basic query design:

• starting with query templates
• adding, removing, rearranging, sorting, and customizing fields
• specifying basic query criteria

Read more about:

• advanced display options
• advanced search options
• query management and action options
• query properties and visualization options

Query Templates

Query templates are preset queries that allow the user to interact with the tables in the system to find data and are available throughout Axiom on the various homepages.

Query templates are generally based on a single table in the database, and determine which fields can be pulled into the query. Think of them as starting points that can be modified to produce specific results. The template used depends on the type of record the user wants to query.

For example: to query data associated with a person record, the template used should be a person query (e.g., Find People, Find Students, Find Candidates). Similarly, to query data associated with a household record, the template used should be the household query.

Query templates may also be nested within a larger query template for further specification. For example, on a Household Query, users may add in elements of a Person Query to provide details on the Head of Household or the Spouse.

Adding & Removing Fields

The fields available in a query template are organized into folders that can be found in the left column of the query design screen. To expand or compress a folder, click on the triangle to the left of the folder name. Use the “Search Fields” box at top of the list to find a field quickly.

The name of a folder containing fields that have already been added to the query will appear in a black bolded font, and fields already added will appear in a blue bolded font.

To add a new field to a query, click once on the field name. The field can be added twice by double-clicking on the field name. To remove a field, place the cursor over the field in the query and click the red X that appears to the right.

Rearranging Fields

The order in which fields are listed in the query is the same order in which fields are listed in the results. To change the field order, simply click and drag the rearrange icon  to the left of the field name.

Displaying & Hiding Fields

A field can be part of the query without being displayed in the results. When a new field is added to a query, the default setting is to display the field. To hide a field, click on the checkbox located in the middle column to the left of the field’s name. When the box is unchecked the field is hidden. To display a field, simply click on the checkbox again. Note that even if a field is hidden, criteria can still be applied to filter the results.

To quickly hide all fields at once, click on the gear icon at the top right corner of the query field list and select “Hide All Fields.” Similarly, to quickly display all of the fields at once, select “Show All Fields.” Select the option called “Rearrange All Fields by Display Status” to quickly move all displayed fields at the top of the list.

Sorting Results

The results of a query can be sorted based on one field or multiple fields in a specified order.

For example: a student query can be sorted only by last name, or first by grade level and then by last name.

To sort by a field, click on the sorting icon (two horizontal arrows) to the left of the field name. Clicking once turns on sorting in ascending order, indicated by the upward-facing arrows. Click on the icon a second time to sort instead by descending order, indicated by the downward-facing arrows.

To sort by multiple fields, click the icons in the order the fields should be sorted. The number that appears to the right indicates the order. This number can be altered using the up-down arrows located next to the number.

To turn off sorting, hold down the SHIFT key while clicking on the icon.

For these sorting methods to work, the field that is being sorted by cannot be hidden; if the field is hidden, then the sorting is not applied. To sort by a field and suppress that field from the query results, click on the field and then click on the Style tab in the right column. In the Column Options drop-down menu, choose the “Suppress from Results” option. If set up correctly, the icon to the left of the field should be a minus sign instead of a blank box or a check mark.

Records can also be sorted from the results screen on the data grid. To sort a column in the results data grid, simply click once on the column header. This will sort the column in ascending order. To sort in descending order, click the column header a second time. Sorting changes made within the query results will persist if the query is run a second time. The sorting will also be applied to the query design.

Resetting the Query to the Original State

Users are able to reset the query back to the original state by using the gear icon at the top right corner of the query field list and selecting “Reset Query”. This will remove all criteria set on the query by the user, for example any values that were set for fields, any sorting that had been indicated, rearrangement of fields, display formatting, etc.

Resetting a Saved Query: An Example

Queries are cached by the system, and cannot be cleared by users. If a user updates an already-saved query, another user accessing it (e.g., from a shared workspace) may not see the most recent version. The second user should reset the query. The following illustrates this situation:

1. User A opens a query, makes a change, and overwrites it.
2. User B opens runs that query without making any changes.
3. User A makes another change to the query, and overwrites it.

How can User B make sure they can see the changes that User A made? The answer is that they need to be sure to be loading the query fresh and not with modifications. Once a query is updated in any way, it becomes a local copy of that query until it is reset or saved. A clue that a user has made modifications to a query is that there will be a * appended to the query title or the URL.

The query should be reset by the second user to ensure they are seeing the most recently updated version.

Query Design 101

Field Properties

Field properties allow users to customize how data in certain fields appear in the results. To access field properties, click on the field name within the query. The field properties section will appear to the right of the query design, separated into “Properties” and “Style” tabs.

Specify Query Criteria

To specify search criteria, enter text and select a type of operator from the drop down menu (begins with, contains, etc.). The default operator is “begins with.”

Tip: Use the % sign at the start of the criteria to use the “contains” operator. Example: Leaving the specified operator as the default “begins with,” type %son to return records that contain the letters “son” (e.g., “Johnson” or “Clarkson”).

Read more about query criteria.

Advanced Search

When related information can be searched (e.g., specifying an advisor in a Find Students query), use the Advanced Search to conduct a “search within a search” on that field. See animated screenshot here for illustration. Check or uncheck the results to retain or remove those criteria from the search.

Overriding Field Descriptions

Field names become the column headers in the results grid. To change a field name, first select the field from the query. In the field properties section, click on the current field name and then type what the new name should be into the box.

Running the Query

Once a query contains the necessary fields, criteria, and properties settings, generate the results by clicking Run Query  at the top right of the screen.

The query results will appear within the same browser window. To adjust query parameters, click Design to return to the query design screen.

Most query results are displayed in a data grid, but results can also be visualized as a calendar, on a map, as labels, or as a URL document preview. Visit the query properties documentation for details on changing the results visualization.