How to Make Auto-Increment Keys in Wizard-Generated Screens

WINDOWS    | MS-DOS | MACINTOSH

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.

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:

   ARTICLE-ID: Q106708
   TITLE     : 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
   
   

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

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.