Category : Dbase (Clipper, FoxBase, etc) Languages Source Code
Archive   : TN8903.ZIP
Filename : SNAKED.ASC

Printing Labels in Snaked Columns

Craig Russell

You can print multiple records on the same line without
programming by using the Label Generator and a special index.

Often times you may want to print reports such as inventory lists,
phone lists, or the like, which are formatted with multiple records
on the same line. The Report Generator in dBASE III PLUS couldn't
do this, and without an intense study of the Template Language, dBASE
IV's Report Generator won't do this either. In contrast, the Label
Generator looks quite accommodatingit handles columns effortlessly.
Label reporting in dBASE III PLUS with more than one field per line
needed some rather complex expressions. In dBASE IV however, our
only problem is getting the order of the records right. For example,
a database sorted alphabetically with four labels across would come
out like this:

Andy Bryan Craig Dave
Elliot Fred Gene Hiram
Irene Jerry Kim Lena
Mark Naoma Oliver Patty
Quincy Rick Sheri Thomas



Notice that the columns do not wrap like a standard
phone book.

The term "snaked" column reporting refers to the manner in which the
records are printed on the page; each column's contents are ordered
such that the first element of the next column follows the last element
of the current column. Order exists only within a column, not across
columns. For example,

Andy Fred Kim Patty
Bryan Gene Lena Quincy
Craig Hiram Mark Rick
Dave Irene Naoma Sheri
Elliot Jerry Oliver Thomas


A cursory method for achieving this would be to create a database
for each column. Each column would have (in our case) one-fourth
of the complete database's records. Then, a query could be created
to link the records by record number and finally a report format could
be created from this query. This works well if all the records fit
on one page; otherwise, let's say our page is two lines long, the
following results:

Andy Fred Kim Patty
Bryan Gene Lena Quincy
------------- with a page break here --------------
Craig Hiram Mark Rick
Dave Irene Naoma Sheri
------------- and another one here ----------------
Elliot Jerry Oliver Thomas

This was defined in a previous TechNotes article as "Series
Side-by-Side" and may be suitable for your report. However, true
snaked columns ("Side-by-Side Series") will wrap in this fashion:

Andy Craig Elliot Gene
Bryan Dave Fred Hiram
------------- with a page break here --------------
Irene Kim Mark Oliver
Jerry Lena Naoma Patty
------------- and another one here ----------------
Quincy Sheri
Rick Thomas

In TechNotes June 1987 and December 1987 there were articles
and programs written in dBASE III PLUS for this very purpose which
can be improved upon in dBASE IV. With the release of dBASE IV and
the support of array type memory variables, you might think of putting
the database into an array and reordering its elements. However,
processing time is rather slow and memory space is extremely limited. Luckily,
the same algorithm to reposition the array elements can be turned
into an index, which speeds up processing time considerably.

Snakey was designed to do this reordering. It is automatic and virtually
limitless, bounded only by the constraints of the Label Generator. Snakey
requires the user to choose the database or view, its index, the label
form, the print form, the number of columns and the number of rows
in the report. It also allows for an optional two line header which,
if used, takes up the top three lines on the pageso create the
labels accordingly. The program then calls Snake which does the actual
reordering. Snake uses the following syntax:

DO Snake WITH <.DBF or .QBE>,;
<.NDX or tag name (if any)>, ;
<.LBG or .LBO>, ;
<.PRF (if any) >, ;
, ;
, ;
, ;


The Snake procedure uses dBASE IV's Label Generator as its engine
for printing. The basic algorithm used is straightforward: copy
the records in an indexed order to a new database, add enough blank
records to fill the last page, index this new file with the snake
key, and finally print out the "snaked" report (labels) from this
database.

The focal point of the entire program is the Snake index key:

INDEX ON
INT((RECNO()-1) / recs_on_page) * 1000 + ;
INT(MOD(RECNO()-1, recs_on_page) / num_rows) + ;
MOD(RECNO()-1, num_rows) * num_cols ;
TO Snake

Before we explain how this index was built, let's determine what we
want our index to look like. As an example, suppose the report we
want to print has 10 rows per page and has 5 columns across. Assume
the database is prepared to this point (i.e. has been indexed by the
desired key, copied to a temporary database and had blank records
appended if needed). Then we want the records to be printed out in
the order shown below:


1, 11, 21, 31, 41

2, 12, 22, 32, 42

3, 13, 23, 33, 43

4, 14, 24, 34, 44

5, 15, 25, 35, 45

6, 16, 26, 36, 46

7, 17, 27, 37, 47

8, 18, 28, 38, 48

9, 19, 29, 39, 49

10, 20, 30, 40, 50

...with a page break here...

51, 61, 71, 81, 91

52, 62, 72, 82, 92

Since the Label Generator prints records across the page, we want
to print Record 1 first, then Record 11, then 21 and so on. The first
50 records will print on page 1, then the cycle repeats for the next
50 records (51 - 100) on page 2. Let's pick an arbitrary record,

say 27, and examine how to tell it where to print. Notice that Record
27 is in the 7th row and 3rd column. This means that we had to print
6 rows of 5 records each and the first two records in row 7 before
we print Record 27. Thus, we get the formula 6 * 5 + 2, which tells
us that 32 records were printed prior to Record 27so, Record
27 is the 33rd record in our desired index.

Now the problem is telling dBASE IV what we just figured out above. First
of all we need to determine the desired positionrow and column
numbersof an arbitrary record on a page. As can be seen in our
example, with 10 rows, for all the records in row 1, MOD(RECNO(),10)
= 1. Similarly, for all the records in row 2, MOD(RECNO(),10) = 2. This
is the pattern we will use to get the row number, but a problem occurs
in the last row; for row 10, MOD(RECNO(),10) = 0. We need to make
some adjustments so that row 10 won't come before row 1. The necessary
adjustment is to subtract one from the Record Number before we calculate
the modulus and then add it back in afterwards. Our final formula
for the row number is

Row = MOD(RECNO()-1,num_rows) + 1

where num_rows is the number of rows on a page. For
Record 27 we get

MOD(27-1,10) + 1 = MOD(26,10) + 1 = 6 + 1 = 7

which coincides with our table.

The column number formula is a little more difficult. For reasons
similar to the row formula (to adjust for modulus values of 0), we
want to subtract 1 from the Record Number to determine which column
a record is in. Doing so, we can see that all the records in column
3 are twice divisible by 10 with some remainderin other words,
they are all in the 20's. Putting this into a formula we get

INT(RECNO()-1/10) = 2

for all the record numbers in column 3. Again we
must add 1 to get the correct column. (This is because the INT()
function "rounds down" to make the first column 0 instead of 1.) So
far so good, but what about records greater than 50? We can't have
Record 87 in column 8. To compensate, we MOD() the RECNO() with the
total number of records on a page. Finally, our column number formula
looks like this:

Col = INT(MOD(RECNO()-1, recs_on_page) / num_rows) + 1

Or, using Record 27 again

INT(MOD(26, 50) / 10) + 1 = INT(26/10) + 1 = 2 + 1 = 3

Okay, so far we know how to get the Row and Column of an arbitrary
record in our report, now we need to build the index. Earlier we determined
that to find the index of Record 27 we had to first count through
the preceding rows, then count over to the column position where Record
27 was. Incorporating the final addition of 1, we did this with the
formula 3 + 6 * 5 to get the 33rd position. More generally, the formula
is Col + (Row - 1) * num_cols, and substituting our values for Row
and Col we get

INT(MOD(RECNO()-1, recs_on_page)/num_rows) + 1 + ;
MOD(RECNO()-1, num_rows) * num_cols

(Since we wanted Row-1, we dropped the 1 that was added to the modulus
in the Row formula.)

We are now done with the indexfor page 1. What about the other
pages? We already addressed the Column formula for Record Numbers
greater than 50. The Row formula isn't affected by higher record
numbers. So, how do we distinguish between the Record in Row 7 and
Col 3 on page 1 from the Record in the same position on page 2? Before
we answer that question let's again think about how we're printingspecifically,
we have to print page 1 before we can print page 2. This means that
all the records on page 1 have to have lower index values than those
on page 2. Furthermore, we have to know what page an arbitrary record
is being printed on. The almost correct solution is to divide the
Record Number by the total number of records on a page, take its Integer
value and add 1 to it. For example,

INT(97/50) + 1 = INT(1.94) + 1 = 1 + 1 = 2

The problem occurs once again at the transition point, in this case
from page-to-page, so we must subtract 1 from the Record Number to
get

Page = INT(RECNO()-1,recs_on_page) + 1

However, simply finding the page number is not enough to provide us
with an accurate index. We can have a Record at Col 4, Row 1 on Page
2 with the index value 2 + 4 + (1 * 5) which equals 11. As we have
already seen, Record 27 on Page 1 has an index value of 33. This
is no good. So, we "weight" the page number by multiplying it by
1000 before we add it to the other index parameters.

In our final index key we dropped the additions of 1 because these
constants do not affect the resulting order:

INDEX ON
INT((RECNO()-1)/recs_on_page)*1000 + ;
INT(MOD(RECNO()-1,recs_on_page)/num_rows) + ;
MOD(RECNO()-1,num_rows)*num_cols ;
TO Snake

Now that the index is established, a few more notes on the workings
of the program are in order.

The Label Generator is vital for Snake to work properly. If there
are to be any literal text characters in your label format (such as
a comma), you must make them calculated fields based on the fields
actually having data in them, i.e. IIF(Len(Trim(LAST))=0,"",","). This
is for the case when not enough records fill up the last page and
a column must be blank so the record's fields are set to null strings.

Additionally, the Label Generator suppresses blank lines; so if you
want a blank line to be generated when the field is empty, put a literal
space character on that line. This will cause the line not to be
suppressed.

To use the program, simply create a label format where the label has
the desired fields and text for the report. Then, at the dot prompt,
either type DO Snakey to be prompted for values or DO Snake WITH list> to expedite the procedure.