MORE INFORMATION
In FoxPro, the developer has control over the treatment of key values in
records. The developer can decide at the design stage how keys will be
generated and handled in the application.
Considerations such as the following should enter into consideration:
- Methods of maintaining referential integrity.
- Whether or not to recycle deleted keys.
- Implications of collision-handling when appending records in a
multi-user environment.
All these factors are affected by the method you choose.
Creating a Unique Key
In most cases, you can create a unique key from the data itself by using a
Social Security number or a combination of a phone number and name. If this
type of data does not exist in the record, a combination of system
functions may be used to create a unique key, such as SYS(1), which
produces the Julian day number concatenated with SYS(2), which returns the
number of seconds elapsed since midnight. Obviously, this method will work
only if it can be guaranteed that no more than one record will be created
per second, a consideration that may not be valid in a multiuser
application. For more information about this, please see the following
article in the Microsoft Knowledge Base:
106708 How to Generate Unique Key Field Values
Using a Counter Field as the Unique Key
In many cases, a simple counter field, incremented when a new record is
added, is adequate to ensure a unique key for each record in the database.
Adding Counter Field to Screen Previously Generated by Screen Wizard
The example given in this article is intended for a single-user system
only. As the next available key is stored in memory prior to actually
creating the record, the original last key is not changed. In a multiuser
environment any number of sessions could receive the same last key, causing
records which should be unique to share the same key.
You'd need to create a different scheme for multiuser applications. For
example, a function could handle requests for obtaining the next key and
deny any other requests until the new record is written. The application
would then need to increment the key immediately (before the record is
written), which means it would not be visible on the screen during data
entry.
In situations where deleted records are purged to archive files, the 'last
record' method would not be appropriate. If the last record is deleted, the
next record added is given the same key as the deleted one. To ensure that
current records will not have duplicate archived keys, use a separate table
to hold the highest key value.
In a multiuser environment, you could lock the key table and the data
table, update both tables using the next value, add the new record to the
data table, and then unlock the key table for use by the next new record.
As previously mentioned, adding records should be handled by a single
procedure to avoid collisions. The procedure should check a flag field to
see if it is set, which would indicate that the procedure is currently in
use. If the procedure is not in use, set the flag and execute the
procedure. If the procedure is called from another session while the flag
is set, the application should ignore the request and try again later. Once
the procedure has added the record and updated the key, it should reset the
flag to allow more records to be added.
Step-by-Step Example for Single-User Applications
In this example, the key is of character type, and no records are in the
database that is associated with the screen.
The modifications made in this example can be applied to existing screens,
and will still work if there are already records in the database. However,
if a key field does not yet exist, add one by following this procedure:
- In the Command window, enter:
MODIFY STRUCTURE
- Add a field with an appriate name (such as KEY) to the field list,
allowing as many characters as necessary to hold the maximum records
anticipated. For example, four characters would allow up to 9,999 keys.
- Choose OK, and make the changes permanent.
- In the Command window, enter the following to assign key values:
REPLACE ALL key WITH RECNO() && If the KEY field is numeric
-or-
REPLACE ALL key WITH TRANSFORM(RECNO(),'L@ 9999') && If KEY is character
The following steps illustrate an automatically incrementing counter field
where each record added to an invoice database must have as its invoice
number the value of the previous invoice plus one. Because this is for
illustration only, the instructions accept defaults for screen generation
and assumes that the sample database to be the only one open.
To follow this example exactly as written, copy the INVOICES.DBF file from
the Tutorial directory to the FoxPro home directory. Once copied, Choose
File, Open... and select the new INVOICES.DBF in the FoxPro home directory.
From the Command window, issue the command:
ZAP
Then choose Yes to remove all the records from INVOICES.DBF.
NOTE: The comments contained in the code below are optional, and as such
may be omitted for brevity.
- Run Screen Wizard to create a new screen (Run, Wizards, Screen).
- Choose Finish, Modify with Design tool, then Finish again.
- Double-click the 'ino' field.
- Change the Field radio button from Input (Get) to Output (Say).
- Change the format to 9999 if using FoxPro for Windows or FoxPro for
Macintosh, and select the Leading Zeros check box under Editing Options
when the Numeric radio button is selected.
- Select the Refresh Output Field check box. Then choose OK.
- Press CTRL+S (or CMD+N in FoxPro for Macintosh), or click Open All
Snippets from the Screen menu pad
- While in the Setup window, press CTRL+F or choose Find from the Edit
menu.
- Type the following in the Look for... field, and then choose the Find
button:
APPEND BLANK
- While the line is highlighted, overwrite it with the following:
DO btn_val WITH 'ADD' && added for counter field
- Press CTRL+END to go to the end of the code, or scroll down.
- In a new line below SCATTER MEMVAR MEMO, add this code:
* section added for counter field
IF RECCOUNT()=0 && no records in dbf
m.ino=1 && initial invoice number
ENDIF
* end of added section
- Close or minimize the Setup window.
- To clean up, press Ctrl+F, and look for the following keyed in
exactly as:
equal_sign-spacebar-apostrophe-ADD-apostrophe
= 'ADD'
- Insert the following code on below the 'ADD' line:
* section added code for counter field
IF RECCOUNT()<>0 && file has records, so process
ord=ORDER() && store current index order (if any)
SET ORDER TO && use file in natural order
SET DELETED OFF && find last record even if deleted
GO BOTTOM && go to the last record
nxt=ino+1 && next number stored to variable nxt
SET DELETED ON && reset hiding deleted records
SET ORDER TO (ord) && restore database order(if any)
SCATTER MEMVAR MEMO BLANK && << original existing line
m.ino=nxt && store the new number
ENDIF
* end of added section
- Close and save the Cleanup code snippet.
- Choose Program, Generate, and then DO INVOICES.SPR.
- When prompted, choose Yes to add a new record. Note that the invoice
number is now 0001. Save the record. Continue to add and save records.
Then delete the last record. Add a new record. Note that the invoice
number is the next one in the series and that the deleted invoice is
removed from the series. Do not save the record; choose Cancel instead.
Add another record, and note that the same number is still available.