Auto-Increment Script - One More Time
I've been following the auto-increment articles and recently had a need to
implement it. While reviewing the articles in preparation for using the method,
I noticed two areas that might be improved so we now have a third update to this
on-going development project.
To see the other articles on Auto-Incrementing, visit the Newsletter Archive.
by Cal Locklin
First, I want to thank Ira Perlow, Bill
Warner, and Ray DiFazio who submitted the previous articles for giving me
something so good to work from. They were also gracious enough to review and
comment on this article prior to publication. This collaboration of efforts is
creating something better than any one of us might have created on our
own.
The two areas I wanted to improve on are: (1) using virtually the
same script in two places made me think that a UDF (user defined function) might
be in order to simplify it's implementation, and (2) building on Ira's concern
about two users accessing it at the same time, I became concerned about what
would happen if the record were to be 'permanently' locked because of either a
computer crash that occurred just as the record was being accessed or because
someone else (typically the developer) was working on the table. The developer
issue should be minimal but the lock-up issue could cause major concern in some
cases. Some users might not associate the fact that the database has locked up
with the fact that one of the user's computers had crashed. Even if the computer
didn't crash, it's possible that an Alpha Five session on one of the computers
didn't shut down cleanly and has kept the table locked. This would not be
obvious to many of my users and could cause some major worries if the user
wasn't able to reach me to find out how to get the program functioning
again.
My solution involves a simple auto-incrementing field which does
not use a descriptive job number so, in that regard, it is simpler than the last
solution provided by Ray.
The new script actually contains two functions.
The first function is the main function that checks the field value in the
current table to determine if it is the default value and, therefore, needs to
be updated to the next auto-increment value.1 The
second function2 is a
relatively simple one that returns the next value from whatever table holds the
auto-increment value and updates it for the next user. It first tries to open
the table in exclusive mode and will re-try up to three times if exclusive
access was not obtained initially. If exclusive access has not been obtained
after the third try, the original field value is returned so the main function
can warn the user and either allow another retry or cancel the new record
without saving it.3
Because of the exclusive access requirement, the auto-increment value is in a completely
separate table which contains only two records and one field. (The second record
is empty but it is needed to avoid a lock-up condition that can occur when a
second user tries to obtain access to the table while someone else is editing
the one and only record.) Using a separate table for each auto-increment value
reduces the chance of having two people accessing the table at the same time
but, equally as important, it also standardizes the UDF because there is no need
to worry about which record or which field to access - it is always field #1 in
record #1.
By making this a function rather than a script, we can pass
the necessary values to the function when it is called and the function itself
is never changed no matter what table it is being used with. Only three
arguments are passed to the function: the name of the field in the current table
that will receive the incremented value, the name of the table that holds the
current increment value, and the default value for the original field. The
default value of the field is passed only so that it can be used within the main
function to determine whether or not exclusive access is not obtained. If
exclusive access is obtained when the second function is called, the increment
value will be returned. Otherwise, the original default value will be returned.
Regardless of which value is returned, the returned value is put into the field.
Then, if the main function sees the returned value is the same as the default
(i.e., the field value hasn't changed), it will show a message with
instructions.
Another idea suggested by Ira was to set a default for the
default value right in the function. Ira also pointed out that a blank value
could be a perfectly acceptable default value for the field - this is such a
simple concept that I, for one, completely missed it. So, it seems logical that
the function could be set up to assume that the default field value is blank.
Then, if no default value is passed to the function, the default field value is
assumed to be blank. In previous versions it was not possible to create defaults
for function arguments but version 5 makes it
very easy.
To use the function, just run it from the table's CanSave
Record event with the appropriate arguments. Assuming the field name is
Cust_idf, the increment table is named Incr_cust, and the default
field value is Pending, the function call would be:
Auto_incr_c( "incr_cust", "cust_idf", "pending" )
After finishing all this, I realized I had
inadvertently made another improvement. If you were really alert, you noticed
the earlier reference to the CanSave event rather than the OnSave
event. By doing this, it is not necessary to re-open the table in order to enter
the new auto-increment value. (CanSave occurs before the record is saved
and OnSave occurs afterward.) Also, it greatly simplifies the process
when you have an embedded browse on your form. If you have an embedded browse,
simply add parentform.commit() to the CanArrive event
of the browse. This triggers the CanSave Record event and runs the script. To
accomplish the same thing with action scripting, the CanArrive event of the form
would be defined by selecting Records / Save Record / Current Form or Browse
Window.
The two functions are actually quite short so don't be put off by
the number of lines of code. Without the warning message and comment lines, the
main function would only be 11 lines long but all the comments make it look much
more complex at first glance. And remember that both functions are intended to
be stored together under the function name Auto_incr_c. (I added the _c
just to distinguish it from my Auto_incr_n() function. I'll leave it to you to
figure out the difference.)
Footnotes:
1. You
could also test to see if the table is currently in the Enter mode before
running the function. I did it this way because at one point I had considered
allowing the record to be saved and then updated after the lock was released but
this idea was ultimately rejected. To test for the Enter mode, simply change the
first IF statement from IF fld.value_get() = dflt_val
to IF tbl.mode_get() = 2.
2. Defining multiple
functions in one script is perfectly acceptable but only the function that has
the same name as the script will be a global function. All other functions will
only be accessible to the other functions within the script. Creating a second
function in this case simply made the main function easier to read. (Ok, if you
really want the truth, the main function was originally a plain script and I
decided it was better not to combine it all into one function.)
3. I tried to
trap a Tbl.Change_begin() error to test for a locked record rather than
requiring exclusive access to the table but it took 30 seconds which seemed far
too
long.
Here's
the full function script:
If you have any problem cutting and pasting
from here, you can download it as a text file from the Programs section of my
website at www.aimsdc.net/Programs.htm
FUNCTION Auto_incr_c AS C ( incr_table as C, fld_name as C,
dflt_val="" as C )
'Get a pointer to the field
in THIS table that will get the incremented value.
tbl = table.current()
fld = tbl.field_get(fld_name)
Restart_here:
'Increment
the field value.
IF fld.value_get() =
dflt_val
'The
'incr_fld_value()' function returns the current Character value in "incr_table"
'and increments it
by 1. If it is not able to open the "incr_table" it returns the
'original
"dflt_val" so it can be checked
below.
newval = auto_incr_c( incr_table, dflt_val )
'Store the returned value
regardless.
fld.value_put(newval)
END
IF
'Not really necessary. Just allows the function to
return something meaningful.
Auto_incr_c = fld.value_get()
'If increment
didn't succeed, allow either trying again or cancelling.
'This allows for the
possibility that someone is working directly on
'the table or it has been
left 'permanently' open for some reason such
'as another computer crashing
at the wrong time.
IF fld.value_get() =
deflt_val
msg = "The
auto-increment number cannot be updated because the '"+incr_table+"' table "
msg = msg +
"is in use." +chr(13)+chr(13)+ "Someone else could be using the
table "
msg = msg + "or it could be locked due to a previous system or database crash
"
msg = msg + "somewhere on the network." +chr(13)+chr(13)
msg = msg + "If nobody else is using the file, it may be necessary for
everyone "
msg = msg + "to shut down and re-boot. In extreme cases, it may even be
necessary "
msg = msg + "to re-boot the server." +chr(13)+chr(13)+ "To
try again, click 'OK'. "
msg = msg +
"To quit and lose this record, click
'Cancel'."
resp = ui_msg_box( "*** ERROR - Record NOT
saved ***", msg, ui_stop_symbol+ui_ok_cancel
)
IF resp =
ui_ok_selected
GOTO
Restart_here
END
IF
'This seems to work even
*after* the message box. It didn't in version
4.
cancel()
END
END
IF
END FUNCTION
FUNCTION Incr_fld_value as C ( tablename as C, init_val as C
)
'Description:Returns current value in field 1 of
'tablename' and increments it by one.
'Used to implement xbasic auto-increment
in CanSave Record event.
DIM curval as C
'May not be req'd(??) but field type could be
anything.
'If exclusive access isn't obtained,
this returns the initial
'value so the field value won't be changed. Add'l
checks
'will be run in the main function above.
Incr_fld_value =
init_val
error_loops = 0
ON ERROR GOTO
Not_exclusive
tp = table.open(tablename,file_rw_exclusive)
ON ERROR GOTO 0
tp.fetch_goto(1)
fld = tp.field_get(1) 'Get a pointer to the first field in the record.
curval
= fld.value_get()
newval = increment_value(curval)
'This is an A5 built-in
function.
'Store the next value in the increment
table.
tp.change_begin()
fld.value_put(newval)
tp.change_end()
tp.close()
'Set the value to be
returned by this function.
Incr_fld_value = curval
'This is the normal ending point.
EXIT
FUNCTION
Not_exclusive:
'Give it 3 tries in
case someone else is entering
'a record at exactly the same
time.
IF error_loops <
3
error_loops = error_loops +
1
RESUME 0
END IF
'If
we get here, the function ends and returns the original
value.
END FUNCTION