Last month, I had an article about a script to auto-increment field values (instead of using the field rule).  I pointed out that one disadvantage was that you would lose a number if you cancelled the records; i.e., if the next number available was 1002, and you cancel the record, the next new record would be 1003, and 1002 would no longer be available. Ray DiFazio wrote to me about this disadvantage, and came up with a very elegant way to avoid it (I'm sure all you accountants out there will be happy).  Here's his idea:

Instead of assigning the next number when the record is first created, Ray put PENDING in the field to be incremented.  Then, when the field is saved, the auto-increment script assigns the number.  This way, if the record is cancelled before the save, the number will not be used, and therefore not lost.

To accomplish this, we have to do four things:
1. 

Put PENDING in the field rules as the default value for the field (make sure you check the first box, Set value at start of data entry).

2. 

Put .T. in the Skip field rule for this field, to prevent the user from changing the value.

3. 

Create an auto-increment script for the OnSave event of the form.1

4. 

If there is an embedded one-many child browse, create a second auto-increment script for the CanArrive event of the browse.


The first auto-increment script is:

dim global newjob as c

t = table.current()
pendjob = t.job

IF pendjob <> "PENDING"
   end 
END IF

tm = table.open("menu")
job = tm.job
newjob = padl(ltrim(str(val(job)+1)),5,"0")

tm.change_begin(.t.)
tm.job = newjob
tm.change_end(.t.)

t.change_begin()
t.job = newjob
t.change_end(.t.)

parentform.resynch()
In this script, job is the character field to be incremented, menu is the table where the job number is stored.  Note that the calculation for newjob uses the padl() function to put leading zeros in front of the number (in this case, it will put enough zeros to make the value 5 characters long).  This will assure that the numbers sort properly.2

The second script is needed for a one-many child browse.  This is because if you click in the browse directly from the form, the first script will not fire (even though the record is saved when you go from the form to the browse).  This script takes care of that: 
dim global newjob as c

t = table.current()
pendjob = t.job

IF pendjob <> "PENDING"
   end 
END IF

tm = table.open("menu")
job = tm.job
newjob = padl(ltrim(str(val(job)+1)),5,"0")

tm.change_begin(.t.)
tm.job = newjob
tm.change_end(.t.)

t.job = newjob
parentform.commit()

parentform.resynch()
This script is nearly identical, except that the change_begin and change_end from the first script are not there (since the table is currently in ENTER mode), and parentform.commit() is used to save the parent.
1 I usually create a global script, and then play the script from the event.  This makes it much easier to trouble-shoot the script, since I can switch to the script from the form without having to go into edit mode for the form.

2 See Using Numbers in Character Fields for more information.