832.TXT
     =====================================================================
     Customize Your Own Autonumbered Column
     =====================================================================
     PRODUCT:  R:BASE                   VERSION: 3.1 or Higher
     =====================================================================
     CATLOG:   Programming in R:BASE    AREA   : Data Manipulation
     =====================================================================
 
     R:BASE's autonumber feature is an excellent tool, but an autonumbered
     column increments even if a row is discarded. For example, you are
     entering data in a form that has an autonumbered column placed, and
     its current value shows 302. If you exit without saving the row and
     then re-enter the form, the column has incremented to 303, even
     though the previous row was not saved. This can result in "holes" or
     "missing" values in the column. This causes problems for applications
     that need automatically incrementing column values without gaps.
     Autonumbering is good for columns that serve as links or indexes, but
     if you need something that increments without gaps or only under
     certain circumstances, you need to customize the autonumbering
     process.
 
     You can customize autonumbering by using an entry/exit procedure
     (EEP) to do the actual numbering. In the example below, a user wants
     to generate an invoice number only if the net amount of the sale is
     greater than zero. If the net amount is zero, the invoice number
     needs to be zero. Non- zero invoice numbers must increment by one and
     there can be no missing numbers.
 
     The database has a table named Track with the following columns:
 
     Name         Description  Type
             
     colid        ID column   Autonumbered
                              Integer
     sale         Sale        Currency
                  Amount
     invoicenum   Invoice     Integer
                  Number
 
     The column colid is an autonumber column that serves as the primary
     key for the table and used to link with other tables. The column
     invoicenum is the calculated invoice number. Invoicenum can't be
     used as the primary key for the table since it can have duplicate
     values_all zero amount invoices have an invoice number of zero (0).
     The column sale is used to determine if this is a zero amount
     invoice. Of course, in an actual application, the table will have
     other columns to hold more information about the sale, such as
     customer id, tax and shipping amounts, etc. 
 
     Create a data entry form to enter the sale information. On the form,
     place the colid and sale columns. Define two expressions for the
     form:
 
     vcolid = colid
     vnet = sale
 
     An entry/exit procedure doesn't know about column values entered in
     the form, those values need to be passed into variables for use by
     the EEP. The expressions place the entered values for the sale amount
     and the id value into variables.
 
     Modify the Form Settings to make the form an entry only form_never
     use the form to edit existing data. The EEP does not calculate
     correctly when used with Edit.
 
     In Table Settings, add the EEP, invocalc.eep, After Saving Row. This
     is important; the current row must be saved in order for the EEP to
     correctly find the maximum value for invoicenum. The invoice number
     is displayed to the screen using the PAUSE command because it is
     calculated after the row is saved. The PAUSE command displays in
     either the DOS version of R:BASE 5.5 or the Windows version.
 
     *(INVOCALC.EEP)
     SET VAR vlast INTEGER
     -- find the maximum invoice number in the table
     SELECT MAX(invoicenum) INTO vlast FROM track
     -- check the amount of the invoice:
     -- if zero, vinvoicenum is set to 0,
     -- if non-zero, vinvoicenum is set to the
     -- maximum invoice number + 1
     SET VAR vinvoicenum = +
       (IFEQ(.vnet, 0, 0, (.vlast + 1)))
     -- update the table with the calculated invoice number
     UPDATE track SET invoicenum = .vinvoicenum +
       WHERE colid = .vcolid
     SET VAR vmsg = +
       ('Invoice Number: ' + CTXT(.vinvoicenum))
     PAUSE 2 USING .vmsg
     RETURN
 
     If you have a lot of rows in your table, you might find the SELECT
     MAX(invoicenum) command too slow for your application. An alternative
     method is to use a table to store the maximum number_the table is a
     single column, single row table. Using this method, the EEP code
     changes to this:
 
     *(INVOCALC.EEP)
     SET VAR vlast INTEGER
     -- retrieve the maximum invoice number
SELECT invoicenum INTO vlast FROM lastnumber +
  WHERE LIMIT = 1
     -- check the amount of the invoice:
     -- if zero, vinvoicenum is set to 0,
     -- if non-zero, vinvoicenum is set to the
     -- maximum invoice number + 1
     IF vnet <> 0 THEN
       SET VAR vinvoicenum = (.vlast + 1)
         UPDATE lastnumber SET invoicenum = +
          (invoicenum + 1) WHERE LIMIT = 1
     ELSE
       SET VAR vinvoicenum = 0
     ENDIF
     -- update the table with the calculated invoice number
     UPDATE track SET invoicenum = .vinvoicenum +
       WHERE colid = .vcolid
     SET VAR vmsg = +
       ('Invoice Number: ' + CTXT(.vinvoicenum))
     PAUSE 2 USING .vmsg
     RETURN
 
     You can modify the "set var vinvoicenum =" line so that the column
     invoicenum increments the way you want it to. Try this simple example
     and see how you could use it in your database.