Re: Subfile Processing

Kyle Goodwin
Extensicom LLC

Jonathan Mason wrote:

Hi Kyle

This is more a question about general programming than subfile design
and programming, but here goes.

A subfile is simply a means of storing and presenting related rows of
information on a display.  They are similar to database files in that
they store multiple records, but they differ in that the records are
stored in the jobs workspace rather than on disk.  They also differ in
that they are not keyed, but can be accessed using relative record
numbers (RRN) or sequentially.

Correct database design has the tables normalised, in other words broken
down into their common form.  For example, splitting an invoice into an
invoice header file (INVOICES) and invoice lines file (LINEITEMS). This

you have done with your tables.

The INVOICES file would normally have a key relating to the invoice
number (INVOICEID) and the LINEITEMS would have a composite key
consisting of the INVOICEID and the invoice line number (INVOICELINE).

When you load the records to your subfile you should use the INVOICEID
to position the file cursor to the first invoice line for the invoice
using the INVOICEID on a SETLL command and then loop through the file
using the INVOICEID as the key:

* Position to the start of the file... C Eval RRN = *Zeros C InvoiceID Setll(e) LineItems * Read through the file until all invoice lines loaded... C Dow Not %Eof C InvoiceID ReadE(e) LineItems * Populate the subfile record... C Eval SflFld1 = FileFld1 C Eval SflFld2 = FileFld2 C Eval SflFld3 = FileFld3 * Write the details to the subfile... C Eval RRN = RRN + 1 C Write Sfl C EndDo

There are a number of techniques for loading subfiles, the above loads
all records at once which you wouldn't normally use where large amounts
of data are expected - it's only shown here as an example.

The RRN is the position of the record in the subfile, so the first
record written is RRN 1, the second RRN 2, etc.  If you know how many
records are in the subfile you can read the whole subfile using a loop:

C                   For       I = 1 to RRN

C RRN Chain(e) LineItems C If %Found C ...

C                   EndIf
C                   EndFor

A good guide to subfile programming is "The AS/400 Programmers Handbook"
which has a large section on subfile programming.  Alternatively you
could check out the FAQ and the archives on this list.

All the best

Jonathan