Query conditions enable you to define specific search criteria so that you can find the exact data you want to work with. This topic introduces you to query conditions and provides examples of the types of conditions you can apply to your data to enable you to find the information you need.
A query condition is an expression you apply to a column that limits the results based on values you tell the query to search for. When you create a query condition, you identify the column and then refine the search of the column's data by choosing a specific property or content that satisfies the condition. A record must meet the criteria you specify to be included in the query's results.
A condition varies depending on the type of data in the column. That is, a condition you specify for a column that contains numeric data looks different from one specified for a column containing text or date data. To view the various types of conditions available and examples of each, go to Query_Operators_and_Examples later in this topic.
A condition is an expression that consists of three elements: the column name, an operator, and a value. For example:
● Column: identifies the column the condition applies to. It tells the query which column's data to evaluate. In the above illustration, Members Age is the name of the column.
● Operator: specifies the condition (criteria) to apply to the column's data. In other words, the operator lets you specify the type of comparison you would like to perform (go to Query_Operators_and_Examples for a list of operators you can use). In the above illustration, is between is the operator.
● Value: identifies exactly what you want the query to look for in the specified column. The value is represented by a link. Clicking the link opens a text entry field in which you specify the value the query must look for. The query searches the column for a value that matches the number, letter, word, or phrase you type into the field.
Keywords are words that have a special meaning within the context of the language that the ParishSOFT IQ application uses to access your database, select data, and process your queries.
The keywords are part of the where clause that appears at the top of the Query Conditions panel. As shown in the following illustration, this statement has four keywords to choose from. The keywords are located on a menu that is accessed by clicking the all link within the where clause:
By default, the default keyword in the where clause is the all, which returns all records that meet the specified conditions. Detailed descriptions of the four keywords are provided in the following sections.
The all keyword (the default) tells ParishSOFT IQ to retrieve only those records for which all of the specified conditions are true.
As shown in the example below, the all keyword has an "and" meaning. Using this keyword has the effect of narrowing the query's search because each "ANDed" condition must be true for a record to be included in the result set. In other words, the query retrieves records that contain exactly what you specify—nothing more and nothing less.
The following diagram illustrates the result set for a query containing three conditions when the all keyword is used to select records. The area of the diagram where all three conditions intersect contains the records for which the three conditions are true:
● Male
AND
● Between 21 and 35 years old
AND
● Unmarried.
So, for this particular query, only the records of unmarried males between the ages of 21 and 35 are included in the result set. In the diagram below, the result set is represented by the intersection of the specified conditions:
Be aware that all is the default. Make sure that if you specify multiple conditions that you want the query to return for which all conditions are true.
The any keyword tells ParishSOFT IQ to retrieve records that satisfy at least one—but not all—of the specified conditions. In other words, one of the conditions must be true.
As shown in the example below, the any keyword has an "or" meaning, which makes it less restrictive.
The following diagram illustrates the result set for a query containing three conditions when the any keyword is used. As the diagram below illustrates, compared to using the all keyword, using any retrieves more records because a record that satisfies any one of the "ORed" conditions is included in the result set. A record satisfying any one of the specified conditions is acceptable:
● Male
OR
● Between 21 and 25 years old
OR
● Unmarried
So, for this particular query, the record of any male, any unmarried individual, and any individual between the ages of 21 and 35 is included in the result set. In the diagram below, the result set is represented by the union of the specified conditions:
The none keyword tells ParishSOFT IQ to retrieve only those records that are contrary to the specified conditions. In other words, the query selects only those records for which all of the specified conditions are false.
As shown in the example below, the none keyword has an exclusive meaning. Using this keyword can help you find records that do not meet the conditions you specify.
The diagram below illustrates the None keyword. The specified condition, Payment Amount ≥ 0.01, is represented by the circle. Records in the None result set are those that do not meet the condition. Those records are represented by the area outside of the circle. Specifically, records in which any contribution amount is greater than a penny are included the None result set.
The not all keyword tells ParishSOFT IQ to retrieve only those records that satisfy some—but not all—of the specified conditions. As shown in the example below, each condition is "ANDed". The query retrieves only those records for which at least one of the ANDed conditions is false.
The following diagram illustrates the not all keyword applied to a query containing three conditions. As the diagram shows, the query retrieves records that meet some—but not all—of the conditions you specify.
Click any of the following buttons to add a condition to a column.
● 
: this button is located at the bottom of the Column Picker panel. Click it to add condition statements to the columns currently listed in the Results Column panel.
● [Add new condition]: this link is located at the bottom of the Query Conditions panel. Click it to display a list of columns. Select the column to apply the condition to.
● 
: this button is located at the top of the Query Conditions panel. Click it to display a list of columns. Select the column to apply the condition to.
You can apply a condition to a column in one of three ways. The three methods are provided below:
In the Query Conditions panel, click .
From the displayed menu, select the column you want to apply the condition to.
The condition expression is displayed in the Query Conditions panel. An expression contains three elements: the column name, an operator, and a value. For example:
To add the condition to the column, complete these steps:
Click the Operator link (see previous illustration) and select the desired operator from the menu.
The operators listed in the menu depend on the type of data contained in the column. For operator descriptions and examples, go to Query_Operators_and_Examples.
The operator is added to the condition.
Click the Value link (see previous illustration). In the text field, type a value or values for the condition.
In the Query Conditions panel, select this link: [Add new condition].
From the displayed menu, select the column you want to apply the condition to.
The condition expression is displayed in the Query Conditions panel. An expression contains three elements: the column name, an operator, and a value. For example:
To add the condition to the column, complete these steps:
Click the Operator link (see previous illustration) and select the desired operator from the menu.
The operators listed in the menu depend on the type of data contained in the column. For operator descriptions and examples, go to Query_Operators_and_Examples.
The operator is added to the condition.
Click the Value link (see previous illustration). In the text field, type a value or values for the condition.
The names of the columns are added to the Result Columns panel.
.For each column you selected, a condition expression is displayed in the Query Conditions panel. The condition expression varies by column and depends on the type of data in the column.
Each expression contains three elements: the column name, an operator, and a value. For example:
To add the condition, complete these steps:
Click the Operator link (see previous illustration) and select the desired operator from the menu.
The operators listed in the menu depend on the type of data contained in the column. For operator descriptions and examples, go to Query_Operators_and_Examples.
The operator is added to the condition.
Click the Value link (see previous illustration). In the text field, type a value or values for the condition.
Following is a list of operators you can apply to columns in your query. Click the link to jump to a description of the operator and an example of how it works.
This list includes all operators. The operators available for selection depend on the type of data contained in the column.
is not equal to (special date)
Retrieves only those records where the value in the specified column's field is equal to [enter value]. The value in the column's field must exactly match the value specified in the text entry field.
Click [enter value] to display a text entry field. Type the desired value in the field.
In version 4.4.0 of ParishSOFT IQ, the Is Equal To conditional operator was temporarily removed from the list of conditions available for the Member > Birthdate column because queries in which the operator was applied produced incorrect and inconsistent results. We are working on a solution and will add Is Equal To back to the list of operators in a future release.
This example applies the is equal to condition to the Batches Actual Cash Total column to retrieve only those records with batches having a cash total equal to $1500.00:
This second example applies the is equal to condition to the Families.Owner Organization ID column to retrieve only those records with an organization ID equal to 21447:
Retrieves only those records where the value in the specified column's field is not equal to [enter value]. The value in the column's field must not equal the value you specify in the text entry field.
Click [enter value] to display a text entry field. Type the desired value in the field.
The following example applies the is not equal condition to the Members is Active column to retrieve only those records of individuals whose current member status is not equal to "Active":
Retrieves only those records where the value in the specified column's field is less than (smaller than) [enter value]. The value in the column's field must be less than the value specified in the text entry field.
Click [enter value] to display a text entry field. Type the desired value in the field.
The following example applies the is less than condition to the Pledges Down Payment Amount column to retrieve only down payment amounts less than $100.00:
Retrieves only those records where the value in the specified column's field is less than (smaller than) or equal to [enter value]. The value in the column's field must be less than or the same as the value specified in the text field.
Click [enter value] to display a text entry field. Type the desired value in the field.
The following example applies the is less than or equal to condition to the Pledges Down Payment Amount column to retrieve only those records in which the down payment amount is less than or equal to $100:
Retrieves only those records where the value in the specified column's field is greater than (larger than) [enter value]. The value in the column's field must be greater than the value specified in the text field.
Click [enter value] to display a text entry field. Type the desired value in the field.
The following example applies the is greater than condition to the Pledges Total Pledge Amount column to retrieve only those records in which the total pledge amount is more than $500.00:
Retrieves only those records where the value in the specified column's field is greater than (larger than) or equal to [enter value]. The value in the column's field must be greater than or the same as the value specified in the text field.
Click [enter value] to display a text entry field. Type the desired value in the field.
The following example applies the is greater than or equal to condition to the Members Age column to retrieve only those records of members 13 years of age or older:
Retrieves only those records where the value in the specified column's field is blank (contains no value).
Note that "null" is not the same as zero (0). Zero is considered a value—not considered null.
The following example applies the is null condition to the Families.Contact Info Email Address column to find records of families that have no email address in the system:
Retrieves only those records where the value in the specified column's field is not blank.
The following example applies the is not null condition to the Pledges End Date column to retrieve records that have a value in the Pledge End Date field. The value indicates the date that the pledge ended or will end as of the date shown in the field.
Retrieves only those records where the value in the specified column's field is contained in the list.
The following example applies the is in list condition to the Pledges.Down Payment Check No column to retrieve records that match the check numbers in the list. The query is looking for columns in which the check number for a pledge down payment is 100, 101, or 102.
Retrieves only those records where the value in the specified column's field is not contained in the list.
The following example applies the is not in list condition to the Funds. Fund Name column to retrieve records that do not match the funds in the list. The query is looking for columns in which the fund name is anything other than the following:
● 005-540-333
● 005-540-111
● 005-540-4444
Retrieves only those records where the value in the specified column's field starts with [enter value].
The following example applies the starts with condition to the Families Last Name column to retrieve records of families whose last name starts with the letter M:
Retrieves only those records where the value in the specified column's field does not start with [enter value].
The following example applies the does not start with condition to the Families.Bank Accounts Type column to retrieve records of families whose bank account type does not start with the letters "Cr":
Retrieves only those records where the value in the specified column's field contains [enter value].
Click [enter value] to display a text entry field. Type the desired value in the field.
The following example applies the contains condition to the Members Special Needs Desc column to retrieve only those records of members in which the special needs description contains the phrase "wheelchair access":
Retrieves only those records where the value in the specified column's field does not contain [enter value]. In other words, the query excludes any records that contain the value specified in the text field.
Click [enter value] to display a text entry field. Type the desired value in the field.
The following example applies the does not contain condition to the Members Career Description column to retrieve only those records of members in which the career description does not contain the words "self-employed":
Retrieves only those records where the value in the specified column's field is between [enter value] and [enter value].
Click [enter value] to display the text entry fields. Type the desired value in each field. The value can be a number or a date.
The following example applies the is between condition to the Members Age column to retrieve only those members between the ages 13 and 19:
Retrieves only those records where the value in the specified column's field is not between [enter value] and [enter value].
Click [enter value] to display the text entry fields. Type the desired value in each field.
In the following example, applying the is not between condition to the Pledges Balance column retrieves only those records that have a pledge balance greater than $1000.
Retrieves only those records where the year in the specified column's field is [enter value]. Allows you to search a date field for a specific four-digit year.
Click [enter value] to display a text entry field. Type the desired year in the field.
In the following example, applying the year is condition to the Sacraments.Baptism Date Completed column retrieves only those records in which the baptism was completed in 1987.
Retrieves only those records where the month in the specified column's field is [enter value]. Allows you to search a date field for a specific month.
The month value entered must be numeric and be between 1 and 12. For example, enter 6 for the month of June.
Click [enter value] to display a text entry field. Type the desired number representing the desired month in the field.
In the following example, applying the month is condition to the Sacraments.Matrimony Date Completed column retrieves only those records in which the marriage was completed in the month of June.
Retrieves only those records where the date in the specified column's field is [enter value]. Allows you to search a date field for a specific date.
The date value entered must be numeric and between 1 and 31. For example, enter 4 for the 4th.
Click [enter value] to display a text entry field. Type the desired number representing the day in the field.
In the following example, applying the day is condition to the Sacraments.Death Burial Date column retrieves only those records in which the burial date is the 4th day of the month.
A sub-query is an inner query or a query that is embedded inside of a main query. ParishSOFT IQ performs two operations: it processes the subquery first and feeds the results to the main or outer query. Then, it processes the main query and displays results in the Query Results panel.
For the selected column, returns data that satisfies the in subquery condition. The data returned by the sub-query is used as a condition to further restrict the records retrieved by the main query.
Select in sub query. Then click [edit sub-query] to open the Edit sub-query window. In this window, select a keyword (for example, all or any) and then click [Add new condition] to specify the sub-query condition.
The best way to understand the in sub query condition is to look at an example. Go to How to Use Subqueries.
A sub-query is an inner query or a query that is embedded inside of a main query. It is used to retrieve data from one column based on data in another column. ParishSOFT IQ performs two operations: it processes the subquery first and feeds the results to the main or outer query. Then, it processes the main query and displays results in the Query Results panel.
For the selected column, returns data that satisfies the not in subquery condition. The data returned by the sub-query is used as a condition to further restrict the records retrieved by the main query.
Select not in sub query. Then click [edit sub-query] to open the Edit sub-query window. In this window, select a keyword (for example, all or any) and then click [Add new condition] to specify the not in sub-query condition.
The best way to understand the not in sub query condition is to look at an example. Go to How to Use Subqueries.
Retrieves only those records in which the maximum value in the specified column's field equal to the maximum of [column link].
Click [column link] to display a list of columns. Select the column from the list.
In the following example, applying the is maximum of condition to the Members Age column retrieves the age of the oldest member.
Note that in this example, selecting Members Age as the Value compares the values in the column with each other to obtain column's maximum value.
Retrieves only those records in which the value specified in the column's field is before [enter value].
Click [enter value] to display a calendar.
The calendar displays today's date as the default. Select the desired month and year from the calendar displayed. If the year you want is not visible, select the earliest year possible to load it into the field. Then, click inside the year field again to display another set of years and select the earliest year in the displayed group. Continue in this manner until you find the year you need. Then, select the desired month and day.
In the following example, applying the before condition to the Ministry Scheduler EndDate column retrieves only those records of ministers whose assignment end date is before August 31, 2015.
Retrieves only those records in which the value specified in the column's field is before the special day option selected from the following menu:
Click Today to display the menu.
Select one of the special day options.
In the following example, applying the before (special) > Today condition to the Contributions Payment Date column retrieves records with a payment date before today's date.
Retrieves only those records in which the value specified in the column's field is after [enter value].
Click [enter value] to display a calendar.
The calendar displays today's date as the default. Select the year from the calendar displayed. The calendar displays today's date as the default. If the year you want is not visible, select the earliest year possible to load it into the field. Then, click inside the year field again to display another set of years and select the earliest year in the displayed group. Continue in this manner until you find the year you need. Then, select the desired month and day.
In the following example, applying the after condition to the Members Birthdate column retrieves records of members date of birth is after July 27, 1990:
Retrieves only those records in which the value specified in the column's field is after the special day option selected from the following menu:
Click Today to display the menu.
Select one of the special day options.
In the following example, applying the after (special) > First day of the year condition to the Pledges Start Date column retrieves only those records with a pledge start date after January 1.
Retrieves only those records in which the value specified in the column's field is within [enter value] and [enter value].
Click [enter value] to display a calendar.
The calendar displays today's date as the default. Select the year from the calendar displayed. If the year you want is not visible, select the earliest year possible to load it into the field. Then, click inside the year field again to display another set of years and select the earliest year in the displayed group. Continue in this manner until you find the year you need. Then, select the desired month and day.
In the following example, applying the custom period condition to the Contributions Payment Date column retrieves only those records with a contributions payment date between August 18, 2015 and August 31, 2015:
Retrieves only those records in which the value specified in the column's field is equal to the special day option selected from the following menu:
Click Today to display the menu.
Select one of the special day options.
In the following example, applying the is equal to (special date) > Tomorrow condition to the Contributions Payment Date column retrieves only those pledge records with payment date of tomorrow:
.png)
Retrieves only those records in which the value specified in the column's field is not equal to the special day option selected from the following menu:
Click Today to display the menu.
Select one of the special day options.
In the following example, applying the is not equal to (special date) > First day of the year condition to the Ministry Scheduler EndDate column retrieves only those records of ministers whose assignment end date is not today:
.png)
How to Add, Edit, or Remove a Condition
How to Disable and Re-enable a Condition