A List Record exists for every query you create in Intranet Quorum. The tabs displayed, however, will vary depending on the type of list. Criteria based lists contain the following six tabs: Review Query, Edit Query, Screen Builder, Report Builder, Status Log and SQL Reference. The List Record page for Personal, Merge and Split lists only contains the Review Query and Status Log tabs.
The List Record page opens in the Review Query panel for both new and existing lists. The Edit Query panel is where you define the list criteria for your query. The Screen Builder and Report Builder panels are used to create custom reports. The Status Log panel displays a history of actions for the list record. The SQL Reference panel contains the actual SQL that is used to generate the records for the list.
The Review Query panel of the List record contains information about the list name, the owner of the list, the Created, Revised, Executed and Expiration Dates, the description of the list and other info. The left-hand side of the panel contains user-updatable fields and information; the right-hand side of the panel displays the query once it has been created.
When you create a new list, IQ automatically assigns an ID and Type and creates a default name, assigns the Owner as the logged-in user, and updates the Execution Filter, Execution Sort, Input List (criteria), Expiration Date, and Usage. The Created and Revised dates are also populated. The Executed date, Duration and Count are populated after the query has been executed.
A sequential number assigned to each list by IQ that is used as part of the default list name.
Lists can be identified with the following types: Criteria, Personal, Split, Merged, Draft Search, Search, Report or Cross Tab. These types are available when creating a new list or searching for existing lists.
A default list name that is composed of the module name and a sequential ID number is automatically entered in this field. You may replace the default name with your own name of up to 40 characters. Note: The > and < characters may not be used in the list name. If you select a name that is already in use, IQ will automatically append the text "(copy #)" to the list name.
The user name of the person who created the list is automatically entered in this field. If you want to assign a different user, open the look-up list and select a user or type the name of a user in the text box.
Used only for People lists, this determines which records, from those meeting the criteria in your list, will be included in the final list. The default value is Individual. Click in this field and select one of the following options:
Individual – the list includes all of the People records that meet the criteria you set. If multiple People records that are part of the same household meet the criteria, all of those records end up in the final list.
Household – the list includes only one record from each household. If multiple People records in the same household meet the criteria, the final list includes the one with the lowest People ID number.
Head of Household – the final list includes only the head of each household, even if multiple People records in the same household meet the criteria.
Unique Address – the list includes a single record per mailing address. The record selected for the final list will always be the one with the lowest ID number, regardless of whether or not that record is Head of Household. This execution filter is useful if your office does not use the Add to Household option for your IQ People records.
Unique E-mail Address – the list includes all People records with a primary e-mail address. All people records without a primary e-mail address will be filtered out of the list results upon execution of the List.
A household includes the following five people records, three of which contain the affiliation code AARP. Your query instructs the system to search for all records that include the affiliation AARP.
|
People ID |
Name |
Affiliation Code |
|
10001 |
John Smith, Jr. (Head of Household) |
|
|
10002 |
John Smith, Sr. |
AARP |
|
10003 |
Mary Smith |
|
|
10004 |
Michael Smith |
AARP |
|
10005 |
Betty Smith |
AARP |
Here are the results of the query for the above records using the following Execution Filters:
Individual results in a list that includes the People records of all three persons in the household whose records contain the AARP code (People IDs 10002, 10004 and 10005). If you sent a targeted mailing to every person on the list, three copies of the same letter would be sent to this household to John Smith, Sr., Michael Smith and Betty Smith.
Household yields a list that includes only one of the three people in the household whose records contain the AARP code. This one record will be the one with the lowest People ID number (People ID 10002). A targeted mailing based on this list would be sent to one person in the household, John Smith, Sr.
Head of Household yields a list that includes only one record. This one record is the one designated as the head of the household (People ID 10001), even though it does not contain the AARP affiliation code, three other household members do contain AARP. A targeted mailing based on this list would be sent to John Smith, Jr., who is the head of this household.
Sort order determines the order in which the selected records are stored in the list. The default order is ID number. The options available for sort order depends on the IQ module from which the list was created. Click in the Execution Sort field to select a sort option. Note: When you use the View List action to view the records in a list, the selected Execution Sort for the list is ignored. Instead, the sorting is controlled by clicking a column heading in the search results page for the list you are viewing.
ID Number – This is the default sort option. Records in this type of sort are stored in sequential order based upon People ID number.
Name – Records in the list will be sorted alphabetically by last name, first name, and middle initial.
Home/Business Zip – Records in the list will be sorted by primary home ZIP Codes. When a record does not have a home ZIP Code, the primary business ZIP Code is used during the sort. If records have the same ZIP Code, the ZIP+4 information is used as the secondary sort.
Business/Home Zip – Records in the list will be sorted by primary business ZIP Codes. When a record does not have a primary business ZIP Code, the primary home ZIP Code is used during the sort. If records have the same ZIP Code, the ZIP+4 information is used as the secondary sort.
Birthdate – Records in the list will be sorted by the date of birth in the People record.
Entered Date – Records in the list will be sorted by the date on which they were entered.
City – Records in the list will be sorted alphabetically by the primary city in the Primary Home address. If the People record does not have a Primary Home address, the city from the Primary Business address will be used instead.
Organization – People selects sorted by this option sort by organization names. The sort is first done on the Org1 field, then the Org2 field, followed by Last, First, and Middle field information.
ID Number – This is the default sort option. Records in this type of sort are stored in sequential order by Mail ID number.
Date Out – Records in the list are sorted by the date on which they were closed.
Date Printed – Records in the list are sorted by the date on which the letters were printed.
Date On Letter – Records in the list are sorted by the date on the letters.
Entered Date – Records in the list are sorted by the date on which they were entered.
ID Number – This is the default sort option. Records in this type of sort are stored in sequential order by the Workflow ID number.
Staff by Contact – Records in the list are sorted by Staff member and then by the primary People contact.
Staff by Workflow Code – Records in the list are sorted Staff member and then by workflow code within the Staff listing.
Staff by Workflow Due Date – Records in the list are sorted by Staff member and then by the workflow due date (oldest due date first) within the Staff listing.
This field allows you to use the criteria, from a selected input list, as the starting point for the criteria in a new List. For example, if list A is selected as the Input List for list B, then when list B is executed, all of the criteria from list A is run first, then the criteria for list B is run against the records found by list A's criteria.
This field can be used to specify a date in which the list will automatically be deleted by IQ. Leave this field empty if you do not want IQ to delete the list. Note: Lists that are used for Search Criteria or Reports cannot be given an expiration date.
Use the Description field to type a brief description of the list you're creating. Information is not required in this field, but is helpful as a reminder and to let others know the nature and purpose of the records in the list.
Identifies how the list created from your query will be used. Selecting the usage alters the options available as you work with your list.
List – is the default usage and is appropriate when you are creating a query that will have limited or one-time use.
Draft Quick Search Screen – is appropriate when you are working on a personal quick search screen for yourself or a query that will eventually be added as an option to the standard Quick Searches available for other IQ users. All users can create draft quick searches for themselves. Only users with the ”r;Publish Select Processor” Security Lock can publish a quick search screen and make it visible to other users.
Published Quick Search Screen – is selected when you would like your query to be available as a standard Quick Search for all users. Only users with the ”r;Publish Select Processor” Security Lock can publish a quick search screen and make it visible to other users.
Detail Report SQL – is selected when your query will be used in conjunction with a report format to present data.
Crosstab Report SQL – is used when your query will be used in a cross tabulation of two or more variables in a report that analyzes data.
The Usage may be changed at any time. For example, a query may initially be a List as you create it and verify the results. As your query develops, you may want to expand it into a custom report.
You may identify a query as a Template so that it can be used by other users to create similar queries. A template can be identified as Draft or Published.
Draft – identifies a query you are working on which is not visible to other users.
Published – identifies a query which is visible to other users as a template to model their own queries. Published templates appear on the Select Template dialog when creating a new list.
Note: All users can create draft templates for themselves. Only users with the ”r;Publish Select Processor” lock can publish a template and make it visible to other users.
The date that the query is created is automatically recorded in this field.
The date that the query is revised is automatically recorded in the field, beginning with the date the query is created.
The date that the query was last executed is automatically recorded in this field. When using a query, check the Executed date to gauge whether the list should be re-executed for more up-to-date results.
The time elapsed to execute a query.
The number of records currently in the list is automatically entered in this field. This is the number of records identified as meeting the criteria of the query.
The Edit Query panel of the List record is where you define the criteria of your query. When you create a new list, the left frame initially contains information about how to edit your query. The right frame is where your criteria statements will appear. The criteria statements show the requirements that must be met in order for a record to be included in the resulting list. The statement is made up of one or more lines that include information about which fields and values will be evaluated.
To begin creating your query, select the Edit button in the right frame. IQ refreshes the page and displays a list of available fields in the left frame for the module you are creating the list. The yellow highlighted line indicates that you are finding records in a specific module. The arrow buttons allow you to view and select the fields associated with that module, and to also reach out to other areas of the database related to the module you are in, known as objects. Once you have selected a field or object, you have the option to select the up arrow to move back to the previous level.
After you have identified the field or object to search for, you need to decide the relational operations to be performed, which are called How Many and Condition. Then you will supply the values (the data from the database) that you are searching for. In most cases, you will select the values from a list of existing values, which can be filtered if it is lengthy. If your query requires multiple criteria, you will select a Connector. Connectors between lines determine how the criteria in each line will be combined with other lines in the criteria statement. As you complete a line of your query, use the Save, Next, Prior and Cancel buttons to complete your query, add another line to your query, return to your previous line or Cancel.
There are three actions on the Edit Query panel that provide three modes of use: Edit Mode, the default mode which allows you to build the query; Copy Mode, which allows you to take individual lines from your query and copy them to a new query; and Bracket Mode, which allows to take a query and apply parentheses to make the intent of your query more clear.
The Screen Builder panel allows you to define which criteria, such as a date range, users will or will not be prompted for when using the query for a Custom Quick Search or Report Criteria. For use as Report Criteria, the Usage field on the Review Query panel is set to Detail Report SQL or CrossTab Report SQL.
The Report Builder panel allows you to setup what columns of data are to be gathered together and how that data should be sorted in the report. This applies to queries in which the Usage field on the Review Query panel is set to Detail Report SQL or CrossTab Report SQL.
The Status Log panel displays the created date and time and the last modified date and time along with a history of all the actions taken on this query. Previous executions of the query are stored and can be restored as the current execution.
The SQL Reference panel contains the query and the actual structured query language (SQL) that will be run to create the criteria based list. This information is provided as a convenience in debugging any problems and to confirm that a query is set up properly to achieve the intended results.