Selection Criteria

 

Purpose:  To specify the records to be selected in the report or view.

 

Launch Point:  The selection criteria window is available from a wide variety of reports and views.

 

Example:  When selection criteria is launched a window will open

As you create the criteria sets, you are building a query that the system will use to pull records. For each criteria set, you are starting with all the records in the database. As you add fields to the criteria set, you are filtering down the number of records that will be returned. It is important to keep in mind where the “and” and “or” are placed in the criteria as you build your criteria set(s).

 

Note:  To be able to fully utilize the selection criteria window, you need to know what data is in your system and how that data is kept. The integrity of the data also plays a large part in the quality of the results you will get.

 

Generally, within a criteria set, there is an “and” between each field. However, for fields that allow for multiple selections, there is an “or” between each of the selections within the field.

 

In between each criteria set is “or”. So each time you add a new criteria set, you essentially start fresh with the entire database again. If one record meets the criteria in multiple criteria sets, the record will only be returned once in the resulting data.

 

So, for example, if you wanted to find records that had a status of member and preferred chapter of Richmond or Northern you would use one criteria set, and it would look like this:

 

The first field to work with is the status.  The status is found in Names file maintenance.

 

Open the Pick Table drop-down list and select Names – Master File as the table to work with. Once the table is selected the fields from that table will be displayed in the Pick Field grid.

 

Note: The listings in the Pick Table drop-down list will vary depending on where selection criteria was launched from.

 

In the Pick Field grid, the default sort is on Tab. Then, within those groupings, each field would be listed in the order in which it appears on that tab within file maintenance. If you would like to change the sort to be on the field name, click on the Field header in the grid. To set the sort back to the default right-click anywhere in the grid and select Default Sort.

 

When you locate the field you want, you can select that field either by double clicking it in the grid or by highlighting it and then clicking the Select button.

 

Once the field has been selected, it will be added to the current criteria group under the active criteria set.

The lower section of the window will also show the appropriate information for the type of field selected.  In this case, the status field is a drop-down field, so a list box is shown with include/exclude options.

 

Click anywhere in the Specify Items to include/exclude in selection box to open the selection window for the field.

 

Move the status “Member in Good Standing” from the Unselected Items box into the Selected Items box. You can do this by highlighting the listing on the left and clicking the Move Item(s) button, double clicking the listing on the left, or dragging and dropping the listing from the left box to the right box. Click OK to return to the selection criteria window.

 

The selected value(s) for the field will now be displayed in the Specify items to include/exclude in selection box. Check that the appropriate include/exclude radio button is selected.

 

The next field to work with in this example is the preferred chapter.  This field is also in the Names file maintenance table so you don’t have to change the Pick Table.  Select the “Preferred Chapter” field from the Pick Field grid.

 

The current criteria will now show both the fields in criteria set 1.

 

As described above, select the chapter listings to work with.

 

This query will pull records where the names status equals “Member in Good Standing” and names preferred chapter equals “Richmond” or “Northern”.

 

To run the criteria, click the Run button at the bottom of the selection criteria window.

 

If you ran this from an export, label, or mail merge, you will see the number of records pulled shown on the left side of the report wizard.

 

Note:  To go back to the selection criteria window, click on the Done bar. The criteria last run will be displayed.

 

By default, the selection criteria window is opened with Criteria Set 1 in the Current Criteria. You are not limited in the number of criteria sets you can add on the selection criteria window.  

 

If you need to add additional criteria sets, click the Add Criteria Set button. Note: The number assigned to the new set will be sequential. If you delete a criteria set, the remaining sets are not re-numbered. If you have 5 sets and you delete criteria set 5 the number on the next set added will be criteria set 6.

 

To delete a field in a criteria set, click the red X to the left of the row in the Current Criteria grid.

 

To delete an entire criteria set, first highlight the criteria set to be deleted and then click the Delete Criteria Set button.

 

To load a previously saved criteria, click the Load button in the lower left corner of the selection criteria window.

 

The Criteria Browser window will open, showing all the saved criteria on your system. Either double-click in the Name column on the row in the grid, or highlight the row and click the Open button to open the criteria in the selection criteria window.  Note:  Any existing data in the Current Criteria will be replaced with the saved criteria being loaded.

 

If you want to save a criteria that you have created on the selection criteria window, click the Save button in the lower left of the selection criteria window.

 

The Save Criteria window will open. If this is an update to an already existing saved criteria, click the Select Criterion to Update button.

 

This will open a window of existing saved criteria. Highlight the criteria to overwrite with the existing setting on the selection criteria window, and click the Open button.

 

The Save Criteria window will then be filled in with the data from the selected saved criteria.  Update as needed and click Save button to update the criteria that is saved.

 

If the criteria to be saved is new:

When the Save Criteria window opens, fill in the Name, Description, and Category fields.  Select the appropriate Security setting and click the Save button.

 

To delete a saved criteria, click the Load button to open the Criteria Browser window. Right-click on the row to delete, and select Delete from the context menu.

 

 

As noted above, the lower section of the window will show the appropriate information for the type of field selected. Below are examples for different field types.

 

The names last name field is a text field. When the Match Type is set to From/To Range the Range of Matches will show a From and To text field.

 

If the Match Type is set to List, the Range of Matches changes to Items to Match. In the top row, enter the text to match. When you hit enter, the text will be added to the list. Keep entering text to match in the top row.

 

If the Match Type is set to Contains, the Range of Matches changes to Contains Match. In the text box, enter the text you want to match within the field.

 

 

The names status field is a drop-down field. The include/exclude options control how your selection(s) in the field are handled.

 

Include Matching Items – the record must have a match to one of the listings selected

Exclude Matching Items – if the record has a match to one of the listings selected it is excluded

Include if field is empty – the record is selected if the field is empty

Include if field is not empty – if the record has data in the field it is selected

Exclude if field is empty – the record is excluded if the field is empty

 

Note: When you select one of the bottom three options, the list box in the middle goes away. That is because for these options no specific listing(s) need to be selected.

 

The names birth date field is a date field. The Match Type options include the From/To Range and List discussed above. In addition there is an option for Month of Year Selection. 

 

When this Match Type option is selected, you will need to select the Month of Year from the Select Month drop-down list.

 

 

The names AICPA member field is a check box (yes/no) field. Note: In some fields, the value in the field can also be blank.  Depending on the field, a blank value can either be equal to “Yes” or “No”.

 

The dues balance field is a numeric field.

 

 

The committee’s position field is a drop-down field. Here the Assignment Position is also looking for service dates.

 

The Events Attended field is pulling data from the events ledger. There is a second list box for the associated registration status. By default this field is set to exclude cancelled and no show.