Simple Filter

The filter screen allows the user to create basic filters on a single table. These filters can be joined over multiple tables using the Complex Filter.

Accessing the Simple Filter

School/Contact – Click the Complex Filter button . Select the table you wish to filter on from the Selection Type. Right click the in the grid under the filter column. The Filter screen will be displayed. For more information on the Complex Filter see the Complex Filter help section.

All other screens – Click the browse/filter button  to display the Simple Filter.

 

Version difference: In the FoxPro version of e-MkIS clicking the browse/filter button  on the School/Contact screen will display the simple filter. In the SQL version a grid of the currently selected records will be displayed. Selecting one of these records will display the record without changing the current filter.

 

Using the Simple Filter

Once you open the Simple Filter you will be presented with the screen below:

 

 

 

 

 

 

 

Explanation of Operators Drop List

Operator Expression	Function
equal	Has this criterion at the beginning with anything following.
not equal	Not Equal to the criterion
equal any	The data contains any of the following criteria
like	Field contains the criterion
not like	Field does not contain the criterion
less than	Less than the criterion
gtr than	Greater than the criterion
less or equal	Equals to or Less than the criterion
gtr or equal	Equals to or Greater than the criterion
exact equal	Exactly Equal to the criterion
empty	There is no data present in the field
not empty	There is data present in the field

 

Join Condition	Function
and	Select all records which match Criterion1 AND Criterion2
or	Select all records which match Crierion1 OR Criterion2

 

       Depending on the type of field selected different operators are available.

Note fields can use like, not like, empty or not empty.
Text fields can use all operators.
Date fields can use less than, gtr than, less or equal, gtr or equal, empty or not empty.
Drop down lists can use all operators and most provide a selection list.
Numeric fields can use all operators other than like and not like.
Check boxes can only be true or false.

Creating a Simple Filter

This example is filter on the School table.

In this is example I want to find all schools in Hampshire with more than 200 students with some notes.

If we break the criteria to individual criterion we have the following:

1. Area equal Hampshire AND
2. Number of students greater than 200 AND
3. Notes not empty

       To create this filter is simple:

       1. Select Geo Code/Brick Code from the field list. Select equals from the operators list. You can now either type ‘HAMP’ into the text box or click the question mark. Clicking the question mark will display a list of areas from which you can select Hampshire. To add this part of the filter click the Add button. You should now have the filter below:

      

       2. Criterion are automatically joined together using the AND operator. To add the next part of our criteria select No Student from the field list and gtr than from the operators list. Now type 200 in the criterion text box and click the Add button. Your filter should now look like this:

      

3. For the final part of our filter select Notes from the field list, not empty from the operators list and click the Add button. We now have our finished filter:

Before we commit the filter there are a few other options we should try out. Click the View button to verify your filter:

Here we can see a pseudo version of the filter. As you can see it is very similar to our written requirement “all schools in Hampshire with more than 200 students with some notes”. Another option we have available is the Browse button. Clicking the Browse button will display a summary table of all the records the filter will select. At the top on the browse window is the number of records which will be selected.

Once you are happy with the filter you can click the Close button and depending on what screen you are on the filter will be applied or you will be taken to the Complex Filter screen. If you are taken to the Complex Filter screen, click the Select button and then click the Exit Filter Set button to apply the filter.

Using the OR condition                            

Let’s continue using our current filter but we now we want to find all the schools in Avon with more than 200 students with notes. To use the OR condition click the last line of the criteria and the click the Group button.

We have now inserted an OR condition. You should easily be able to finish the criteria using what you have already learnt:

There is another way of creating this filter using the equal any operator…

Equal any

We are now going to modify the filter to use the equal any operator. We could use the Reset button to clear the filter and start again, but let’s practice using the Delete button. Click the line with the OR condition and press the Delete button four times also delete the top line. You should be left with:

Make sure you have a blank line selected on the criteria box. Select Geo Code/Brick Code from the field list and the equal any operator. Click the question mark to display the Multi Select screen. Select AVON and HAMP from the drop down list:

Click the OK button and then the Add button on the filter screen. You now have a shorter filter which gives you the same results as before:

This completes the instructions on the Simple Filter but there are some other options on this screen you should be aware of:

Order drop down list only works in the non SQL version of e-MkIS
Up/Down buttons allow you to rearrange the order of the criterion in the criteria.
Report button allows you to create a report on your filter. Please review the Reporting section of the help file for more information.