Many example programs in FoxPro Summary use EmpList.dbf as an example database. Click here to download Emplist.dbf
BOF and EOF functions (Beginning Of File / End Of File)
Brettell uses E.O.F. function in common, so this section will only mention about EOF() function. This function usually uses with Do-While statement. E.g.
Set default to “P:\FoxPro”
Use Emplist.dbf
Do While not EOF() While it’s not the end of file then (not at last record)
? Emplist.Fname Display First name in the database
Skip Move to the next record
Enddo
Use
Another main use will be to check that the record is end of file or not. E.g.
If EOF() then EOF() function will return value as True or False (Boolean)
? “End of File”
endIf
Open & Close the database
Use command to open/close the database
Use [filename] [Options]
E.g.
- Use “P:\Foxpro\Emplist.dbf” Open as normal
- Use “P:\Foxpro\Emplist.dbf” exclusive Open as exclusive
OR (Recommended Option to use with Set Default)
Set default to “P:\FoxPro”
Use Emplist.dbf
Reminder: You only need Set default to command once in your main module.
Use command to close the database
E.g. Use
It is important to close the database once you opened it by calling use command
Add the record into the database
Use Replace command
Replace [Table.Fieldname] with [value]
E.g.
FirstName = space(20)
Set default to “P:\Foxpro”
Use Emplist.dbf Open the database
Get FirstName Get user input
Read Read in
Append blank Add new blank record
Replace Emplist.Fname with FirstName Replace name in Fname field
Use Close the database
This means that you replace the Name [Variable that you set to get user’s input, with the field name on the table (Emplist.Fname – [Table’s name].[Fieldname] )
There are 3 main ways you can apply replace command into your project.
- Edit the record using Replace command
The program itself edit the data by replacing new data
- Edit the record through the Textbox
user edit the data through the Textbox contains existing data
- Edit the record manually
The user manually edit the data in the table
Using Replace command
Replace [Table fieldname]* with [value] (See above section for more info.)
***NOT FINISH***
Browse between records
Go command
E.g.
- Go Top Select Top record
- Go Bottom
- Go 5 Select fifth record
Skip command
E.g.
- Skip Select the next record
- Skip 4 Move to next 4 record from current one
- Skip –2 Move back 2 records from current record
Search the table
Using Locate command
Locate for [Field name] = [Search Value]
Locate for [statement]
E.g. Locate for TestTable.Fname = “Jutiphan”
This will search the database, but not going to display on the screen. To display:
Do While Found() If found,
? Fname Print the result
continue Continue searching
enddo
Please note: Don’t forget to use Text functions as well. This is not included in the example.
Extended: Example of searching & search validation
This is example of searching using Emplist.dbf database. Hope this is enough to give you the idea of applying Locate command to the project as this is the most frequently asked task!
FirstName = Space(15) This is to store the user’s input. (Name to search)
YPosition = 7 More info, See Do While statement: Common problem
Set Default to "P:\FoxPro"
Use Emplist.dbf Open the database to search
@ 5,5 say "Enter the First name to search:"
@ 5,35 get FirstName Get input from user on what to search
Read
Simple variation convert used. You may want to perform more validations.
Firstname = Alltrim(Proper(Firstname))
Clear
@ 5, 5 say "First name" style “B” Display the titles for 3 pieces of info. to display
@ 5,20 say "Last name" style “B”
@ 5,35 say "Address" style “B”
Use locate command to search
Locate for Emplist.Fname = Firstname
Do While Found() If found then display the info. In that record
@ YPosition, 5 say Emplist.Fname
@ YPosition,20 say Emplist.Lname
@ YPosition,35 say Emplist.Address
YPosition = YPosition 1
Continue Continue to search until end of record (EOF)
Enddo
Show info. After finish searching. (You should use IF statement to say differently if no record found)
@ Yposition 2,5 say str(Yposition - 7) " records found."
@ YPosition 3,5 say "Total no. of record in the database: " alltrim(str(Reccount()))
Close the database
Use
Click here to download the sourcecode for an above example
Sort the table
Using Sort command
Sort to [New table’s name] on [fieldname1] (, [fieldname2]) [ASCENDING/ DESCENDING] )
If you use sort command, it doesn't actually sort the table, but instead it creates a new table with sorted records. Below is the example using Emplist database
Set default to “P:\FoxPro”
Use emplist.dbf Open the table you wanted to sort
Sort to SortedEmplist.dbf on Emplist.Fname Sorted by Fname, in SortedEmplist database
Use Close the Emplist database
The above example will create a new table named SortedEmplist.dfb with Fname sorted and it will be in P:\FoxPro (where you set default to.)
Extended: Using Sort command
You can also use Sort command to sort in different fields, and in different order (Ascending or descending). E.g.
- Sort to SortedEmplist.dbf on emplist.fname , emplist.lname descending.
This will sort fname in descending order and follows by lname order
- Sort to SortedEmplist.dbf on emplist.fname/a, emplist.lname/d
This will sort fname in accending order and follows by lname in decending order (/a shorts from ascending, and /d short from descending) For more information on sorting, use Helpfile.
Using Indexes
You can also use Index to sort the table. This method is much better than using Sort command. Soon the information will be available. For now, Use Helpfile or ask Mr. Brettell directly.
Delete the record
Delete record [Record no.], Delete for [Statement] E.g.
- Delete all Delete the whole table records
- Delete Delete current selected record. (Best with Browse between records commands)
- Delete record 3 Delete record 3
- Delete for Fname = “Jack” Delete the record where Fname = Jack
Delete command doesnot actually delete the record, it just appears to be deleted by marking it as deleted. Using Pack command will permanently delete the marked records. Becareful: The record will be deleted permanently and the table has to be opened as exclusive. Here is how you can delete the data permanently in one go:
Set default to “P:\FoxPro”
Use Emplist.dbf
Delete for Fname = “Jack” Mark the record as deleted
Pack Permanently delete all records makred as deleted
Use Close the database
Above example is highly NOT recommended. Users sometimes make mistakes, You can just use delete command, then have a another menu option for “Organise the database” or something, then this will sort the table and permanently delete the record. If you don’t want that, You can ask the user to confirm that they want to delete it or not by using Messagebox() function (Advance) simply just display a confirmation message on screen.
Restore the record (Undo deletion)
Use Recall command. (Same structure as Delete command) This is possible only for records that aren't yet permanently deleted by Pack command. You can use the same statements as shown in Delete the record section. E.g. Recall All (Undo delete all records)
Display the records/fields by using commands
Using List command
- List Specific field: List fields [field’s name] E.g.
- List field Fname
- List fields Fname, Lname
- List Specfic record: List record [record no] E.g.
- List all records: List
Using Display command
This will only display the selected record.
Display [Field’s name] E.g.
Goto 3 Move to third record
Display Fname, Lname Display Fname and Lname of that record
There are some problems using List/Display command. You cannot specify where you want it on the screen and most of the time it looks messy. There is an alternative way of doing this as shown in Student Worksheet 2 or Disp3 program. This alternative way will be explained in more details in the section below.
Display the records/fields by programming
This is the best way of display records as you can specify how you want it to look by using SAY command and able to customize how you want it to look. Below are the examples of displaying all the records by listing them (Similar to Extended Search sample section)
Multiple records at a time (List view)
YPosition = 7
Set Default to "P:\FoxPro"
Use Emplist.dbf
Clear
Display the titles
@ 5, 5 say "First name" style “B”
@ 5,20 say "Last name" style “B”
@ 5,35 say "Address" style “B”
If the database is not end of file then continue
Do While .not. EOF()
@ Yposition, 1 say alltrim(Str(Yposition – 6)) “.”
@ YPosition, 5 say Emplist.Fname
@ YPosition,20 say Emplist.Lname
@ YPosition,35 say Emplist.Address
YPosition = YPosition 1
Skip Move to the next record
Optional: Code in bold is for support of large number of records to display
If YPosition = 20 then If 20 records are display then
Wait Wait for user to press key and view the next set of records
Clear
Since the screen is cleared, titles will need to be display again, set YPos to the top
@ 5, 5 say "First name" style “B”
@ 5,20 say "Last name" style “B”
@ 5,35 say "Address" style “B”
YPosition = 7
Endif
Enddo
Show info. Telling how many records are there in the database
@ YPosition 2,5 say "There are “ alltrim(str(Reccount())) “ records in the database"
Use
Click here to download the sourcecode for an above example
One record at a time (Column view)
*This sample contains some functions or features that didn’t explain in this summary as yet, but soon will be available….
Variable Settings
YPosition = 7
ExitAllow = .F.
Set Default to "P:\FoxPro"
Set cursor off
Use Emplist.dbf
Do while ExitAllow = .F. If user didn’t choose Exit choice, continue
Clear
Display information on records of the database
@ 3,10 say "Record " alltrim(str(recno())) " of " alltrim(str(Reccount()))
Display the titles
@ 5,10 say "First name:" style "B"
@ 6,10 say "Last name :" style "B"
@ 7,10 say "Address :" style "B"
Display the data from the database
@ 5,23 say Emplist.Fname
@ 6,23 say Emplist.Lname
@ 7,23 say Emplist.Address
If RecNo() <> RecCount() then If current record is not the last record then
@ 9,10 say "Press Right arrow to view next record"
endif
If RecNo() <> 1 then If current record is not the first record then
@ 10,10 say "Press Left arrow to view previous record"
endif
@ 12,10 say "Press ESC to exit"
On Escape ExitAllow = .T. If Escape (ESC) key is press, exit the loop
wait "" Wait for user to enter the choice
do case
case (LastKey() = 4) .and. (RecNo() <> RecCount())
Skip If user pressed ‘Right arrow’ key, and not the last record. Move to the next record
case (LastKey() = 19) .and. (.not. BOF())
Skip -1 If user pressed ‘Left arrow’ key and not the first record. Move to the previous record
case lastkey() <> 27 Show an error message when incorrect key pressed
messagebox ("The key you pressed is incorrect or current option is not available! Try again.", 48 ,"Incorrect Key pressed")
endcase
enddo
If user chose to exit (pressed ESC key), then do the following:
Clear
Use
set cursor on
Click here to download the sourcecode for an above example