DOCUMENT #684
=======================================================================
PRINT MANY LABELS PER RECORD
=======================================================================
Product: R:BASE Version : 3.1 or Higher
=======================================================================
Area : PROGRAMMING Category: LABEL PRINTING
=======================================================================
R:BASE has an excellent labels program. It makes one, two, or three
across labels, as well as rotary file cards and other card formats.
The labels can be printed to the screen, a printer or a file. In
addition to the default label formats, labels can be customized in
several different ways to match just about any label style.
Normally, one label is printed for each row or record in your table.
Sometimes, however, several labels are needed for each record. For
example, some mailings require one label for the letter being sent
and another for the envelope. Often a third label is used in-house
for the internal copy.
This article explains a simple method of producing several labels per
row of data in a table. The Employee table in the CONCOMP sample data-
base is used for illustration. There is an existing label definition,
emplabel, that is used with this procedure. The procedure works with
any label that is already created, or a new label definition can be
created.
The first step is to define a "dummy" table and a view. The "dummy"
table has the same number of rows as labels you want to print for each
record, the view joins the "dummy" table and the data table to create
the data for printing.
STEP 1
======
CREATE TABLE How_many (empid INTEGER)
This is just a one-column table, using an integer column, that is used
to determine the number of duplicate labels to print. Rows are deleted
and inserted into this table to match the number of rows you wish to
print for each label.
CREATE VIEW emplabview AS SELECT +
empid,emptitle,empfname,emplname,empaddress, +
empcity,empstate,empzip,empphone,empext,hiredate,entrydate
FROM employee,how_many
This view selects all the columns from the table the label is based on,
Employee, and joins that table with the "dummy" table, How_many.
NOTE: Use the F3 and Shift-F6 keys to let R:BASE type all the column
names from the employee table, making this view definition easy.
Since the view Emplabview joins two tables and has no WHERE clause to
link the tables, it produces what is known as a Cartesian product. A
Cartesian product is simply a result where each row of one table or
view is matched with every row of another table or view. This produces
a result containing the number of rows from each table/view multiplied
together. In this example, if the Employee table has 7 rows, and the
How_many table has 2 rows, the view, Emplabview, will have 14 rows,
2 duplicate rows for each employee record.
By using the Cartesian product principle, the number of labels to be
printed for each employee is adjusted by simply changing the number of
rows in the How_many table. If you want three labels per employee then
insert three rows into the How_many table.
The second step of the process, after creating the view and the "dummy"
table, is to change the existing label definition, emplabel, so that it
works with the Emplabview view instead of the Employee table. Do this
by making a copy of the label and associating it with Emplabview.
STEP 2
======
Using the R:BASE menu system:
<> Select LABELS from the REPORTS menu
<> Select CREATE/MODIFY from the LABELS menu
<> Select COPY from the LABELS definition menu
-- Choose the Emplabel label
-- Give it a new name like mremplab (for multiple row employee
label)
-- Choose Emplabview as the driving table/view
-- Change or enter a description, press enter and the new label
is done!
Since the view uses the same column names as the table, no
modifications are needed to the new label. You can even delete
the old label and then rename the new label to the old name.
That way existing applications work without being modified to
use the new label name.
Now that you have created the How_many table, the Emplabview view and
modified the label definition, all you have to do is to insert into
the How_many table the number of rows to match the number of labels
you want for each employee. Use the command:
INSERT INTO How_many SELECT empid FROM employee WHERE LIMIT=n
where "n" is the number of labels to print for each employee.
If you always print the same number of labels per employee then you're
done. If you sometimes print 2 labels and sometimes print 5, read
below to see how to create a simple command file to dynamically specify
the number of rows (and therefore the number of labels to print) in the
How_many table and print the labels. The third step is defining the
command file and automating the process.
STEP 3 (optional)
==================
You've already created the table How_many, the view, Emplabview, and
modified the label to reflect the viewname. Now, use R:BEDIT or any
text file editor to create your command file. Give it a name like
MREMPLAB.CMD (for multi-row employee label command).
*( MREMPLAB.CMD)
*( Connect the database and set the environment )
SET MESSAGES OFF
SET ERROR MESSAGES OFF
CONNECT concomp
CLEAR VARIABLE vlabcount,vkey,vresp
*( clear a screen area to write on )
CLS FROM 5,10 TO 15,76 BLACK *(3.1A or higher)
CLS FROM 4,8 TO 14,74 CYAN *(3.1A or higher)
*( write a message to the screen explaining the purpose of the
command file )
WRITE 'This command file lets you select the number of labels' +
AT 5,10 BLACK ON CYAN
WRITE 'you will print for each employee. ' AT 6,10 BLACK ON CYAN
*( The dialog command asks the user for the number )
DIALOG 'How many labels would you like? ' vlabcount,vkey,1 AT 7
*( Error checking )
IF vkey = '[Esc]' THEN
RETURN
ENDIF
IF vlabcount IS NULL THEN
RETURN
ENDIF
SET V vlabcount integer
IF vlabcount NOT BETWEEN 1 AND 100 THEN
WRITE 'The limit must be between 1 and 100 labels per employee. ' +
AT 23 10 YELLOW ON RED
PAUSE 2
CLS
RETURN
ENDIF
WRITE 'MREMPLAB will produce ',.vlabcount,' labels per employee.' AT +
11 10 YELLOW ON CYAN
WRITE 'Press any key to continue. ' at 12 10 RED ON CYAN
PAUSE
DELETE ROWS FROM How_many
INSERT INTO How_many SELECT empid FROM employee WHERE +
LIMIT=.vlabcount
CLS FROM 11 10 to 12 60 CYAN *(3.1A or higher)
LABEL RETRY
DIALOG 'Is the printer ready?' vresp vkey YES at 7 *(3.1B or higher)
IF vkey='[Esc]' THEN
RETURN
ENDIF
IF vresp='YES' THEN
OUTPUT PRINTER
LBLPRINT mremplab ORDER BY empid
OUTPUT SCREEN
ELSE
GOTO RETRY
ENDIF
CLS
RETURN