In the May issue, Bill Warner did an article on auto-incrementing.  As it happened, I have a client who needed a specialized auto-incrementing job number.  I had programmed it using a slightly different approach than Bill'S, so I threw my two cents worth in last month.  Bill was gracious enough to give me credit as co-author of his follow-up article in the the June issue.

Then we heard from Ira Perlow, who pointed out that we needed to tighten the code further yet.  So, here we go again!

First, I'd like to share the specific solution I used for my client, GREAT IMPRESSIONS PLUS, a printing broker in Silicon Valley. They use a descriptive job number in a seven-character field.  The first character is an alpha code designating the salesperson.  The next two characters designate the year and the last four characters are the job number for that year.  So in effect there are two parts of the field that need to increment (although one part only increments once a year).  Here's how I did it:

The Job Entry form (and Set) for GREAT IMPRESSIONS PLUS contains the seven-character Job Number (Job).  It also contains a two-character Salesman ID (Slsmn_Id) and an Order Date field (Ordr_Date).  These two are required fields.  The Slsmn_ID field has a cross file validation field rule to guarantee that we have a good Slsmn_ID when we do a fetch in the script.  I also have a validation field rule for the Ordr_Date field which forces the date to the current year.

If you read and understood the two previous articles, then you know that we set the default for the job number to PENDING which exactly fits our seven-character Job field.  Then we set the skip field rule to true (.T.).

Now we create the two auto-increment scripts with slight modifications. For our purposes, let's assume an Employee Master table called EMP_MST which has a two-character Slsmn_ID field, and a one-character Slsmn_Code.  This table is indexed on Slsmn_ID to Sales. We'll use the same 1 record Menu table that Bill used in the first two articles.  However, this time it contains a two-character Year field and a four-character Job field.  Four characters allow for 9,999 discrete jobs in a given year.  If the client does more than 10,000 jobs in a year, I'll rewrite the script, and buy stock in the company!!!

Notice that I've moved the incrementing calculations in the following scripts so that they are performed after the record is locked with the tbl3.Change_Begin(.t.) statement. This, as Ira pointed out to us, eliminates even the slim possibility that a second record could get a lock before the first "incremented" fields can be written!!

The first script:

dim tbl as P  ' The Current table

tbl = table.current

IF tbl.job <> "PENDING"  ' Get out if Job Number is already set
   END
END IF

dim dateyear  as C  ' Two character year variable
dim newjob    as C  ' The New Job Number
dim salescode as C  ' One character salesman code 
dim rec       as N    
dim tbl2      as P  ' Pointer to the Employee Master table
dim ndx2      as P  ' Pointer to the active index for EMP_MST
dim tbl3      as P  ' Pointer to the Menu table

'*** First, we get the salesman code from the EMP_MST table. 

tbl2  = Table.Open("EMP_MST")
ndx   = Tbl2.Index_Primary_Put("Sales")

rec = tbl2.Fetch_Find(tbl.Slsmn_ID ) 

IF rec > 0
   salescode = UT(tbl2.Slsmn_Code)
ELSE
   salescode = "X"  
      '*** This should never happen unless there is an error
      '*** in the EMP_MST table.  If the user sees a Job Number
      '*** starting with "X" she knows something is wrong   
END IF

tbl2.Close()

'*** Get the 2 digit year from the order date.  We know this is the
'*** current year because of the field rule which validates only 
'*** dates in the current year 

tbl3 = Table.Open("Menu")

dateyear = substr(CDate(tbl.Ordr_Date),3,2)

'*** Now we lock the record and set the rest of the Job Number 
'*** If a new year change the year field and reset Job Number to zeros

tbl3.Change_Begin(.t.)
   IF dateyear > tbl3.Year
      tbl3.Year = dateyear  
      tbl3.Job  = "0000"   
   ELSE
      tbl3.Job  = padl(LTrim(Str(Val(tbl3.Job)+1)),4,"0")
   END IF
tbl3.Change_End(.t.)

newjob = salescode + tbl3.Year + tbl3.Job

tbl3.Close()

'*** Write it to the Job table

tbl.Change_Begin(.t.)
   tbl.Job = newjob
tbl.Change_End(.t.)

parentform.Resynch() 

END

'*** Write it to the Job table
tbl.Job = newjob
parentform.Commit() 
parentform.Resynch() 
END