• Home
• About Us
• Development
• Training
• Support
• Hints and Tips
• Legal

Records affected by a FoxPro command

Many FoxPro commands can be modified so that they only affect a certain group of records. There are six modifiers. Two are similar to the Where clause in an SQL statement and select records which meet a logical criterion. The others rely on the physical position of the record in the table and must be used with care.

Looking at the FoxPro commands which deal with records in a table they fall into two broad categories:

• Destructive commands like Delete and Replace which default to the current record only.
• Benign commands like Locate and Sum which default to the entire table.

This makes sense. If you make a mistake with a destructive command such as Delete then it's reassuring to know that the damage has been limited to the current record and it won't be too much work to put it right.

Scope

There are 6 keywords that control the scope of a command:

• All
• For <logical expression>
• Next <numeric expression>
• Record <numeric expression>
• Rest
• While <logical expression>

These are described below but note that all of them will respect any filter so "all" in the next section means "all the records available to the current filter."

All

As has been mentioned above, this is the default scope for many of the non-destructive commands. It can however be applied to any of the others so for example:

will delete all the records in the table.

For <logical expression>

The For clause will apply the command to every record which meets the logical criterion. For example:

will delete all records which have "Bath" in the City field.

The criterion for equality is controlled by the state of the Set Exact flag.

Next <numeric expression>

You should only use this clause if you are certain that the records are in a particular order and if you are certain that you know where the record pointer is located. It restricts the command to the next n records so a command such as:

will send the contents of the next 50 records to the printer, "next 50" meaning the next 50 records according to the current index order. If no index order is in effect then the command will apply to the next 50 record numbers.

The Next clause can be very useful for this sort of interactive usage but if you see it being used in an application then you're usually looking at an old design with no relational features. It used to be used where you would always have a block of ten records for each order because these held details of the ten items that were permitted for each order. A better design would of course have the item details in a separate table so that there could be any number of lines per order.

Record <numeric expression>

This clause also goes right back to the first days of FoxBase and forces the command to operate on a given record number. These were the days of flat files and it seemed sensible to be able to refer to a record by its position in the table rather than by any unique property such as the Primary Key.

The syntax of this clause is:

This syntax is supported by Visual FoxPro 9 but is not to be recommended. Nobody reading this line will have any idea why record 3 is important.

Rest

Rest is another clause which relies on the position of the record pointer and on the order of the records but this one can be very useful when you are tuning an algorithm for speed. If you can arrange the records so that they are in a certain order then you might be able to design the algorithm so that you can just process all the records from a certain point in the table:

will copy all the records from the current position - including the current record - to an Excel spreadsheet. This can be much faster than working through the entire set of records, picking and choosing as you go.

If you don't need the current record then use the Skip command to move to the next record before processing the "rest".

While <logical expression>

This does not have the same meaning as While in an SQL select statement. A command under the control of While will execute against consecutive records as long as the criterion remains true. This is not quite the same as a For clause which will operate on every record in the table that matches the criterion. A While clause will stop as soon as it meets a record that fails to satisfy the criterion. If the first record doesn't meet the criterion then no records at all will be processed.

It's usual to use this clause in conjunction with a Seek command:

This code will put the table into City order and then move the record pointer to the first record for "Bath". Because the table is in City order we know that all the records for "Bath" will be in a block following the current record and the While clause makes sure that we will process that block of records and no more. If we had used a For here then the application would have continued retrieving and examining the rest of the records in the table.

Careful use of While can reduce the running time of an algorithm from order n ² to order n. If you are processing all the deliveries for all the customers then a simple design would be to scan the entire deliveries table once for each of the customers. By using While you can process the deliveries for the first customer and then carry on from that point to process the deliveries for all the following customers one at a time. This just takes one scan through the customer table and one through the deliveries table.

As with the for clause, the definition of equality is controlled by the state of the Set Exact flag.

Exact matches

The way that the For and While clauses match records for equality is controlled by the state of the Set Exact flag. This has a global value across the current data session and can take either of the two string values "ON" and "OFF"

If Exact is "OFF" then two strings will be considered equal if the first letters of the longer string match all the letters of the shorter string. The command:

will find "Bath", "Bathavon", "Batheaston", etc. If Exact is "ON" then it will only find the exact match of "Bath".

The default behaviour can be set by selecting Options from the Tools menu and picking the Data tag.

Use the following commands to alter the state of Set Exact whilst a program is running:

It is always a wise precaution to have one of these commands at the head of your program. This ensures that, regardless of any local settings, you can be certain that your application will be operating with exact or partial matches as appropriate. You will also need to repeat these commands whenever you start a form with a private data session. The value of Set Exact is restricted to the current data session.

Note that Set Exact only applies to string comparison using a single = operator. If the comparison uses the double == operator then the two strings will only be considered equal if they are the same length and any trailing spaces will be included in the comparison.

Set ANSI

Set Exact applies to the native FoxPro commands and not to records being selected by SQL commands. The screenshot above shows that Set ANSI has also been set to "ON". This has a similar effect on comparisons in SQL.