Right click to download the Auto_incr_c() function as a text file.
A number of articles have been presented in the Alpha Newsletter regarding improved methods for auto-incrementing of field values in network situations. The purpose of this special auto-increment script is to avoid the possibility of two users getting the same increment value even if it is only a temporary issue prior to saving the record because it can cause the appearance of 'ghost' child records for the second user when the first user saves the parent record and begins adding child records. 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.
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.
One possible drawback to using a blank default value can occur when using a set that uses this field as a linking value. There might be (shouldn't be but might be) some child records with a blank value in the linking field. If this happens, those child records might show up prior to saving the new parent record. Of course, it's also possible that any default value might also inadvertently end up in a child record but that should be less likely than a blank value. This can be a good thing if the users understand what's happening in this situation because it warns them of possible data entry problems. Unfortunately, not all users are sophisticated enough to understand this situation.
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 inadvertantly 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 fuctions 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: (remember, both functions are stored as one)
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 = incr_fld_value( 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() = dflt_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 autoincrement 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.