As you probably know, Alpha Five has a field rule that will automatically increment a field.  However, there are times that the auto-increment rule might not provide the flexibility or security that you need.  In these cases, an auto-increment script could be the answer.

First of all, let's examine exactly what the auto-increment rule does.  If you start with a blank field, the next new record will show a one preceeded with zeros (the number of zeros depends on the size of the field.  If you start with a non-blank value, the field will increment as follows:

	123	124	125
	abc	abd	abe
	12z	13a	13b
	ab9	ac0	ac1

Please note that once the incrementing has cycled through the possible values, it starts over again:

	999	000	001
	zzz	aaa	aab

Now, you might wonder when this would not be sufficient.  One case would be when you want to increment a field differently for two groups.  For example, you might have sales customers and service customers, and you might want to assign incrementing job numbers differently for each.  This is a good application for scripting the auto-incrementing.

Another situation arises when using a network.  Although Alpha Five is supposed to assign a unique number when two people enter a new record at nearly the same time, this doesn't always happen.  Once again, an auto-increment script can reduce this problem.¹

So, what's the next step?  Well, before we create the script, we will need someplace to store values to be incremented.  As it turns out, a "dummy" table is the perfect place for this (see dummy tables in the Tips & Tricks section).  Briefly, this is a table that is used to create various menu forms, and also to store data not normally used in the application.  In our case, we will use this table (called MENU) to store the value for the incrementing field.

Next, we need a way to initiate the script, so the value in the field will increment as desired.  In the case of a form, we will use the OnEnterRecord event (Click here for details).  Also, we will have this event play a global script called INCREMENT.²

Now, for the script. The idea behind the script is to store a value in MENU, and when a new record is created in the data-entry form, the script will retrieve the current value from MENU, increment it, save the new value in MENU, and then use this value in the form.  The reason the script works so well to reduce the possibility of a duplicate value is that the time it takes to run this procedure is extremely short.³

(Please note that the script has one disadvantage:  If you start a new record, and then cancel it, the number that would have been assigned to this record will be lost.  In other words, you would go from 123 to 124, which was cancelled, to 125 for the next record.)

Here's a typical script for incrementing a character field containing numbers:⁴

dim global jobno as c
dim global newjobno as c

tbl=table.open("menu")
jobno = tbl.job_no
newjobno = ltrim(str(val(jobno)+1))

tbl.change_begin(.t.)
tbl.jobno=newjobno
tbl.change_end(.t.)

parentform:job_number=newjobno
parentform.resynch()

In this script, the fifth line (newjobno =..) is the calculation needed to increment the numeric value in the field.  Also, JOB_NO is the field in MENU that contains the value for incrementing.  JOB_NUMBER is the field on the data-entry form currently being used.

This is a pretty simple script for incrementing a numeric value. To increment a character value, the calculation is more complex (I'll just show the calculation -- the rest of the script remains the same):

ln=len(trim(jobno))

a = right(job,1)
b = substr(right(jobno,2),1,1)

s = CASE(a="h","i",a="n","o",a="H", "I",a="N", "O",.T.,a)
t = CASE(b="h","i",b="n","o",b="H", "I",b="N", "O",.T.,b)

IF a=="z"
   newjobno=left(jobno,ln-2)+chr(asc(t)+1)+"a"
   ELSEIF a=="Z"
   newjobno=left(jobno,ln-2)+chr(asc(t)+1)+"A"
   ELSE
   newjobno=left(jobno,ln-1)+chr(asc(s)+1)
END IF

In this script, s and t are defined so as to exclude the letters "I" and "O".  Please note that the script will work from aaaa to aazz.  If you want to have the second character from the right increment past z, more scripting would be required.

So far, these scripts have only done what the auto-increment field rule does, and you might wonder what the advantage is (aside from the duplicate issue mentioned earlier).  Well, this is only the beginning.  For example, MENU could have two fields, one each for Sales and Service  The script could be modified to include a prompt for the type of transaction, and the result from the prompt could determine which of these fields to increment.  This could give you Sales records that incremented SA125, SA126, etc, while the Service records incremented SV2047, SV2048, etc.

As you can see, the possibilities for different types of incrementing are only limited by your imagination (and a little Xbasic skill).  If you need more than the auto-incrementing in field rules, these scripts are a good place to start.

¹ Although it's almost impossible to absolutely guarantee that two identical values will not be created, many users have found that this type of script can minimize the duplicates substantially.

² While you can write the script in this event, rather than playing a global script, it is much easier to trouble-shoot the script if it is a global script.  You can switch between the form and the script quite easily, rather than having to close the form and open the field rules for that table.

³ For a more complete explanation of why this script helps to avoid duplicate values, please see Auto-increment script .

⁴ While you can increment a numeric field, there are some good reasons to use a character field with numbers in it, rather than using a numeric field.  Please see Numeric Fields for more information.