In Xbasic for Everyone, I illustrated a short script to
duplicate any record of any table. But what if you need to duplicate the
active records of an entire set that spans two or more tables?
A typical example would be an invoice set, in which there is an invoice
header and multiple detail lines. In some applications it is not unusual
for many customers to repeat the exact same order time after time. In this
short article, I'll illustrate how to duplicate the records in a set.
| Structure of Inv_hdr.dbf | ||
|---|---|---|
| Field | Type | Width |
| Inv_no | C | 6 |
| Date | D | 8 |
| Total | N | 8, 2 decimal |
The set, invoice.set, consists of 2 tables, inv_hdr.dbf and inv_items.dbf, linked on a one-to-many linkage through an inv_no field.
| Structure of Inv_items.dbf | ||
|---|---|---|
| Field | Type | Width |
| Inv_no | C | 6 |
| Desc | C | 20 |
| Amount | N | 7, 2 decimal |
And the link properties of invoice.set are:
As is typical in an invoice application, the inv_no field in the parent table, inv_hdr, is an autoincrement field, that is, Alpha Five will assign a consecutive unique value to each newly created invoice. You define the field as an autoincrement field in the Field Rules under Field Type:
For the purposes of this article, the inv_no field does not have
to be an autoincrement field. In fact, this article would be easier if it
were not an autoincrement field! But we'll do the autoincrement case here,
and if you have a different way of assigning invoice numbers, you can use
that in your application.
Our next step is to create the default form for invoice.set:
Now, add a new button to the form. We'll place a script on the button to
duplicate the invoice, so make the text label "Duplicate this invoice"
and make it a custom button. Right-click on the button, choose
Actions, and then switch to the Xbasic editor. Enter the following
code:
''XBasic
dim hdr as p
dim items as p
dim old_parent as b
dim count_items as n
dim i as n
| hdr=table.current(1) items=table.current(2) count_items = count(Inv_Items->Inv_No,GRP->INV_HDR) if count_items>0 then |
|||
| dim records[count_items] as b i=0 items.fetch_first() while i<count_items |
|||
| i=i+1 records[i]=items.record_data_get() items.fetch_next() |
|||
| end while | |||
| end if 'count_items>0 old_parent=hdr.record_data_get() hdr.enter_begin() |
|||
| ' note, in this table the inv_no is ' an autoincrement field ' a new inv_no is assigned by the field rule, ' which overrides ' the Xbasic record_data_set() |
|||
| hdr.record_data_set(old_parent) | |||
| hdr.enter_end(.t.) if count_items>0 then |
|||
| for i=1 to count_items |
|||
| items.enter_begin() items.record_data_set(records[i]) items.inv_no=hdr.inv_no |
||
| items.enter_end(.t.) | ||
| next | ||
| end if ' count_items>0 parent.resynch() end |
||
Let's discuss this script in some detail. First, note the use of the count()function in the script. Because the script uses the set structure of invoice.set, it is possible to use count() over a grouping level to get the total number of separate invoice items for the chosen invoice. I could also have coded a while..end while loop, but if Alpha's developers have already coded a function, why not use it?
After determining how many invoice items are present, the script next establishes an array, records[count_items], of blob variables. A blob, or binary large object, can be used to store entire records. The script then loads the different elements of the array with the individual line item records, using the record_data_get()method.
Next, the script loads the values in the old invoice header into another blob variable, old_parent. It then creates a new invoice header record and stuffs it with the data contained in old_parent. Note that since the inv_no field is an autoincrement field, the autoincrement field rule will override the old inv_no with a new, unique value.
Finally, the script enters new line item records. It stuffs each new line item with the values stored in the records[] array. Because the line items in the records[] array all contain the old inv_no, the script explicity sets the inv_no field of the new records to the value of inv_no in the new header record.
The very last line, parent.resynch(), simply synchronizes the
form to the newly created duplicate invoice.
6/11/98 - pkw