Query
The Query Group Item Link allows you to use a more complex set of criteria to search for records than the standard Find group item link.
Clicking the Query group item link will take you to the Query Tool page. The Query Group Item Link and the Query Tool page are standard through all netFORUM modules. However, the tables used to create queries are specific to the module within the query tool. Only data pertinent to the current module is available for you to use in the query.
Compared to the Find tool, the Query tool allows you to make more refined and specific searches of the processes in the database. The Query tool allows you to use Boolean search characteristics such as combinations of and, or, not, and parenthesis to create complex search criteria.
Find vs. Query
The Find page has a predefined set of fields, which are the criteria for your search and represent the columns of tables in the database. The Query search allows you to select any column in the search table as the criteria for your search.
User Interface Elements
- Tabs
- Query Columns - allows you to choose which columns display in the query results and the order of those columns.
- Grouping - enables you to group and aggregate your query results for a more informative and concise data display.
- Query Order - allows you to determine how the query results are sorted.
- Security - allows you to provide access or deny access to this query for various user groups. See the Query Security topic for more information.
- Fields and Menus
- Load an Existing Query - allows you to load a saved query.
- Limit # of Results - enables you to determine how many results to display.
- Randomize Results? - selecting this check box sorts results in a random order.
- Distinct? - selecting this check box eliminates duplicate records based on the columns requested in the query.
- Column to Query - use this drop-down list to select the fields to query. The tables available to you to create queries are specific to the module within the query tool. Only data pertinent to the current module is available for you to use in the query.
- Operator - use this drop-down list to determine the operator netFORUM uses when running the query (such as Is Equal To)
- Value - allows you to enter a value to query. For example, if you are running a query on a certain Batch Name, enter the Batch Name in this field.
- Query Name - allows you to save your query by entering a name in this field and then clicking the Save Query button.
- Comments - allows you to enter comments or a description about your saved query.
- Private Query - selecting this check box makes the saved query private so that only the user who added the query can see or run it.
- Group List - use this drop-down list to choose which user groups can or cannot see your saved query. You can select a group from this drop-down menu and click the Deny Selected Group button to keep that group from seeing your query in netFORUM.
Note: Column to Query values are in the following form: Table Name::Field Description. Use the Field Description to help you decide what column to select. Column to Query values are grouped by Table Name with the Field Description alphabetized within each Table Name group.
- Buttons
- Add ( - allows you to add a left parenthesis to your query statement for grouping conditions.
- Add to Query - allows you to add a query condition to your query.
- Add ) - allows you to add a right parenthesis to your query statement for grouping conditions.
- Add Or - allows you to add an Or condition to your query. All query conditions are automatically And statements unless you manually add an Or statement.
- Deny Selected Group - clicking this button adds the user group selected in the Group List drop-down list to the Denied Groups list. Groups in the Denied Groups list cannot view your query in netFORUM.
- Save Query - clicking this button saves your query so that you can access it later. You must provide a name for the query in the Query Name field if you want to save it.
- Run Query - clicking this button runs your query based on the conditions you have entered.
Note: The Deny Selected Group, Save Query, and Run Query buttons are not displayed until you choose a saved query, or you enter conditions into a new query.
Using the Query Tool
To Navigate to the Query Tool:
- In the Module page, select a Group Item.
- Select the Query Group Item group item link, from the Group Item menu.
- The Query Group Item page opens.
Creating Distinct Queries
Clicking the Distinct? check box on the query tool eliminates duplicate records based on the columns requested in the query.
- Joe T. Pro registered for two events. You run a query for individuals who registered for either event. You request the following Query Columns (fields): First Name, Last Name, Individual Type, and Age. You clear the Distinct? check box.
- The query returns Mr. Pro twice, once for each event.
- If you select the Distinct? check box, and run the same query, then the query returns Mr. Pro only once. Selecting the Distinct? check box causes netFORUM to filter out one instance of Mr. Pro, because the columns returned are identical regardless of which event he attended.
- If the query columns are modified to include the attended? check box on the Event Registrant profile and the Distinct? check box is still selected, the results change as follows :
- If Mr. Pro attended both of the events, the query returns Mr. Pro once because the attended? check box is selected for both events.
- If Mr. Pro attended one event but not the other, the query returns Mr. Pro twice because the attended? check box is selected for one event and not the other, making the two events distinct.
Note: The query returns zero records if you select the Distinct? check box and a text-type column. The database does not allow a distinct query on a field type of text because the database stores a pointer to the actual text. The pointers are always different, even if the text is the same. The work around is to either remove the text-type field from the Query Columns, or clear the Distinct? check box.