Q126404: How to Make Auto-Increment Keys in Wizard-Generated Screens
Article: Q126404
Product(s): Microsoft FoxPro
Version(s): 2.60a 3.00 | 2.60a | 2.60a
Operating System(s):
Keyword(s):
Last Modified: 19-AUG-1999
-------------------------------------------------------------------------------
The information in this article applies to:
- Microsoft Visual FoxPro for Windows, version 3.0
- Microsoft FoxPro for Windows, version 2.6a
- Microsoft FoxPro for MS-DOS, version 2.6a
- Microsoft FoxPro for Macintosh, version 2.6a
-------------------------------------------------------------------------------
SUMMARY
=======
While other database applications have a native field type (known as a Counter
type) that is defined to automatically increment a counter, the implementation
of such field behavior in FoxPro is left to the application developer.
This article shows by example one method you can use to programmatically
increment a key field when a new record is added. It gives you the changes
necessary to add a counter field to a screen previously generated by the Screen
Wizard.
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:
Q106708 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:
1. In the Command window, enter:
MODIFY STRUCTURE
2. 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.
3. Choose OK, and make the changes permanent.
4. 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.
1. Run Screen Wizard to create a new screen (Run, Wizards, Screen).
2. Choose Finish, Modify with Design tool, then Finish again.
3. Double-click the 'ino' field.
4. Change the Field radio button from Input (Get) to Output (Say).
5. 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.
6. Select the Refresh Output Field check box. Then choose OK.
7. Press CTRL+S (or CMD+N in FoxPro for Macintosh), or click Open All Snippets
from the Screen menu pad
8. While in the Setup window, press CTRL+F or choose Find from the Edit menu.
9. Type the following in the Look for... field, and then choose the Find
button:
APPEND BLANK
10. While the line is highlighted, overwrite it with the following:
DO btn_val WITH 'ADD' && added for counter field
11. Press CTRL+END to go to the end of the code, or scroll down.
12. 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
13. Close or minimize the Setup window.
14. To clean up, press Ctrl+F, and look for the following keyed in exactly as:
equal_sign-spacebar-apostrophe-ADD-apostrophe
= 'ADD'
15. 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
16. Close and save the Cleanup code snippet.
17. Choose Program, Generate, and then DO INVOICES.SPR.
18. 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.
Additional query words: VFoxWin FoxWin FoxDos FoxMac
======================================================================
Keywords :
Technology : kbHWMAC kbOSMAC kbVFPsearch kbAudDeveloper kbFoxproSearch kbZNotKeyword3 kbFoxPro260aMac kbFoxPro260aDOS kbFoxPro260a kbVFP300
Version : 2.60a 3.00 | 2.60a | 2.60a
=============================================================================
THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.
Copyright Microsoft Corporation 1986-2002.