SE Using Visual FoxPro

slug:SE Using Visual FoxPro 3.0 0075-1 VFP9.DOC

(a)9

(b)Using BROWSE to Access Data

BROWSE is one of the most ancient and powerful of all xBase commands. Over the years it has evolved from a simple command that invoked a spreadsheet-like view of a table to a single command that can be used to create fully functional applications. Despite all of this change and added complexity, BROWSE still provides someone using Visual FoxPro as a database manager with an effective, easy-to-use tool to access and manipulate data on a record-by-record basis.

In this chapter, you will learn how to:

***begin bulleted list***

[lb] Open and navigate a BROWSE window

[lb] Configure a BROWSE window

[lb] Add, modify and delete data from a BROWSE window

[lb] Use the optional BROWSE clauses from the Command Window

[lb] Browse related tables

***end bulleted list***

(c)Opening and Navigating a BROWSE window

Opening a BROWSE window can be as simple as issuing a single command or a selecting an item from the system menu, and in many settings, BROWSE may be the only tool you'll need on a daily basis to access and maintain your tables. The friendly spreadsheet-like interface provides familar navigational keystrokes while in the background, Visual FoxPro's underlying database technology provides superior speed and lookup capabilities. This speed and lookup however does have a price. Your data must be much more structured in a browse window than in a spreadsheet. Each column is a field in your table, and all of the cells in a given column must contain the same type of data. Additionally, each row in this spreadsheet-like view corresponds to a record in the table that you are browsing.

To get a good feel for browsing a table, let's browse one of the sample tables included with this book.

1. CD to the Using Visual FoxPro sample directory.

***insert OPEN toolbar icon ST02 in ICON10a.pcx to side***

2. Click the Open Icon or choose File | Open from the menu.

3. Pick CUSTOMER.DBF and chooose OK.

4. Select View | Browse "Customer"

***insert 09fig01.pcx***

Fig. 9.1

Browsing a table

As you can see in Fig. 9.1, the customer BROWSE window does indeed 21421e47v resemble a spreadsheet. If you prefer to see the whole record at once try choosing View | Edit to edit the records as shown in Fig 9.2.

***insert 09fig02.pcx***

Fig. 9.2

Browsing a table in edit mode

***begin FRI***

[lbr] "The View Menu Options," pg.xx (Ch 1)

***end FRI***

(d)Navigating with the Keyboard and the Mouse

Table 9.1 describes the navigational keys common to both the browse and the edit modes.

***begin table 9.1***

Key Description

Tab, Enter Move to the next cell

Shift+Tab Move to the previous cell

Up Arrow Move up one cell

Down Arrow Move down one cell

Left Arrow If the entire cell is selected, move to the previous cell, otherwise move left one character

Right Arrow If the entire cell is selected, move to the next cell, otherwise move right one character

Home Go to start of current cell or if at the start move to the previous cell

End Go to end of current cell or if at the end move to the next cell

Page Up Browse mode - If current cell is at the top of the window, go up one page, otherwise go to the top of the window

Edit mode - move up on record

Page Down Browse mode - If current cell is at the bottom of the window, go down one page, otherwise go to the bottom of the window

Edit mode - move down one record

Esc Abort changes to current cell and close the window

Ctrl+End Close the window and save all changes

***end table 9.1***

In addition to navigating with the keys, you can use the mouse and the scroll bars to point and shoot your way around. The verticle scroll bar can be moved up or down to display additional records. To select and activate a record, simply click the mouse cursor on one of the fields in the desired record.

(d)Navigating from the Table Menu

You can also choose Top, Bottom, Next, Previous, Record #, or Locate from the Table | Go to Record submenu to. Top and Bottom send you to their respective ends of the table based on the current table order. Next and Previous are also influenced by the active index. The Record # menu item brings up the dialog pictured in Fig 9.3. This value corresponds to value return by the RECNO() function for a given record and is not dependent on the current index.

***insert 09fig03.pcx***

Fig. 9.3

Jumping to a specific record with the Record # dialog

The Locate menu item is a little more complicated, but much more useful.

1. Select Table | Go to Record | Locate from the menu.

2. Choose the For button.

3. Double-click on state_prov in the field list.

4. Type "= [UT]" as shown in Fig 9.4

***insert 09fig04.pcx***

Fig. 9.4

Locating records with a FOR expression

5. Choose OK.

6. Choose Locate.

***insert 09fig05.pcx***

Fig. 9.5

Viewing the record after a locate

***begin tip***

Tip

For quick searches on small tables, you can also use the Edit | Find option just like you would in a text file.

***end tip***

(c)Configuring a BROWSE Window

A BROWSE window can be modified in several ways from the Visual FoxPro interface. These modifications allow you to resize column widths to fit more columns on the screen at once, reorganize the fields for easier data entry and partitioning the BROWSE window to access different parts of the table at once.

(d)Resizing Column Widths

In today's graphical user interfaces, fonts provide a great deal of flexilibity in the presentation of data. A BROWSE window allows you to set its font as well as the widths of each column in the window (this doesn't effect the field in the table.) Using these two ablities, you can fit as much or as little data on the screen at once as you like.

1. Choose Table | Font.

2. Type "Arial", hit tab twice, and type 6.

3. Choose OK.

Visual FoxPro shrinks all of the text in the browse window to the specified size.

***begin tip***

Tip

When invoked from a program or the Command window, the BROWSE command supports an optional FONT for setting the base font and size and an optional STYLE clause for setting the font style.

***end tip***

4. Position the mouse cursor between the last_name and first_name columns. The two-headed column sizing cursor should appear. Drag the column border to the left until the white space is mostly gone. You could also do this by choosing Table | Size Field and using the left and right arrow keys.

5. Repeat the procedure for all columns.

When you're done the browse window should look like Fig 9.6.

***insert 09fig06.pcx***

Fig. 9.6

Adjusting font and column widths to maximize usage of screen real estate

(d)Reorganizing BROWSE Columns

Many times, the order that the fields are defined in the table are not always the ideal order for BROWSE columns. The BROWSE columns can be organized independently of the table definition which means that you can reorganize your browse columns without fear of changing your table.

1. Position the mouse cursor on the first_name column header. The mouse cursor will turn into a down arrow.

2. Drag the column to one slot to the left, in front of last_name.

You can also choose Table | Move field and use the arrow keys to arrange the fields as seen in Fig 9.7.

***insert 09fig07.pcx***

Fig. 9.7

Rearranging the columns for smoother data entry

***begin note***

Note

In edit mode, you can drag the field names up and down instead of dragging the column headers left and right.

***end note***

(d)Partitioning the BROWSE Window

Occasionally, you may want to view two records at once or copy data from one record to another. Visual FoxPro can partition a browse window into two separate panes. By "unlinking" the partitions, you can scroll to different parts of the database, or you can use one side to browse and scroll the key fields and use the other side to view all of the detail in edit mode.

Partitions can be created by dragging the partition mark, pictured in Fig 9.8, in the lower left-hand corner of the browse window or by choosing Table | Resize Partitions and using the arrow keys.

***insert 09fig08.pcx with callout partition mark***

Fig. 9.8

Partitioning a browse window

(c)Adding, Modifying and Deleting Data

Adding and modifying data is what BROWSE is all about. BROWSE can be used to add records one at a time or add them in bulk. BROWSE is also a great place to view and modify existing records.

Choosing Table | Append New Record or pressing Ctrl+Y, adds a blank record to the bottom of the database and selects the first cell of the new record. Simply begin typing and press Tab or Enter after each field. To add records en masse, simply select View | Append Mode. If you are in browse mode, you will have to press the down arrow after completing a record. In edit mode, simply Tab or Enter past the last field to append another record.

To modify a record use the navigation keys, or the mouse and scroll bars to locate the field you wish to change. If the field is highlighted and you wish to replace the entire field, begin typing and you will overwrite the existing data. If you just wish to edit the existing data, use the mouse, or press Home or End to unselect the field and access the thin insertion point cursor. Finally, to modify a memo or a general field, double click on the memo or general field.

To delete a record just click the deletion mark box shown in Fig 9.9 or choose Table | Toggle Deletion Mark. A filled-in deletion mark indicates that the record will be purged during the next PACK or if Table | Remove Deleted Records is choosen. Repeat the toggle command to undelete a record.

***insert 09fig09.pcx with callout deletion mark***

Fig. 9.9

Toggling the deletion mark of a record

***begin caution***

Caution

Once you use PACK on a table, those record cannot be retrieved. Visual FoxPro literally rebuilds all of the files associated with the table and leaves behind the old records. There is no simple way to recover packed data.

***end caution***

(c) Using the BROWSE Clauses from the Command Window

Although the Visual FoxPro interface will invoke a BROWSE window from a variety of menus and managers, the full power of BROWSE can only be harnassed via the Command window or a program. The BROWSE command supports over thirty clauses which can used to control the appearance and behaviour of each and every aspect of a BROWSE window. In this section, we'll cover the essential clauses for everyday BROWSE use.

(d)Using a Field List

The FIELD clause of the BROWSE command allows you to choose only specific fields and to set various properties for each of the choosen field. The FIELD clause accepts a list of fields modified by field modifiers and separated by commas. Table 9.2 describes each field modifer.

***begin table 9.1***

Syntax Description

:numeric1 A : followed by a numeric expression in a field list specifies the column width based on the browse window's initial font.

:P=character1 character1 is a picture string and defines a field template exactly like a PICTURE clause in an @ SAY or @ GET or the format codes in the TRANSFORM() function.

:R This modifier makes the field read-only.

:H=character2 character2 is a character string that is used for the column heading

:W=logical The value of logical determines upon entering the whether or not the field can be edited like the WHEN event of a control object.

:B=lower, upper [:F] lower defines the lowest acceptable value for the field while upper defines the highest acceptable value. The optional :F forces this bounds checking regardless of whether the value had been changed or not.

:V=expression [:F] expression is usually an expression that is either true (.T.) or false (.F.) and that tells Visual FoxPro whether or not the field value is valid. If expression is 0, then Visual FoxPro will not permit the changes, but will not issue an error message either. :F as above forces validation.

:E=errormsg If the the current field value is invalid and the :E modifier is in use, then errormsg will replace the standard Visual FoxPro error message.

***end table 9.1***

***begin tip***

Tip

If you include a user-defined function as part of the :W=logical or :V=expression, then the user-defined function along with the :W and :V can be treated as events within your browse environment.

***end tip***

Additionally, a field list can contain a calculated field. Calculated fields appear in the list as calcname=expression and are also subject to the field modifiers. BROWFIEL.PRG demonstrates the use and syntax of a BROWSE command with a field list and field modifiers.

*BROWFIEL.PRG

USE customer IN (SELECT("customer"))

SELECT customer

BROWSE FIELDS;

name=ALLTRIM( last_name ) + ", " + ALLTRIM( first_name ) :20,;

address :30,;

city :17,;

state_prov :12 :H="State/Province" :P="@!",;

zip_postal :H="ZIP/Postal Code" :R,;

country :V=ALLTRIM( country ) $ "USA,Canada,Mexico" :F;

:E="This database is for NAFTA addresses only"

BROWFIEL.PRG first insures that CUSTOMER.DBF is open. It then invokes a browse window with a calculated last-comma-first name field, adjusts the widths of various columns, sets a few headings and insures that all the addresses in our database are from NAFTA member nations. Fig 9.10 shows the browse window rejecting a non-NAFTA address.

***insert 09fig10.pcx***

Fig. 9.10

Enforcing database rules with the :V modifier

***begin FRI***

[lbr] "Field Level Validation," pg.xx (Ch 4)

***end FRI***

(d)Using FOR and KEY to Browse Subsets of Tables

The FOR and KEY clauses allow you to filter the data that appears in the browse window. The FOR clause filters data using a logical expression such as "state_prov = [UT]". The KEY clause specifies a range of records using the current active index. BROWCRIT.PRG uses a FOR clause and then a KEY clause to perform the same filter operation.

*BROWCRIT.PRG

USE customer IN (SELECT("customer"))

SELECT customer

BROWSE;

TITLE "Customer - BROWSE FOR";

FOR state_prov = [UT]

SET ORDER TO state_prov

BROWSE;

TITLE "Customer - BROWSE KEY";

KEY [UT]

***begin tip***

Tip

The FOR clause uses Visual FoxPro's Rushmore technology which is hampered if you have an active index. If the active index coincides with the desired viewing criteria, the KEY clause is much quicker.

***end tip***

***begin FRI***

[lbr] "Selecting Groups of Records," pg.xx (Ch 6)

[lbr] "Selecting an Active Index at Runtime," pg.xx (Ch 3)

***end FRI***

(d)Using LAST

The LAST clause of a BROWSE command tells Visual FoxPro to look into the system resource file and look for a previous browse configuration associated with the current table. If one is found, then the browse window will "remember" its previous state including such settings as font, column widths, memo and general windows and column order. To summon the previous browse configuration:

BROWSE LAST

(d)Using PREFERENCE

The PREFERENCE clause is identical to the LAST clause in its ability to recall previous resizings, rearrangments, etc with one major difference. PREFERENCE allows you to give a name to a particular configuration. Thus you can have many preferences for a given table as opposed to only one last setting. To name a browse configuration:

BROWSE PREFERENCE pref_name

***begin troubleshooting***

Troubleshooting

I issue BROWSE LAST or give my BROWSE a name, and Visual FoxPro doesn't remember my settings.

Since the LAST and PREFERENCE commands use the resource file to store information, you must have the same resource file active when you got recall that information. SET RESOURCE ON activates FOXUSER.DBF, the default resource file. If you have any question about which resource file is active, use SYS(2005). Also, be sure not to confuse the NAME clause with the PREFERENCE clause. The NAME clause defines the name of an object variable and treats a browse window as if it were grid control. The PREFERENCE clause specifies an indentifier in your resource file associated with a given record.

***end troubleshooting***

***begin note***

Note

BROWSE LAST and BROWSE PREFERENCE do not interact. If, for instance, you open a browse window and specify a preference, and then reopen the browse with BROWSE LAST, it does not use the changes from the PREFERENCE session. The LAST clause is really a PREFERENCE that uses the table name as the resource file indentifier.

***end note***

(d)Using WHEN, VALID and ERROR

The WHEN and VALID clauses of a BROWSE command operate at the record level much as the :W and :V act at the field level. Everytime you shift the focus from one record to another, the when expression is evaluated. If changes have been made to a record, the valid expression is evaluated, too. Also like the :V field modifier, the VALID clause accepts the :F force evaluation directive. If you include :F in a VALID clause the valid expression is tested regardless of whether changes where made or not.

The WHEN and VALID clauses behave exactly as their field-level counterparts do. If the associated expressions return true (.T.), then all is well. If a when expression evaluates to false (.F.), then the current record becomes read-only. If a valid expression is false (.F.), then an error message, which can be set using the ERROR clause, is generated. The expression associated with a VALID clause can also return a zero. In this case, the error message is supressed, and the burden is placed on the programmer to notify the user of the error. Either a false (.F.) or a 0 will prevent you from changing the currently selected record until the VALID expression returns true.

The syntax for a BROWSE command with WHEN and VALID clauses is

BROWSE WHEN expression1 VALID expression2 :F ERROR error_string

***begin FRI***

[lbr] "Record Level Validation," pg.xx (Ch 4)

***end FRI***

(c)Browsing Related Tables

In keeping with the rest of Visual FoxPro, the BROWSE command fully supports the relational paradigm. Using SET RELATION TO and SET SKIP TO, BROWSE can help you easily lookup and navigate in the parent table data while updating and editing the child table. SET RELATION TO links the record pointer movement in two tables based on the parent table. SET SKIP TO enhances SET RELATION TO by allowing the child table to drive.

***begin note***

Note

The relations created with SET RELATION TO are not exactly the same as the relations in a Database Container. Like classes, the relations in the database container are concepts or blueprints. Like objects, SET RELATION TO relations are actually instances. The SET RELATION TO command synchronizes the record pointer movement between tables. The Database Container relations are passive and insure the integrity of the data.

***end note***

(d)Browsing One-to-One Relations

To browse fields from tables in a one-to-one relation, simply establish the relation with SET RELATION TO or the View window, issue a BROWSE command with a field list and fields in the child database using the table alias-dot notation as shown in program ONE2ONE.PRG. The relation and the BROWSE window "weld" the two tables together so that they appear as one table as in Fig 9.11.

*ONE2ONE.PRG

m.resource = SYS(2005)

SET RESOURCE TO (LOCFILE("vfp09.dbf","dbf","Where is VFP09.DBF"))

USE customer IN (SELECT("customer"))

USE orders IN (SELECT("orders")) ORDER id

SELECT customer

SET RELATION TO id INTO orders

BROWSE;

FIELDS name = ALLTRIM( last_name ) + ", " + ALLTRIM( first_name),;

orders.product, orders.inv_time,;

orders.unit_price, orders.unit_qty,;

total_price = orders.unit_price * orders.unit_qty;

PREFERENCE one2one

SET RESOURCE TO (m.resource)

***insert 09fig11.pcx ***

Fig. 9.11

Browsing a one-to-one relation

(d)Browsing One-to-Many Relations

One-to-one relations are fine, but more often, tables have a one-to-many relation such as a tables with many orders for one customer or many parts that can be purchased from one supplier. The BROWSE command natively supports one-to-many functionality in two different ways.

(e)Browsing Related Tables in Separate Windows

Once you have established a relation between two tables using SET RELATION or its equivalent, BROWSE automatically supports the relation if you open up a browse window for each table. After a chance to refresh, the child browse window will only display those records that are related to the currently selected record in the parent table. DO SEPARATE.PRG to see related browse windows in action.

*SEPARATE.PRG

m.resource = SYS(2005)

SET RESOURCE TO (LOCFILE("vfp09.dbf","dbf","Where is VFP09.DBF"))

USE customer IN (SELECT("customer"))

USE orders IN (SELECT("orders")) ORDER id

SELECT customer

BROWSE TITLE "Customer before relation";

PREFERENCE custseper SAVE

SELECT orders

BROWSE TITLE "Orders before relation";

PREFERENCE ordseper SAVE

SELECT customer

SET RELATION TO id INTO orders

SELECT orders

BROWSE TITLE "Orders after relation";

PREFERENCE ordseper NOWAIT SAVE

SELECT customer

BROWSE TITLE "Customer after relation";

PREFERENCE custseper NOWAIT SAVE

SET RESOURCE TO (m.resource)

In Fig 9.12 you can see how the two appear before the relation is set, and in Fig 9.13 you can see the the child browse respecting the newly established relation.

***insert 09fig12.pcx ***

Fig. 9.12

Browsing two tables before SET RELATION

***insert 09fig13.pcx ***

Fig. 9.13

Browsing one-to-many relations after SET RELATION

***begin note***

Note

The relations you create with SET RELATION TO are not the same as the relations in the Database Container. Like classes, the relations in the Database Container are the blueprints, a conceptual model. The SET RELATION TO relations, and SQL Select joins are the actual instances of these relations.

***end note***

(e)Browsing Related Tables with SET SKIP

The BROWSE command's support of SET SKIP TO allows you to view one-to-many related table in a single browse window. SET SKIP TO accomplishes that by moving the record pointer in the child database and by moving the record pointer in the parent database only when someone else's child record is pointed to. Effectively, it "emulates" a one-to-one relation and the BROWSE command handles to two situations very similarly. SETSKIP.PRG browses the same one-to-many relation that we saw in SEPARATE.PRG and ONE2ONE.PRG.

*SETSKIP.PRG

m.resource = SYS(2005)

SET RESOURCE TO (LOCFILE("vfp09.dbf","dbf","Where is VFP09.DBF"))

USE customer IN (SELECT("customer"))

USE orders IN (SELECT("orders")) ORDER id

SELECT customer

SET RELATION TO id INTO orders

SET SKIP TO orders

BROWSE TITLE "Customer/Orders with SET SKIP";

FIELDS name = ALLTRIM( last_name ) + ", " + ALLTRIM( first_name),;

orders.product, orders.inv_time,;

orders.unit_price, orders.unit_qty,;

total_price = orders.unit_price * orders.unit_qty;

PREFERENCE one2one NOWAIT SAVE

SET RESOURCE TO (m.resource)

***insert 09fig14.pcx ***

Fig. 9.14

Browsing one-to-many relations after SET RELATION and SET SKIP

(c)From Here[el]

To learn more refer to these chapters:

[lb] Chapter 6, "Selecting, Viewing and Ordering Data," helps you understand the basics of working with tables.

[lb] Chapter 7, "Creating Basic Queries," explores using Visual FoxPro's SELECT statement to analyze and filter your data

[lb] Chapter 14, "Advanced Form Design Controls," has a large section on the grid control, the object version of a browse window.