Category : Paradox DBMS
Archive   : TECH91.ZIP
Filename : TI662.ASC

PRODUCT : Paradox NUMBER : 662
VERSION : 2.0 & up
OS : DOS
DATE : December 9, 1991 PAGE : 1/4

TITLE : How to Use PAL to Generate Unique Values
(Auto-increment)




ÚÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ¿
³ ³
³ This Technical Information Sheet is intended only for users ³
³ who are familiar with PAL. It contains PAL code to demon- ³
³ strate a model solution to a specific problem and is not ³
³ intended to represent a complete programming solution. ³
³ Programmers who use this code must take responsibility for ³
³ debugging and developing their application. Assistance ³
³ in debugging and developing applications is considered ³
³ consulting and is beyond the scope of technical support. ³
³ ³
ÀÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÙ

In many cases, you will want to generate unique values for each new
record in a table. For example, each new customer might need a new ID
number. Here are three different methods for creating an auto-
increment capability.

The code samples here are generic examples only, and should be modified
to fit your specific application. Note: the following methods work
only in Edit and Coedit modes.

For a single user in interactive mode, the following method works.

SETKEY -23 [] = IMAGECMAX()+1

Whenever the user presses , the current field becomes equal to
one more than the current maximum in the current column. To use this
command, insert the above command into the script INIT using the menu
choices: Scripts | Editor | Write. Next type in "Init" and press
. Type in the above command exactly as it appears. INIT is
played every time you start Paradox, thus activating . The
keycode -23 corresponds to the key . For more keycodes, see
Appendix B of the PAL User's Guide. Also the cursor must be in the
field to incremented, which must be numeric.

The following is an example of a PAL program that increments a field.


















PRODUCT : Paradox NUMBER : 662
VERSION : 2.0 & up
OS : DOS
DATE : December 9, 1991 PAGE : 2/4

TITLE : How to use PAL to Generate Unique Values
(Auto-increment)




IF ISEMPTY("CUST") THEN
[CUST ID] = 1
ENDIF

WHILE TRUE
IF ISBLANK([CUST ID]) THEN
[Cust ID] = CMAX("Cust","Cust ID") + 1
ENDIF

WAIT RECORD
UNTIL "F2", "INS" ...
KEYPRESS RETVAL
SWITCH
CASE RETVAL = "F2":
.
.
.
ENDSWITCH
ENDWHILE

(For more information on the PAL WAIT RECORD command see Technical
Information Sheets 538 and 697)

Whenever the user moves to a record where the CUST ID is blank, the
application enters in a new ID number. The new ID number is derived
using the CMAX function which returns the highest number. The
application then increments this number by one.

The previous two methods work fine in a single-user environment,
however if there is a possibility the application might be used on a
network, you should use the following method. Note: the following
method works only in Coedit mode.

First, create a table with a single Numeric field. For example:

ID ÍËÍÍÍ ID ÍÍÍ»
º 10001 º

This table contains the next number to be used when the application
increments the field in the table you are entering data.

Whenever the user moves to a record where the CUST ID is blank, a new
record is created and filled in with a new ID number. The main data












PRODUCT : Paradox NUMBER : 662
VERSION : 2.0 & up
OS : DOS
DATE : December 9, 1991 PAGE : 3/4

TITLE : How to use PAL to Generate Unique Values
(Auto-increment)




entry routine calls a procedure that moves to the ID table, and
attempts to lock the record. If it fails the first time, it keeps
attempting to lock the record until it succeeds. During these
attempts, no one else can access the number there. This method
guarantees that each user obtains a unique number.

When the application succeeds in locking the number, it increments it
by one so the next time the application accesses a the table a new
number is available. Next, the application moves back to the CUST
table to continue the editing.

The ID table must be on the workspace in order to access it. If data
entry occurs in table view or with a single table form, you must place
the ID table on the workspace before editing your data table.

A multi-table form complicates the process of accessing the ID table.
(In a multi-table form it is not possible to toggle to table view
before posting the current record.) The simplest solution is to place
the ID table into your master form as an unlinked embedded form, with
the foreground color the same color as the background to hide it from
the users. With the ID table embedded on the master table, the
application can access the ID table while remaining in form view.

The following procedure is an example of how to access the ID table to
obtain the next ID number.

PROC GetCustID()
PRIVATE id
CurrTab = TABLE()
MOVETO "ID"
LOCKRECORD ;Give only one user access
WHILE NOT RETVAL ;Keep trying if first attempt
SLEEP 10 ;fails
LOCKRECORD
ENDWHILE
id = [ID]
[ID] = [ID] + 1 ;Increment ID
UNLOCKRECORD ;Allow other users access
MOVETO CurrTab
RETURN id
ENDPROC

The following PAL program is an example of the main data entry routine.












PRODUCT : Paradox NUMBER : 662
VERSION : 2.0 & up
OS : DOS
DATE : December 9, 1991 PAGE : 4/4

TITLE : How to use PAL to Generate Unique Values
(Auto-increment)




WHILE TRUE

IF ISBLANK([Cust ID]) THEN
[Cust ID] = GetCustID()
ENDIF

WAIT RECORD
UNTIL "F2", "INS" ...

SWITCH
CASE RETVAL = "F2" :
.
.
.
ENDSWITCH

ENDWHILE