Creating a New Query

The purpose of a query is to search an area of the database, filter results based on criteria you specify, and display those results according to parameters you determine.

Users can create queries in two ways:

1. by creating a query from scratch, OR

2. using an existing query as a starting point.

In either case, begin by navigating to the Queries App and selecting Query Builder.

Tip: If possible, using an existing query as a starting point is typically the recommended method as it makes for a faster and simpler setup

Building a Query from Scratch

1. Navigate to the Queries App > Query Builder.

2. In the section Step 1 - Select Query Target, choose the target of the query using the Query Target drop-down field.

3. Locate the section Step 2 - Define Filter Criteria Criteria are used to filter query results based on the values in a field. Criteria can be static data defined by the query creator, or can be left open-ended to allow the user running the query to choose or enter the desired data..

4. In Step 2 - Define Filter Criteria, click the green Add icon. A new Select Field line item appears.

5. Click Select Field to choose the criterion type. The Select Field window will appear.

   In the window, locate the query field to add to the list of filter criteria. Certain fields relate directly to the query target, while others relate to linked subjects. If a field is part of a linked table, click the carat (>) next to the table name to access the sub-fields. A Search field is also available at the top of the window for simple searching of field names.

Click Select to add the field to your query. Add as many fields as necessary to Step 2 - Define Filter Criteria.

Note: When using the search bar to locate a filter, search results will include field names but not table names.
For example, consider a target jointed to the "Address" table. It will offer fields like "Address > Line 1," "Address > Line 2," "Address > State," etc. A search for "Address" in won't cause all available options to appear, but by entering "Line 1" in the Search field, that option will appear.

Elements of Step 2 - Define Filter Criteria include:

• Hamburger icon: Click and drag the "hamburger" icon at the start of each line of filter criteria to rearrange the criteria.

• Delete: To remove a line from the list of filter criteria, click the red delete icon.

• And / Or Operator: Designates whether the search results must meet each of the filter criteria or any of the filter criteria. In most cases, "Or" filters will need to use indented rows to function as intended. See the help article, Adding Sub-Filters/Indented Fields.

• Field Name: The name of the field being used as a filter criterion.

• Operator: The operator specifies how the system should search on a certain criterion. Examples of commonly used Boolean operators include "Is Equal To" and "Is Not Equal To," "Contains," and "Does Not Contain," etc.

  Tip: For additional information on the function of operators, please see websites such as the MIT Library page "Database Search Tips: Boolean operators" and the Columbia University Libraries page "Boolean Operators." (Links to the MIT and Columbia University websites are for information only—Impexium has no affiliation with either of these organizations.)

• Query Criterion Field: Enter data in this field to narrow the field criteria to a specific data point.

  Example: If you specify that the query field "Last Name" must use the operator "Is Equal To" and then enter the name "Smith" in the Query Criterion Field, running the query will return only individuals whose last name is equal to "Smith." To allow the user running the query to enter whatever value they wish, leave this field blank and check the Ask At Run-Time check box.
  

  Note: If the Query Criterion Field is left blank, then the Ask At Run-Time check box must be checked.

• Ask At Run-Time: Check the Ask At Run-Time check box to allow users running the query to enter whatever data they wish into the query search field. If unsure on this setting, it is recommended that you enable it.

  Example: For example, if the field "Last Name" is added to an individual search query and the Ask At Run-Time check box is checked, each user who runs the query can enter a different value in the "Last Name" field when the query is run.

• Add Indented Row: This setting allows staff to prioritize the query filters. See the help article, Adding Sub-Filters/Indented Fields.

Note: Using a sub-query configuration allows two different query targets to be joined together. For information on creating sub-queries, see the help topic Creating Subqueries.

6. In Step 3 - Select Result Fields, click the Add icon to add the desired fields to the result set.

   Use the Group (Aggregate) Results option if you wish to display aggregate values in the query results. See the help article, Aggregating Query Results

7. In Step 4 - Define Sort Order, click the Add icon to add the desired fields by which search results should sort.

8. At the bottom of the form, you can configure additional query settings:

   

   • Query Name: The name of the query. Each query must have a unique name. System queries' names cannot be edited.

   • Query Description: Further details about or instructions for using the query. System queries' descriptions cannot be edited.

   • [Try to] Eliminate Duplicates: A data search may return a single result multiple times, known as a "true duplicate." To exclude true duplicates from the query results, check this box. Please note, however, that data may still include "duplicates" if a result has multiple values listed for any of the results fields.

   • Disable Calculating Count: Selecting this option will remove the count total and the page numbers from the query results. Checking this can reduce the run time on queries that pull substantial amounts of data.

   • Is Public Query: Changes the viewing permissions on the query to allow external users to access it when it is added to an external-facing web page.

   • Is Private Query: Makes the query available only to the person that created it (and users with the Administrator role).

   • Tags: Can be used to label or categorize queries.

9. Click Save or Save and Run.

Using an Existing Query as a Starting Point

Using an existing query has the benefit of a pre-populated query target and fields.

1. Choose a query from the Load Query field. All sections of the Query Builder will auto-populate with the settings of the selected query.

2. Scroll to the bottom of the page to the Save as a New Query (Create a Copy) field.

3. Create a name for the new query and save. This will help prevent you from accidentally overwriting the existing query with your changes.

4. Modify the Filter Criteria, Results Fields, and/or Sort Order as needed.

It is recommended that you save the query before running it for the first time. If the query is not set up to perform as expected and has not been saved before being run, it may be necessary to recreate the query from scratch.

Note: Another way to use an existing query as a starting point is to first run the intended query. In the query results under the Actions menu, select the option that "Modify this Query" or "Customize this Query."