![]() |
FoxPop |
www.foxpop.co.uk |
POWERBASE TUTORIAL 6
by Laurie Jane Kern
Searching: Find, Filter and Groups - A lesson in 3 parts
Tutorial 6b - Filter
If you have not already done so, please download the most recent version of the Sample Address Book (06), which you need for the whole of tutorial 6.
In Tutorial 6a you saw that Find is an easy tool to use, but it has its limitations: it is not necessarily fine enough when you need to narrow your data down to an exact match. At the moment, doing a Find with 12 records and having it return 4 does not present much of a problem. If you do not find what you are looking for, you only need to look at the 4 records returned by Find, and you can locate the one you need. But when a database gets big - and it will! - you will arrive at a situation where a Find might return as many as 10 records. That is where Filtering comes in. Filters operate by matching all records for which an expression evaluates as 'TRUE'. To show you how this works, we will do a simple Filter that we could do with a Find and build from there.
Open the Contacts Table in List View and maximize it by clicking the middle button in the upper right corner. Start by doing a Find on the letters 'mr' and see how many records you get. I get 5 matches. Look through them, and you will see that only 3 records have the letters Mr in the Title Field. In the other 2 records the letters "mr" must be in a word in one of the other Fields. Now do another Find, this time using the letters 'auth' which is part of the word "author". I get 3 matches. But how many men are also authors? That is what we are about to find out!
To start, make sure the Contact Table has focus and then click the Filter button on the toolbar, on the right hand side of the screen, (or from the menu go to Records|Filter.. or press CTL+F). The dialog box that pops up (shown below) is called the expression evaluator and it is where you will build your Filter expression.
On the lower left is a list of available functions that can be inserted into your expression, and on the lower right is the list of available Fields you can use in the Filter expression. You are going to build the expression you see above. First click in the Field list, and you will see a complete list of all the Fields you created when designing the database. Select the Field named Title. Now click the Insert button and the Field name will be inserted into the upper portion of the Editor. Now click in the Editor and type the following: ="mr" then press the Test button. If all is correct you will be presented with a 'No error found' message in the upper right corner of your screen. If you get a different message, go back and see if you can find what is wrong.
When you are ready, click the OK button, and apply your Filter. What did you get? If you had NO records returned, then the Filter worked correctly! Why is that? Well - in your Title Field the letters are 'Mr' NOT 'mr'. But you may remember 'mr' worked for the Find function, why not here? Remember that the Find properties were set to find any string match - it was not case sensitive (upper or lower).
So go back to the expression evaluator and restructure the Filter so it looks like this: [Title]="Mr" (you can just retype the Mr) and then press the OK button. This time you should have only matched 2 records, not 3 as you had before. If you look closely you will discover the reason: one of the records actually is 'Mr.' (there is a period in the Field). You may want to go and look at the Table to verify this. Put the cursor in the Find box and press the ESC button or the Enter button, either of these will un-apply the Filter. You should now have 12 records to look at. Find our friend Bill Gates and look at the Title Field. Remember that I stated that Filters operate by matching all records for which an expression evaluates to 'TRUE', and "Mr" is TRUE whereas "Mr." does not evaluate as TRUE.
You are now going to add 2 functions to your expression to make it a bit more flexible. I will explain what you are doing after you have built the expression. Open up the expression evaluator and erase the last Filter. Using the list of functions on the bottom left of the box - click and look at the full list you will find there. Select the functions 'Find' and 'Upper$', and insert them to build the expression as it appears below, move the cursor as required before doing an insert or typing. Then test that the evaluation is correct. When you are content with your expression, press OK. You should get 3 records returned.
I shall now explain what this expression is doing. The function Find returns the position, in the Title Field, of the 1st occurrence of the string "MR" (no period). Notice that we used the upper case "MR" not mixed case "Mr". Then since the Title Field does have mixed case, we are converting it to upper case with the function Upper$(). I converted the string to uppercase, in case the data entry in the Title Field had been written as upper case, lower case or even mixed case. The last part of the expression tests to see if the Find returns the POSITION of " MR" equal to 1 (at the start of the Field) If the letters "MR" were anywhere else in the word, the expression would not evaluate to TRUE.
Your final expression will further narrow the search by selecting those contacts with the title of Mr who are also authors. Enter the following expression in the evaluator:
Find(Upper$([Title]),"MR")=1 AND [Contact Type] = "Author"
Test your expression and then press OK. You should see that ONLY Mr Charles Dickens meets these criteria.
The last task to do as part of this lesson is to remove the Filter, and then look at the records for Emily Bronte and Jane Austen. These ladies have the title of Ms and this does not fit our string "MR", which is why their records are not returned as a result of our filters. Yet what would happen if you were to edit their Titles and change them to Mrs? Think about how you might change the filter so they were not selected if this was the case.
Let me close by mentioning some additional information about the Editor. There are many functions that can be used in the Editor; I have only used 2 here. The available functions are documented in the Help File as well as the PDF manual. There are many functions that can be used to evaluate numbers as well as strings. Below are some important points which you should remember.
![]() | Any item enclosed in square brackets is assumed to be a Field
name and MUST match a Field name. When you are more experienced, if you know what you want
to evaluate, you can type it directly into the evaluator. |
![]() | Any items enclosed in double quote marks are assumed to be
text values. |
![]() | Any item that does not meet either of these two rules is then
assumed to be either an operator (=, +, >, <, ...) an operand (1, 2, 3,....) or
function name (Abs, Sin, Eval, ... ) |
![]() | You cannot use a Memo Field in an expression. If you look at the list of available Fields in the expression evaluator you will see that the Notes Field is not in the list. |
And finally to close, the expression evaluator is the same tool that is used for a calculation Field, specifying a default value for a Field and for building Groups, which you will be doing in the next lesson. I hope to see you then!
Keep the same Sample Address Book (06) for the next tutorial.
© LJKern and FoxPop 1999