Program your own Autoincrement field

by Dr. Peter Wayne

This article teaches you how program your own incrementing field. You also learn how to use the form's Timer property and OnTimer event, and are exposed to real-life multiuser programming consideraions.

Let's review basics for a minute. If you define an autoincrement field for a table, then Alpha Five assigns a new, consecutive numeric or character value to each new record added to the table. For most applications, Alpha Five's built-in autoincrement field type is the ideal field type to use to create unique identifiers for each record. Customer numbers, invoice numbers, job numbers, ticket numbers - all are successfully represented by autoincrement fields.

There are occasions, however, in which an autoincrement field just won't do. There are 3 circumstances in which you might want to change the built-in autoincrement field for one of your own. These are:

  1. You may have several different series to enter. For example, you may be selling tickets to an event. For each event, you might want to begin at a new series number and continue from there.
  2. You may have two or more physically separate offices in which data entry is taking place, but you have to periodically append records from the subsidiary location to the main database. You might want to assign a different range of customer numbers to each office, to avoid conflicts.
  3. You may not like the way Alpha Five assigns autoincrementing numbers. When a new record is created in a table with an autoincrement field, Alpha Five assigns a temporary autoincrement value. The temporary value is made permanent if the record is saved, otherwise, the temporary value is abandoned. If two or more users are working on the autoincrement table, then more than one user may be assigned the same temporary value. In a single-table application this does not cause any problems, but if the autoincrement field is used as the linking field in a set, then linked records from one workstation can momentarily appear on another workstation. These "temporary links" will disappear when the users at each workstation save their work, but the experience of seeing another user's invoice line items magically appear on your screen under a new invoice that you are creating is unnerving and leads users to question the integrity of your whole application. You can avoid this unsettling experience by coding your own rules for an autoincrementing field.

Alpha Five stores the autoincrement field information in the table's .dbf file and data dictionary. We're going to create a different kind of field - let's call it simply an incrementing field. For a simple example, let's take a ticket agency that sells consecutive series of tickets to a number of events.
As we develop this one-table application, we'll also learn a little bit about multiuser programming, error trapping and using the form's OnTimer event.
For this article, we'll begin with a simple table, ticket_sales.dbf:

Structure of ticket_sales.dbf
Field Name Type Width
Date Date 8
Agent Character 10
Amount Numeric 8.2
Sell_to Character 10
Ticket_Number Numeric 6.0

We'll make the Ticket_Number field into an incrementing field. We need a table to store the last value of ticket_number. We'll call this table ticket_counter.dbf. It has one field, ticket_number, defined as Numeric, width 6, 0 decimal places.
Every time we create a new entry in ticket_sales, we'll obtain the last ticket number from ticket_counter and use that number as the default value for the ticket_number field in ticket_sales. There will only be one record in the ticket_counter table.
Where should we place the script that does this? The script should be attached to the OnEnterRecord event for the ticket_sales table. Here is the first rendition of the script:

Script 1. OnEnterRecord event script for ticket_sales.dbf
dim tbl as p
dim counter as p
dim ticket_num as n
dim ix as p
tbl=table.get("ticket sales")
counter=table.open("ticket_counter",file_rw_exclusive)
ix=counter.index_primary_put()
if ix.records_get()=0 then
counter.enter_begin()
counter.ticket_number=1
counter.enter_end(.t.)
else
counter.change_begin()
counter.ticket_number=counter.ticket_number+1
counter.change_end(.t.)
end if
ticket_num=counter.ticket_number
counter.close()
tbl.ticket_number=ticket_num
end

You'll notice that this program first checks to see if the ticket_counter table is empty. If it is indeed empty, the script creates the sole record and assigns a ticket_number of 1 to the ticket series. If the table is not empty, then the script reads the value of the last ticket number, increments it by one, writes it back to the counter table, and then uses that value in the current ticket_sales record.

Special considerations for multiuser programming

Alpha Five's programmers have taken care to check for conflicts whenever two interactive users want to change the same record: Alpha Five gives precedence to the first user and locks the second one out until the first one has finished. We have to exercise the same care when we write an Xbasic script that changes a record. That's why we opened the ticket_counter table in file_rw_exclusive mode. We have to be sure that only one user at a time can make changes to the counter record. Once we finish updating that counter record, we immediately close the ticket_counter table so that another user can access it.

But what happens if one user tries to access the ticket_counter table during the instant that another user has locked it? Ooops - the script will fail. Script 1 requires a run-time error trap at the line,
counter=table.open("ticket_counter.dbf",file_rw_exclusive)
to recover from a failure to obtain exclusive access to ticket_counter. What we will do is conceptually very simple: if we can't obtain exclusive access to ticket_counter, we'll wait a second and try again. We'll make up to 3 tries. If we can't get access by the 3rd try, ask the user if she wants to keep trying or if she wants to quit, since there may be some system error preventing her from getting access to the table. Here's the script:

Script 2. OnEnterRecord event script with run-time error trapping
dim tbl as p
dim counter as p
dim ticket_num as n
dim ix as p
dim tries as n
tries=0
tbl=table.get("ticket sales")
on error goto no_open
counter=table.open("ticket_counter",file_rw_exclusive)
on error goto 0
ix=counter.index_primary_put()
if ix.records_get()=0 then
counter.enter_begin()
counter.ticket_number=1
counter.enter_end(.t.)
else
counter.change_begin()
counter.ticket_number=counter.ticket_number+1
counter.change_end(.t.)
end if
ticket_num=counter.ticket_number
counter.close()
tbl.ticket_number=ticket_num
end

no_open:
on error goto 0
if tries<3 then
sleep(1)
tries=tries+1
resume 0
else
reply=ui_msg_box("Unable to update counter","Keep trying?",UI_YES_NO)
if reply=UI_YES_SELECTED then
tries=0
resume 0
else
end
end if
end if
Create a form for ticket entries

Now, let's create a form for ticket entries. On the form, we'll display the next available ticket number as a calculated field on the form. The calculated field's expression is
LOOKUP("ticket_counter", "recno()=1","ticket_number")+1
The lookup()function is new to Alpha Five version 3. It can take the place of the lookupc(), lookupn(), and lookupd()functions in previous Alpha products. The expression for this calculated field gets the value of the ticket_number field in ticket_counter's first (and only) record, by applying the filter recno()=1.

We'll also place a button on the form to allow the user to reset the value for the next available ticket number. Don't worry about the script for this button yet - we'll get to it later. The form should look like this:

Ticket entry form

Create a script to allow the user to change the next ticket number

Now we have to program the button to allow the user to reset the ticket_number field in ticket_counter. The code to do that is fairly straightforward:

Script 3. Reset ticket_number in ticket_counter.
dim counter as p
dim next_num as n
dim cnext as c
dim tries as n

next_num=lookup("ticket_counter.dbf","recno()=1","ticket_number")+1 cnext=alltrim(str(next_num,6,0))
cnext=ui_get_number("Reset ticket number","Enter new number",cnext)
if val(cnext)<1 then
end
else
tries=0
on error goto table_in_use counter=table.open("ticket_counter.dbf",file_rw_exclusive)
on error goto 0
counter.change_begin()
counter.ticket_number=val(cnext)-1
counter.change_end(.t.)
counter.close()
parent.resynch()
end if
end

table_in_use:
on error goto 0
tries=tries+1
sleep(1)
if tries<3 then
resume 0
else
ui_msg_box("Unable to lock ticket counter table","Try later, please!")
end
end if

The parent.resynch() in this button script makes sure that the form displays the newly updated Next available ticket number field on the form. Be aware that in a multiuser environment, the calculated field shown as Next available ticket number may be inaccurate. It will not be updated automatically as new tickets are assigned by other users. If this is a problem for you, don't despair - I'll show you how to fix it.

Use the form's OnTimer event to keep the calculated field current

Each form has a Timer property, which is nothing more than an internal clock that is set to go off at whatever interval you, the programmer, define. We'll set the Timer property of this form to 3 seconds:Setting the form's Timer property

And now let's give the calculated field that displays the next ticket number a meaningful name, like Next_in_series:

Assign a name to the calculated field

Finally, we're ready. We will write a one-line script for the OnTimer event of the form that updates the Next_in_series field. The script is simplicity itself:OnTimer script

The single line, Next_In_Series.refresh(), will force the calculated field to recalculate itself.
You may not realize that you can run Alpha Five version 3 in multiuser mode even if you only have 1 computer. Simply open two or more copies of Alpha Five on your Windows desktop. You can experiment with entering new tickets and resetting the ticket numbers and see how the OnTimer event keeps updating each separate Alpha Five session.

6/20/98 - pkw

Don't forget, we need your feedback to make this site better!

Return to home