Numbering browse lines

by Dr. Peter Wayne

You may have used Alpha Five's autoincrement field to assign consecutive numbers to records, but in many applications you would like to have more control over the line numbers. In another article on this site I show how you can write your own incrementing field code to create consecutive numbering. But what if you want to make each invoice's item lines begin with "1" and increment consecutively? Neither Alpha Five's autoincrement rule nor the programmable increment presented will help you here.

In a typical one-to-many set, the parent record is represented by form-level fields and the child records are displayed on a browse. What we want to do is to program a form that looks like this:

Figure 1. Consecutive detail line numbering in an invoice set.

We have 3 requirements:

1. The first line item for each new invoice will begin at "01".
2. Subsequent line items will increment by 1.
3. Deletion of a line item will cause the remaining line items to be renumbered, so there is never a gap of line numbers.

We'll create 2 simple tables, header and line_items, for this illustration:

And the line_items table is equally simple:

The two tables are linked one-to-many on the header_number field. Call the set (what else?) invoice.set.

The programming for creating line numbers for new line items goes in the OnSaveRecord event of the line_items table, and the script to renumber line items after one is deleted goes in the OnDeleteRecord event of the same table. In fact, we will run the same script with each event:

''XBasic dim head as p
dim items as p
dim line_number as n

line_number=0
on error goto not_in_set
head=table.get("header")
on error goto 0

items=table.get("line_items")
items.fetch_first()
while .not. items.fetch_eof()

line_number=line_number+1
items.change_begin()

items.line_number=padl(ltrim(str(line_number,2,0)),2,"0")

items.change_end(.t.)
items.fetch_next()

end while
parentform.resynch()
end

not_in_set:
on error goto 0
end

OnSaveRecord and OnDeleteRecord script for line_items

Looking at this script, we can see that its logic is fairly straightforward.
First, we check to see if the line_items table is being opened in a set with header present in the set. That's very important. If the line_items table is not opened as a child to header, then there is no meaningful way to assign consecutive line numbers to the records in the same invoice. If the table is opened by itself, then the script passes program flow to the error trap at not_in_set and does not renumber the line item records.
Once we have verified that we are in the right set, then we simply fetch one at a time through the records in line_items. We can use fetch_first() and fetch_last() in a while..end while loop, since the set structure guarantees that active range of the line_items records is limited to the current header number.
After fetching each record in line_items, we have to update its line_number field. That is done through the change_begin() and change_end() methods.
Finally, after updating the records in the browse, we issue a parentform.resynch() to make sure the form reflects the changes made to the underlying table.

You can use this technique any time you need to number child records consecutively. Examples would include invoices for customers, clinic visits for patients, monthly deposits or withdrawals in an account - the number of such applications is unlimited.

Under rare circumstances, it is possible for a multiuser record-locking conflict to arise. If two users are simultaneously adding or deleting line items on a single invoice, then conceivably one of the OnSaveRecord or OnDeleteRecord scripts could fail to obtain the record lock it needs to initiate a change_begin(). This would be a rare circumstance indeed, but of course it is just such rare circumstances that are behind most spectacular software failures.
We can check for multiuser conflicts by placing another on error goto statement just before the change_begin(). As in all error traps, we have to decide in advance what we'll do if we encounter an error. In this case, we'll just delay one second and try again. If the record is still locked and we can't change it, we'll delay a second and then a third time. If we continue to fail after 3 tries, we'll abort the script with an informative message to the operator. Here is our script with multiuser error trapping added:

''XBasic
dim head as p
dim items as p
dim line_number as n
dim tries as n

tries=0
line_number=0
on error goto not_in_set
head=table.get("header")
on error goto 0

items=table.get("line_items")
items.fetch_first()
while .not. items.fetch_eof()

line_number=line_number+1
on error goto record_locked
items.change_begin()
on error goto 0

items.line_number=padl(ltrim(str(line_number,2,0)),2,"0")

items.change_end(.t.)
items.fetch_next()

end while
parentform.resynch()
end

not_in_set:
on error goto 0
end

record_locked:
on error goto 0
if tries<3 then

tries=tries+1
sleep(1)
resume 0

else

ui_msg_box("System error","Unable to lock and update line numbers!")
end

end if

Updated script for OnSaveRecord and OnDeleteRecord

6/23/98 - pkw

Send me your comments on this article
Return to home page