Adding a Query

All queries must contain accurate Query Conditions. You can run a query with just the columns (data fields) entered in the Query Conditions tab. Conversely, the Query Columns and Query Order tabs are completely dependent on the data entered in the Query Conditions.

Query Conditions Tab

The Query Tool page defaults to the Query Conditions tab.

1. Select a query condition from the Column to Query drop-down list.
2. The page will refresh.
3. Select a Boolean Operator from the Operator drop-down list. (See Is In for use of the Is In operator.)
4. Enter a value in the value text box (OR select or clear the check box or select a value from the drop-down list).
5. Enter an amount in the Limit Number of Results Returned text box (OPTIONAL).
6. Select (Default) or clear the Distinct? check box.
7. Select or clear the Ask at Run-Time? check box.
8. Click Add to Query.
9. The condition will display in the Query Where section.
   • If you selected the Ask at Run-Time? check box, (Ask At Run-Time) will display to the right of the Order Column (conditions).
   • The Ask at Run-Time? check box will automatically clear.

10. Select a group from the Group List drop-down list (OPTIONAL).
11. Click the Deny Selected Group button if you wish to deny a specific group access to the query (OPTIONAL).
12. Click Run Query.
13. If the Ask at Run-Time? check box was selected, the Query - Ask At Run-Time Values page will display.
14. If the value is still valid, click Go.
15. A List page will display with the results of the query. (The Profile page will display if only one record is returned.)

Multiple Condition Queries

Like the Find page, netFORUM assumes an AND between each line of the query. Thus, the record must meet all of the criteria for the query to be returned. Conditions are separated with an OR and grouped together with opening parenthesis and closing parenthesis. If you open a parenthesis, you must close it or the query will not return the desired results.

The following example for this section is a query of Individuals whose member flag is cleared versus selected with a specific city i.e. a query of individuals in the city of Falls Church who are members versus non-members.

1. To add an opening parenthesis, click the Add( button.
2. Select the first query condition from the Column to Query drop-down list.
3. Select a Boolean Operator from the Operator drop-down list.
4. Select the value check box (or enter a value in text box or select from the drop-down list).
5. Select the Ask at Run-Time? check box (Optional).
6. Click the Add to Query button.
7. Select the second query condition from the Column to Query drop-down list.
8. Click the Add to Query button.
9. To add a closing parenthesis, click the Add) button.
10. To add an OR, click the Add Or button.
11. Repeat steps 1-9 for the second set of conditions.
    • Note the Ask at Run-Time? check box was not selected with the first column.
    • You will need to clear the value check box to create the second query set.
12. Click Run Query.
13. The Query - Ask At Run-Time Values page will display.
14. If the value is still valid, click Go.

Note: In this particular example, by selecting the Ask at Run-Time check box, you are ensuring that those select Individuals are still members at the time you run the query.

15. A List page will display with the results of the query.

Note: Not all queries will require the OR separator and begin with or have parentheses. This is a Boolean query and thus will be executed exactly the way you write it (just like an algebraic formula).

Comparing Columns in a Query

Queries can be built to compare two columns in the same query by clicking the Use Column? checkbox.

The Use Column? checkbox can only be used to compare columns that have the same data type. For example, you cannot compare a date column to a numeric column, but you can compare a decimal column to an integer column. In addition, the Operator that is chosen must not be an "in" operator (such as "Is In" or "Is Not In".)

Clicking the Use Column? checkbox will expose another drop-down menu to allow you to pick the two columns to compare.

You will continue to Adding a Query just as you would any other query.

Note that the comparison columns will only list columns contained in tables in the immediate query. If you have a "main" query with a Sub-Query, the Sub-Query cannot compare a column to columns from the main query.

Query Columns Tab

You will notice in the example query, that the member flag does not display on the standard profile record in the query results. In the Query Columns tab, you will be able to set up which columns (fields) will be displayed in those results.

1. Click the Query Columns tab.
2. Select your first column from the Available Columns drop-down list.
3. Click the Select Column arrow between the drop-down list and the Display Columns panel.

4. The selected column will move to the Display Columns panel.
5. Repeat the process for multiple column selections.
6. The columns will display in the order they were selected and once selected, will be removed from the Available Columns drop-down list. (Note: The order in which the columns are displayed in this panel is also the order in which they will display on the results page.)
7. To return a selection to the Available Columns drop-down list, click the selection of your choice, click the Remove Column button.
8. To move a selection up or down in the Display Columns panel, click the selection of your choice, click either the Move Column Up or Move Column Down buttons to the right of the panel.

Tip: On the Query Columns tab, you can now move items between the Available Columns and Display Columns lists using the drag-and-drop feature. When using this feature however, do not release the mouse button before dragging the items to the other column.

• To select multiple items that are next to each other, select the first item, press the SHIFT key and click on the bottom item.
• To select multiple items that are NOT next to each other, press the CONTROL key and click on the items.

Query Order Tab

After selecting the columns (fields) to be queried, you can sort the order in which they will be displayed in the Query Order tab. In the working example, the Individuals will be sorted first by a cleared member flag, then by a selected and both sections will ascend alphabetically.

1. Select the first column you wish to sort on from the Column to Sort On drop-down list.
2. Select a display direction Direction drop-down list (Ascending (A-Z) or Descending (Z-A)).
3. Click the Add to Sort button.
4. The result will display in the Query Order By section.
5. If necessary, repeat Steps 1-3.
6. Click Run Query.

7. The final query result page will display

Grouping Tab

The Grouping tab provides the ability to aggregate the data that is returned on the queries you create. Notice in the screen shot above that there are three sections that make up the Grouping Tab; the Group By drop-down list, the Aggregate Columns drop-down list, and the Having drop-down list.

These three fields are used to group and sort your queries into more manageable and informative data. For example, by using the Group tab, you can now run a query to find out upcoming expiration dates and sort the result by Member Types having greater than 5 upcoming expire dates with aggregated data columns displaying count, expire date, effective date, and join date.

The first step is to decide how you want to sort or group your query results. In the example used above, you are requesting data on upcoming expiration dates to be grouped by Member Type.

1. In the Group By drop-down list, select the date you want your results grouped by. In this case, Member Type, although you can add more than one.
2. Click the Add to Group By button to add the output specification to the query

Once you have decided upon groupings, decide how you want the data returned aggregated by column. For example, in addition to the user records that will be returned for this query, the aggregate columns can also be viewed by the expire, effective, and join dates for this query.

1. In the Aggregate Columns drop-down list, select the data you want the query results to be sorted into columns by. You can add more than one column.
2. Click the Add to List button to add an aggregate column.

Finally, use the Having drop-down list to place any restrictions on your results. For example, in this example, only results greater than 5 records will be returned.

1. In the Having drop-down list, select what restrictions you want to place on your date (if any)
2. Click the Add to Having button to add your restrictions to the query.

Click the Run Query button as you normally would once you have specified the aggregate groupings you want on the Grouping tab. You will end up with similar results as seen below.

Notice that the results are grouped by Member Type as specified in the Group By drop-down list with each column in the Aggregate Column also returned. Finally, also notice that the Having restriction (only Member Types having records with expire dates upcoming > 5) is also in place. If you click the Expand button next to each grouping, you will see each of the actual records returned.

As always, the green GoTo will take you directly to an individual record.

Save Query

Use the following steps to save a query that you want to reuse:

1. Enter a query name into the Query Name text box.
2. Enter query comments into the comments box. This is intended to be a description of the query and its function so that you no longer have to run or look at the code of the query to find out what it does.
3. Click the Private Query check box if you intend that only you can use or view this query. Only the person who created the query will have access to it if this check box is selected.
4. If you intend to restrict access, use the Group List drop-down list with the Deny Selected Group button to do so.
5. Click Save Query.

Note: A saved query will be available for all netFORUM users unless you configure Query Security or select the Private Query check box.

3. The query name will now display on the results page each time you run the query.

Editing a Query

You can edit several parts of a query in the Query Conditions tab:

• The Order
• The Column
• Deleting a column

Editing the Order

Click the arrow up or arrow down buttons, to move the column to it's correct position within the query.

Editing a Column

1. Click the edit button to the left of the incorrect column.
2. The Query Where section will fade and the column will return to the Column to Query section.
3. Make the appropriate edits, click OK.

Deleting a Column

Click the delete button to the left of the column you wish to delete.

Updating a Query

Click the Update Query button to save any changes made to an existing query.