# Scanning For Records

Scanning, an alternative method of finding records, matches each record to entered criteria. This method for finding records is called "scanning", because each record is tested (or scanned) to see if the values in its fields match those criteria designated by the user. The selection criteria is entered as a "query" in either short or extended format.

If a record matches your criteria, it is brought to the screen. Successive matches can be seen from that point by pressing ENTER to go to the next matching record, or by pressing B to browse the next 18 records which match your criteria (if there are that many).

Short Selection

Select 2 Scan For Records.

Select 1 Short Selection.

filePro tests matching criteria using the de-facto standards, "equal to", "not equal to", "greater than or equal to", "greater than", "less than or equal to", "less than". It also has a valuable addition to these in "contains". Besides these criteria matching operators, it also adds a unique tool to speed building queries "range". All of these operators are spelled as closely as possible to what they mean, i.e., EQ means "equal to", GE means "greater than or equal to", CO means "contains" and RG means "range". The following screens show how to use these operators.

Enter the following query and press ENTER at the "Enter Connective (and/or)" prompt.

This query will search the city field of every record for the word pat.

The first record that matches the scan criteria is displayed.

To see all records that match your criteria, press B for browse.

Without moving the highlighted bar, press ENTER to take you back to the full screen view of the record on which you are standing (Mark Farmer). You will notice a new sign on the bottom right corner of the screen. It says, "ENTER for Next Match" (The little left-pointing arrow means ENTER). This is very important! It means that you must press ENTER, not the Down Arrow to go to the next matching record in your scan. Once you press ENTER to move to the next matching record, you can NOT press the Up Arrow to go back to the record on which you were standing. Scanning, unlike indexing, does not move the records around to organize them. It would be as if you had an address book in which you randomly wrote names and numbers. If at some point, you went through the book and highlighted all of the local phone numbers, you could flip through and find them quickly, but the records themselves would not be ordered. That is what filePro does when it performs a scanit shows you the highlighted records in a list, but if you use the arrows to navigate, it moves you through the other records. This will be shown more clearly later in this section.

Note the sign on the bottom right of the screen.

Press ENTER now, and you will be brought to the next matching record in full screen view.

Press ENTER again to see the next match. Since there are no more matching records, you will see the following screen:

Try another scan by pressing 1 - Short Selection.

Enter this query.

The criteria GE means that records must contain data that is "greater than OR equal to" the data designated in the query. If the data in the field being used is all numbers, then this means a match on numbers that are higher than or equal to the supplied number. Even though field 7 is a ZIP code made up of 5 characters, they will all be numbers and filePro will compare the first three characters of each record's field 7 against our test criteria... in ascending order through all 5 characters of the field. In other words, 07900 and 08000 and 09999 and 99999 will all be "greater than or equal to" the "079" we have entered. Zip codes of "07899" and lower will be excluded.

If you perform the query above by pressing ENTER, you will be brought to the first record that matches.

Press B to browse the results, and you will see the following screen. However, no matter which browse format you are using, there is no ZIP code on it! But, you do know how to add a field to the browse format. Do that before looking at any of these records.

Press F and update the screen to add field 7 to the browse format line. Put the Zip code field to the right of the other fields on your screen and enter the header "Zip Code".

When you are done adjusting the browse to include the Zip code field, press X again to view the records found by your scan. The screen should look something like this.

IMPORTANT: Notice that the correct records have been retrieved. They are all greater than or equal to 079, but they are not in sorted order! The records may not be in any special order. As stated earlier, scan normally does not use indexes to do its work. Scan merely stops at every record (in record number order) and tests to see if the criteria for the query is met. If so, it selects the record, if not it goes to the next record and tests it. You will see only records that match your query, but they are not necessarily in any particular order. Since scan stops on every record in the file to do its testing, it is VERY slow compared to using an index to find a particular match. Using an index to find the first 079 in a large file of records causes filePro to jump over all the records lower than 079 (as this indexed field data is in ascending numerical order) and stop immediately at the first one that is equal to or greater than the criteria (079). All you need to remember now is how scanning and indexing are different. It will help you in future use of filePro.

One of the additions to de-facto queries implemented by filePro is the RG (range) function. This operator will automatically prompt you for the lowest match that you are looking for, and then prompt you for the highest match you want. It speeds up the query entry considerably.

Enter the following query. When you choose RG, it will automatically ask you for the lowest and highest limits for the zip code.

After filePro brings you to the first matching record, press B to see the full browsed list of matches shown below.

Sometimes a query will bring up more than one screen full of records. In these cases, you can continue to use browse and the Page Up, Page Down keys to view the records that match the query. While on the browse screen you can also use the Up and Down arrows to view the retrieved records, but remember, if you are on a full screen, you can only go "forward" in the scan by pressing ENTER. If you DO press an UP or DOWN arrow during a scan selection from a full screen, you may see a record that DOES NOT match your criteria. To see this VERY IMPORTANT concept about scanning graphically, do the following:

Enter a query selecting by Cities that are not equal to Pat. It should look like this:

When the first matching record is retrieved, press B to browse the records. You can move the highlighted bar with the arrow keys to any record in the selection and you will never come to a record that does not match the criteria. However, put your highlighted bar on the following record (Hakan), then press ENTER to go to the full screen view of this record.

Full Screen View:

Press ENTER to see the next record of Smith.

While on the full screen view of Smith(record #2), press ENTER and you will be brought to the next match, Hoben (record #6).

Everything is still working as you would expect, you are only seeing records within the desired selection set.

However, now press the Up arrow and you will see that you are brought to Hall (record #5) and this record does NOT match the criteria. The city is EQUAL TO Paterson and we asked for city NOT EQUAL TO Paterson.

So remember to use the ENTER key to find the next match when on a full screen view.

CO Operator

The CO operator asks whether the specified field contains the supplied criteria anywhere within it. For example, does an address field contain the word "Avenue"? An address of "23 First Avenue" would match this criteria. Imagine that you have forgotten someone's phone number. All you can remember is that it had "4444" in it.

Enter this query, using the CO "contains" operator.

You should see two records for this selection as follows.

You may have noticed that certain fields on the rolodex screen have a p) in front of them. This marks them as associated files. Associated fields are unique to filePro and they are a very powerful feature of the program. They allow you to perform a query on a group of fields at one time without having to specify each field individually. If the desired criteria is found in any of the fields within the associated field group, the record will be selected for inclusion in the selection set.

To put an associated field group in a query, use its associated field "name". Since the "rolodex" file has an associated field group defined as p), this is what you use on the selection set screen.

Enter the following criteria:

The following records match the criteria.

NOTE: We changed the browse format to reflect two phone numbers.

Testing for one piece of criteria is fine, but not very powerful. How can we limit or expand the criteria searching capability? This is where the "Connective..." prompt we have been skipping comes in. With this, you can test two different criteria at once. We can use this connective to specify that both criteria have to be true (the first AND the second), or that only one OR the other has to be true in order for a record to be retrieved.

Assume you have made brownies and want to deliver them by hand to only your local friends who like them. By performing the following search, only records for people in town will come up.

Enter a query that selects records containing the word brownie in either of the note fields. At the "Connective..." prompt, type the word "and". Now enter a query that selects records containing the word wash in the city field. It should look like this:

The only records to match will be the ones that "contain" the word brownies in either field 12 or 13 "AND" the city is "EQUAL" to Washington.

To see the connective feature on Short Selection work as an "OR", lets try the following criteria.

Enter a query that selects records in which the company name is not empty OR the email is not empty. In other words you want filePro to pick out the records that contain any company name or any email information.

While this may seem difficult at first, try to think of how you can represent blank fields. As you can see below, it is still just simple criteria.

The above selection will bring up all records where the company field OR the email field has something in it. (Not equal to "blank" means there is "something... anything besides space" in the field.

Extended Selection

The "Connective..." prompt on Short Selection is sometimes not enough for complicated queries. Besides, you may want to save a query for later use (so you don't have to type it all in again). Extended Selection addresses both needs. Queries can be made up of many, many connectives (and, or and even not). In fact, if one screen full of connectives is not enough, you can attach up to five screens together!

Enter the Extended Selection screen.

From the Scan for Records menu, Select 2 Extended Selection.

The same query we did on Short Selection for locals who like brownies would be done as follows using the extended format screen:

The extended selection screen has a set of rules for how lines work with each other. The most important rule is that each line automatically defaults to the and connective. In other words, each line of the screen has to be true for a record to be considered a match. This is always true, until you learn how to override this default.

There are several ways to make the lines become OR conditions instead of the usual ANDs. The simplest of these is to "group" the lines you want to be OR conditions in the same group. A group can be any "name", i.e., "a", "abc", "fred", "group1", etc.

By naming the two lines from the previous selection set as "a", they will not both have to be true in order for a record to match this query. Now, either one OR the other being true means the record matches and we want to select it.

If you try the selection set above, it will select two records. Each record has at least one of the criteria met.