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 
     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, +
        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 
            -- 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 )
       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
       IF vlabcount IS NULL THEN
       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
       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
       DELETE ROWS FROM How_many
       INSERT INTO How_many SELECT empid FROM employee WHERE +
       CLS FROM 11 10 to 12 60 CYAN    *(3.1A or higher)
       DIALOG 'Is the printer ready?' vresp vkey YES at 7   *(3.1B or higher)
       IF vkey='[Esc]' THEN
       IF vresp='YES' THEN
           LBLPRINT mremplab ORDER BY empid
           OUTPUT SCREEN
         GOTO RETRY