The criteria statement, displayed on the Edit Query panel of the list record, shows the requirements that must be met in order for a record to be included in the list. The statement is made up of one or more lines that include information about which tables, fields, and values will be evaluated. The relator determines the nature of the evaluation. Connectors between lines determine how the criteria in any given line will be combined with other lines in the criteria statement.
In a relational database like IQ’s, data is stored in
tables. The tables contain columns of information
roughly translating to the fields located in IQ record pages. Each line
of your query must indicate which table
Fields are the available columns in the database tables and typically correspond to the fields located in IQ record pages. When a field is multi-valued (a single People record may have multiple Affiliation codes) or when a record has multiple relationships to other types of records (a single People record may have multiple Mail or Workflow records), IQ uses an object to define the relationship for the purposes of querying and/or reporting on the data.
The objects expand the ability of IQ to query the database based on the existence or non-existence of data. For example, you may want to find People who are Agency Contacts or Mail records without Issue Codes.
The information contained in each field in a table is that field's value. When you create a query, you indicate which value to look for, and whether the list should include or exclude records that contain that value.
You can be more or less specific about the value by including a relator. The relator defines the relationship between the value you type into the Value field and the actual value that the field contains in each record that the query looks at.
IQ uses two types of relators: How Many and Condition. Each relator has multiple options.
Any
No
More
Less
Between
Equal
To
Not
Equal To
Greater
Than
Greater
than or Equal To
Less
Than
Less
Than or Equal To
Contains is used when you are searching for a string of data found in a field.
Starts With is used when you are searching for a value that begins with a string of data.
Ends With is used when you are searching for a valued that ends with a string of data.
Use this field to specify the value to be located. The value for any given field is the information used in that field and stored in the IQ database. For example, the values for the Last Name field might include Smith, Jones, McCutcheon or any other last name. If the field you are searching has a finite set of values, such as a code file, a list of the possible values that can be selected will appear. You may scroll through the list, clicking the values you are searching for or you can enter the value in the Filter box, and the list of possible values will be filtered by your entry. Note: If there are a large number of items, IQ will limit the display of both the full list and the filtered list of values. Click the Load More link to display more values. Selecting the value from the possible values list ensures that the value you select is what is in the database and also gives you the option of selecting values that are in error, such as misspelled city names or invalid codes. If the value you are selecting is a date, the possible values column presents a calendar from which to select your date, or you may type the date in the Values list box. If you are searching for multiple values in the same field, each value should appear on its own line in the Values list box.
When the query criteria contains multiple statements
or multiple groups of statements, you must indicate the order in which
the statements or groups should be operated on
When you combine arithmetic operations such as multiplication and addition, the results differ depending on the order in which the operations are performed. Use parentheses to determine the order:
(4 x 3) + 6 = 18 (4 times 3 equals 12; 12 plus 6 equals 18)
4 x (3+6) = 36 (3 plus 6 equals 9; 4 times 9 equals 36)
The parentheses in a mathematical equation tell you that the piece within the parentheses should be considered first. Likewise, parentheses in a criteria statement work the same way to group criteria and mark it to be executed first. Using parentheses to group statements in different ways can lead to different results.
Parentheses are added to the criteria by selecting the Bracket Mode action on the Edit Query panel of the List record page.
To add an opening parenthesis, click the plus (+) button located at the beginning of a criteria line.
To add a closing parenthesis, click the plus (+) button located at the end of a criteria line.
To remove a parenthesis, click the minus ( - ) button to delete it.
You can use multiple sets of parentheses to group criteria lines and change the way the query builder will operate on them. Remember that you must use parentheses in pairs.
These two queries contain the same criteria lines, but parentheses group the statements differently.
|
( |
Address |
Zip Code |
EQ |
22204 |
OR |
|
|
Address |
Zip Code |
EQ |
22205 ) |
AND |
|
|
Affiliation |
Code |
EQ |
VIP |
|
In this first query, with parentheses enclosing the first and second statements, the database is searched once. Any record that contains the ZIP Code 22204 and the affiliation code VIP is selected, as well as any record that contains the ZIP Code 22205 and the VIP affiliation code.
|
|
Address |
Zip Code |
EQ |
22204 |
OR |
|
( |
Address |
Zip Code |
EQ |
22205 |
AND |
|
|
Affiliation |
Code |
EQ |
VIP ) |
|
In the second query, with parentheses enclosing the second and third statements, the database is searched once. Any record that contains the ZIP Code 22204 whether or not that record contains an affiliation code of any kind is selected, as well as any record that contains the ZIP Code 22205 and the VIP affiliation code.
Connectors are used in a query to group multiple lines. When you execute a list, the database is searched once for each group of query lines, and sets containing all the records that meet the criteria in that group are created. Those sets are then combined according to the connectors that are used between them.
Functionally, there are two classes of connectors. The first class includes And, And Also, and Or. When you use one of these between query lines, statements are connected to make a group. The second class includes Union, Intersect, and Minus. When you use one of these, the statement (or group) that follows it is in a different group than the statement (or group) that precedes it.
When a query has only one statement, the database is searched once for records that meet the criteria in that statement.
Two or more statements that are connected by And or Or are in the same group. The database is searched just once for records that meet the criteria in the group.
When two or more statements (or two or more groups of statements connected internally by And or Or) are connected by Union, Intersect, or Minus, the database is searched once for each statement or group, and a set of the records that meet the criteria is created. The final list is created by excluding, intersecting, or combining the sets based on the connector used.
The And connector tells IQ to look for records that meet both criteria. And is used when there is more than one requirement that needs to be met in order for the record to be included in the list; and when no more than one field specified in the criteria statement can have multiple values for the type of record being selected. When you use the And connector, the database is searched once for every record that meets the criteria in both the first and second query statements.
Example:
Find People ...
with Any Address having City Equal To 'Arlington' And
with Any Affiliation having Code Equal To 'VIP'
Every record that contains both the city name Arlington and the affiliation code VIP is selected, so that you have a list of all the VIP members in Arlington. Any record that meets only one of the criteria is not included in the list.
Do not use the And connector for the following queries (use Intersect instead).
When you’re searching for more than one item in a single multi-valued field, for example, if you’re trying to find People records that contain both affiliation codes VIP and ARTS.
When you’re searching for information in more than one multi-valued field, for example if you’re trying to find People records with the affiliation code VIP that also have correspondence with the issue code BUDGET.
When you are comparing different criteria within the same record, IQ creates a subquery using the And Also connector. And also is used to evaluate multiple values within the same record and at the same level of detail before moving on to evaluate the next record.
Example:
Find People ...
with Any Mail having Status Equal To 'Approved' And Also
with that same Mail having Assigned User with User Name Equal To 'Dottie Sempsey'
Because a People record can contain more than one associated mail record, IQ has to know to evaluate these criteria together before moving on to the next record or the next set of criteria. So in the above example, IQ will only find those People records that contain an Approved Mail record that is also assigned to Dottie Sempsey. Any People record that contains a Mail record that meets only one of the criteria is not included in the People list.
The And Also connector does not use a How Many relator. How Many relators are used to limit the set of records to be evaluated (Any, No, Less than X, etc.). When you use And Also, you are automatically telling IQ this record that is already being evaluated.
The Or connector tells IQ to look for records that meet either criteria. Since all records meeting requirements connected by Or are included in the final list, the restriction for fields with multiple values does not apply to this simple connector. When you use the Or connector, the database is searched once for every record that meets the criteria in either the first statement or the second statement.
Example:
Find People ...
with Any Address having City Equal To 'Arlington' Or
with Any Affiliation having Code Equal To 'VIP'
Every record that contains the city name Arlington as well as every record that contains the VIP affiliation code is selected. The final list includes all of the records of both types: all the people in Arlington (no matter what their affiliation codes are) and all VIP members (no matter where they live).
When you use the Union connector, the database is searched twice, first for all records that meet the criteria in the first statement or group of statements, and second for the records that meet the criteria in the second statement or group of statements. The two sets are then compared, and every record ID number that appears in the first set, the second set, or both, is placed into the final list.
Example:
Find People ...
with Any Address having City Equal To 'Arlington' Union
with Any Affiliation having Code Equal To 'VIP'
First, all of the records that contain the city Arlington are located. Then all of the records that contain the affiliation code VIP are found. A union of the two sets is made, and the final list includes all people who live in Arlington (no matter what affiliation codes their records have) and all people who belong to the VIP group (no matter where they live).
When you use the Intersect connector, the database is searched twice: first for all of the records that meet the criteria in the first statement (or group of statements) and then for the records that meet the criteria in the second statement (or group). The records are placed in two sets, and the sets are compared. Every record that is in both sets is placed the final list.
Example:
Find People ...
with Any Affiliation having Code Equal To 'ARTS' Intersect
with Any Affiliation having Code Equal To 'VIP'
Every record that contains the affiliation code ARTS
is found. Then all records that contain the VIP affiliation are located.
The intersection of the two sets
When you use the Minus connector, the database is searched twice. First a set of every record that meets the criteria shown before the Minus is created. Then the database is searched for the records that meet the criteria shown after the Minus, and they are placed in a set. Last, the two sets are compared, and every record that's in the second set is taken out of the first set.
Example:
Find People ...
with Any Address having City Equal To 'Arlington' Minus
with Any Affiliation having Code Equal To 'VIP'
A set that contains the ID number of every record that has the city Arlington is created. Then a set that contains the ID number of every record with the VIP affiliation code is created. Any record ID that's in both sets is removed from the Arlington set, and the ID numbers that remain in the first set are placed in the final list. The final list contains all people in Arlington except for those who are VIPs.
Although in some cases using And and Intersect to connect query statements (or groups of statements) yields the same results, there are important differences between the two. They can create very different lists.
A People record can contain multiple Affiliation fields, with one code in each (or a mail record can have multiple Issue fields, with one code in each). However, because of the way the tables in the IQ database are arranged, a single query statement can search for only one value (code) per field per record.
Using And, the following query would search the database just once, looking for any record that contained both affiliation codes:
Find People ...
with Any Affiliation having Code Equal To 'DOC' And
with Any Affiliation having Code Equal To 'HOSP'
Because it's impossible for the query builder to "see" that one People record contains both the DOC and HOSP affiliation codes, the resulting list would contain zero records.
Using Intersect tells the query builder to search the database twice:
Find People ...
with Any Affiliation having Code Equal To 'DOC' Intersect
with Any Affiliation having Code Equal To 'HOSP'
On the first search, any People records that contain the affiliation code DOC are found. On the second search, a set that includes any record with the HOSP affiliation code is created. Because the same People record might actually contain both affiliation codes, the same ID number could end up in both sets. Because the intersection of the two sets is any record that is a member of both sets, the final list contains the ID number of every record that contains both the DOC code and the HOSP code.
A People record can contain dates for multiple mail records. A mail record can contain multiple Issue fields, with one code in each; however, because of the way the mail dates and issues are linked in the IQ database, a single query statement can search for only one issue code per piece of mail.
Using And, the following People query would search the database just once, selecting any People record that linked an incoming correspondence date on or after January 1, 2005, with the issue code BUDGET.
Find People ...
with Any Mail having Date In Greater Than or Equal To '01/01/2005' And
with Any Mail having Issues with Code Equal To 'BUDGET'
The final list would contain the ID number of every People record in which a piece of correspondence addressing a gun ban was logged in on or after January 1, 2005.
If you use Intersect to connect the same two criteria statements in a People query, the database is searched twice.
Find People ...
with Any Mail having Date In Greater Than or Equal To '01/01/2005' Intersect
with Any Mail having Issues with Code Equal To 'BUDGET'
On the first pass through the database, all People records that contain mail logged in on or after January 1, 2005, are found, and their record ID numbers are placed in a set. This correspondence could address any issue.
On the second pass, all People records that contain the issue code BUDGET are found, and their record ID numbers are placed in a set. The correspondence that addressed BUDGET could have been logged in on any date.
The intersection of the two sets would be any record ID number that is a member of both sets. One record might contain a piece of mail that came in after January 1, 2005, and a piece of mail addressing BUDGET, and therefore be included in the final list. However, the incoming date and the issue code are not necessarily for the same piece of correspondence. Therefore, the ID numbers in the final list can be for records that do not contain correspondence that was both logged in on or after January 1, 2005, and in reference to BUDGET.
The combination of tables, fields, relators, and values constructs a query statement. The Edit Query panel in the List Record page displays the statements that will be used to create the list.
If a query includes only one statement, the set of records it creates becomes the final list. In a query that includes multiple statements, the statements are used to create one or more sets of records, and then combined in accordance with the connectors between the statements. The final list includes all of the records in the combined sets.