What's an AutoIncrement field?

AutoIncrement, or AutoInc for short, is a new type of field, available in level 7 DBF tables.  The values in AutoInc fields are integers, and they are read-only.  As the name implies, these values are supplied automatically by the Borland Database Engine each time a record is appended to the table.  The value for the AutoInc field in the next new record is stored in the header of the DBF file, and that value is only assigned to the field when the record is actually saved to the DBF. Therefore, no matter what kind of caching or buffering is going on, the AutoInc values in the records of a given table will all be unique.

Once an AutoInc value is assigned for a particular record, it is permanent.  When a row is removed from a table, the AutoInc field values of the other rows do not change.

In the old days, if you had a table where a unique identifier for each record was required, you had two choices.  You could identify the record by the record number, or you could create a special field that would acquire a unique number or code, via some home-grown mechanism.

Using record numbers proved to be a bad idea, because they are simply not permanent.  When you PACK a table that has deleted records, the record numbers get rearranged.

Home-grown mechanisms that provide a permanent unique number or code for each record were always a little complicated, and seemed slightly precarious in busy multi-user environments.  One of the best efforts at such a mechanism is Romain Strieff's SeqValue.CC, which was written for Visual dBASE 5.5x.

AutoInc fields provide us with a simple and elegant way to meet this need, with no fuss or muss.

What are the limits?

The first thing you'll notice when using the Table Designer is that an AutoInc field is only 4 bytes long.  Does that mean that it can only count up to 9999?  No, that would be pretty useless, wouldn't it?  It's 4 bytes, not 4 digits.  That means it can hold 256⁴ permutations of values, or  4,294,967,296 different possible values.  The first value normally begins at 1, then counts up to 2,147,483,647, then rolls over to -2,147,483,647 and begins counting back up to zero.  The practical limit is actually somewhat less - in fact it has to do with the limit of the DBF size, and therefore depends on the record structure.  But the main thing is that in reality, the limit will never be reached in most tables.

How can we control the values?

Normally you don't need to.  In most applications, AutoInc values are used only for relating data between tables, and the user never sees them.  So it doesn't matter what the values are, as long as they are unique and permanent.  However, there's nothing to stop you from making these values visible to the user - as invoice numbers, for example - and in this case you might want to be able to “reset” the AutoIncrement counter to some arbitrary value.  For example, most companies would prefer to start with invoice number 10,001 rather than 1, which might give the impression that the company is on its first trip around the block. Visual dBASE 7 does not have any built-in functions to control the next AutoInc value, but we can mess with the header of the DBF with low-level file functions to control it.  The wheel to achieve this has already been invented, and it's available as freeware, in Ken Mayer's dUFLP library.  Look for the file called Dbf7File.CC. If you decide to mess with the AutoInc counter, you might want to create a DISTINCT index tag on the AutoInc field, to prevent the accidental creation of non-unique values. The BDE will issue a “key violation” error if you try to create a duplicate key - there's probably no way to handle this error gracefully, but it's certainly better than letting the problem go undetected.

Why use AutoIncs?

AutoInc fields provide us with an easy and secure way of relating the data in two or more tables.  This is illustrated in the example that follows.

A caveat to remember: Because the value of the AutoInc is not determined until the row is saved, you cannot create a child row in a parent-child relationship until the parent row is saved.

Additionally, AutoIncs give us persistent references that we can use to instantly locate specific rows. If you're experienced with VdB7, you already know that a rowset.bookMark() is only useful within that instance of that rowset, and even then, bookmarks are unusable after certain actions, such as changing the Query's SQL string, or issuing a Requery(). To set the row pointer to a desired row, rowset.findKey(nAutoIncValue) is very reliable.

An example of programming with AutoIncs

Suppose we have a small database, consisting of three tables: Regions, Customers, and Invoices.  Here are the exact structures of these tables:

Table	Fields	Type, size
Regions.dbf	Region_No	AutoInc
	RegionName	Character, 30
Customers.dbf	Customer_No	AutoInc
	CustomerName	Character, 30
	Region_No	Long
Invoices.dbf	Invoice_No	AutoInc
	InvoiceDate	Date
	Customer_No	Long
	InvoiceAmount	Numeric, 9, 2

Our little database is very nicely normalized, meaning the duplication of data is kept to a minimum.  This design allows us to easily provide our users with the views of the data they'll probably want.  Here are some demands that a user is likely to make:

Show me the invoices for a specific customer

Show me the customers in a specific region

Show me how much has been invoiced for a specific region, or for a specific customer

Give me a user interface that easily lets me get all the above information, and easily lets me enter data into the 3 tables.

This single form is really a complete mini-application. The user can add or edit the rows of data directly in the grids. The LookupSQL properties of the Customers->Region_No and Invoices->Customer_No fields cause these fields be automatically morphed in the grids so that names are shown instead of the numeric values, and the user can choose the looked-up RegionName or CustomerName from comboboxes, right in the grids.

With the right index tags, it's easy to use MasterRowset/MasterFields to establish a parent-child-grandchild behavior, so that the user only sees the invoices belonging to the current customer, in reverse chronological order, and (optionally, with the checkbox) only sees the clients in the current region.

I should mention that in real-world applications, it's probably a better idea to make your grids read-only, and let the user add or edit rows via a dialog that has OK and Cancel buttons. There's just less chance that the user will mess things up that way. But for the purposes of this article, I decided to keep things simple and use the grids for both viewing and data entry.

Note that the user can change the spelling of a customer's name without breaking any referential integrity - in other words the customer's invoices don't get “lost”.  The same can be said of regions: try changing “USA” to “United States”, and you'll find that doing so will not break any of the links between Customers and Regions (though you'll have to scroll the Customers grid to refresh it, so that “United States” is displayed for the American customers). No special programming is required to ensure this integrity. It all happens automatically, because our references are based on AutoInc values that never change.

Here is the complete source code for the sample form. Paste everything below into a new file called AutoInc.wfm (in the Command window, type:  Modify Command Autoinc.wfm), and run it by pressing F2.

// AutoInc.wfm

// If the tables don't exist yet, create them, build the indexes,
// and add a few records containing sample data:

if not file('Regions.dbf')   // If the tables aren't found...

  create table Regions (Region_No  autoinc,;
                        RegionName char(30) )
  use Regions exclusive
  index on Region_No         tag Region_No
  index on upper(RegionName) tag RegionName distinct

  create table Customers (Customer_No  autoinc,;
                          CustomerName char(30),;
                          Region_No    integer )
  use Customers exclusive
  index on Customer_No                          tag Customer_No
  index on upper(CustomerName)                  tag CustomerName distinct
  index on str(Region_No,7)+upper(CustomerName) tag RegionCustomerName distinct

  create table Invoices (Invoice_No    autoinc,;
                         Customer_No   integer,;
                         InvoiceDate   date,;
                         InvoiceAmount numeric(9,2) )
  use Invoices exclusive
  index on str(Customer_No,7)+dtos(InvoiceDate) tag Customer descending
  // Why did we create that funny index tag?  Because it lets us view the 
  // invoices for a specific customer in reverse chronological order
  // (the most recent invoice will be at the top of the list), by applying
  // SetRange(str(Some_Customer_No,7)) or MasterRowset/MasterFields.
  //
  // An index on Invoice_No is not needed by this form.  However, if we
  // had an InvoiceDetails table, we would certainly need it.

  // Append sample data:

  use Regions
  append blank
  replace RegionName with 'Canada'
  append blank
  replace RegionName with 'USA'

  use Customers
  append blank
  replace CustomerName with 'Smith & Co.',;
          Region_No    with 1  // Canada
  append blank
  replace CustomerName with 'Weston Washers.',;
          Region_No    with 1  // Canada
  append blank
  replace CustomerName with 'Big Money Investments.',;
          Region_No    with 2  // USA
  append blank
  replace CustomerName with 'The Malarkey Corp.',;
          Region_No    with 2  // USA

  use Invoices
  append blank
  replace Customer_No   with 1,;         // Smith & Co.
          InvoiceDate   with date()-1,;
          InvoiceAmount with 100
  append blank
  replace Customer_No   with 1,;          // Smith & Co.
          InvoiceDate   with date()-2,;
          InvoiceAmount with 200
  append blank
  replace Customer_No   with 2,;         // Weston Washers
          InvoiceDate   with date()-3,;
          InvoiceAmount with 300
  append blank
  replace Customer_No   with 2,;         // Weston Washers
          InvoiceDate   with date()-4,;
          InvoiceAmount with 400
  append blank
  replace Customer_No   with 2,;         // Weston Washers
          InvoiceDate   with date()-5,;
          InvoiceAmount with 500
  append blank
  replace Customer_No   with 3,;         // Big Money Investments
          InvoiceDate   with date()-6,;
          InvoiceAmount with 600
  append blank
  replace Customer_No   with 3,;        // Big Money Investments
          InvoiceDate   with date()-7,;
          InvoiceAmount with 700
  append blank
  replace Customer_No   with 4,;        // The Malarkey Corp.
          InvoiceDate   with date()-8,;
          InvoiceAmount with 800

  use   // Close the table
endif
*----------------------------------------------
** END HEADER -- do not remove this line
//
// Generated on 12/06/1999
//
parameter bModal
local f
f = new autoincForm()
if (bModal)
   f.mdi = false // ensure not MDI
   f.readModal()
else
   f.open()
endif

class autoincForm of FORM
   with (this)
      height = 18.1818
      width = 77.2857
      text = "AutoInc sample form"
      autoCenter = true
   endwith

   this.REGIONS1 = new QUERY()
   this.REGIONS1.parent = this
   with (this.REGIONS1)
      onOpen = class::REGIONS1_ONOPEN
      left = 44.4286
      top = 1.7273
      sql = 'select * from "REGIONS.DBF"'
      active = true
   endwith

   this.CUSTOMERS1 = new QUERY()
   this.CUSTOMERS1.parent = this
   with (this.CUSTOMERS1)
      onOpen = class::CUSTOMERS1_ONOPEN
      left = 48.8571
      top = 1.7273
      sql = 'select * from "CUSTOMERS.DBF"'
      active = true
   endwith

   this.INVOICES1 = new QUERY()
   this.INVOICES1.parent = this
   with (this.INVOICES1)
      onOpen = class::INVOICES1_ONOPEN
      left = 53.1429
      top = 1.7273
      sql = 'select * from "INVOICES.DBF"'
      active = true
   endwith

   this.RECTANGLE3 = new RECTANGLE(this)
   with (this.RECTANGLE3)
      left = 0.8571
      top = 16.8636
      width = 75
      height = 1
      text = ""
      border = false
      fontSize = 8
      fontBold = true
      borderStyle = 1   // Raised
   endwith

   this.RECTANGLE2 = new RECTANGLE(this)
   with (this.RECTANGLE2)
      left = 0.8571
      top = 5.2273
      width = 75
      height = 6.4091
      text = ""
      border = false
      fontSize = 8
      fontBold = true
      borderStyle = 2   // Lowered
   endwith

   this.RECTANGLE1 = new RECTANGLE(this)
   with (this.RECTANGLE1)
      left = 0.8571
      top = 0.2727
      width = 75.1429
      height = 4.6364
      text = ""
      border = false
      fontSize = 8
      fontBold = true
      borderStyle = 2   // Lowered
   endwith

   this.REGIONSGRID = new GRID(this)
   with (this.REGIONSGRID)
      onRightMouseDown = {; this.datalink.beginAppend()}
      dataLink = form.regions1.rowset
      columns["COLUMN1"] = new GRIDCOLUMN(form.REGIONSGRID)
      columns["COLUMN1"].dataLink = form.regions1.rowset.fields["RegionName"]
      columns["COLUMN1"].editorType = 1 // EntryField
      columns["COLUMN1"].width = 42.8571

      with (columns["COLUMN1"].headingControl)
         value = "RegionName               "
      endwith

      bgColor = "white"
      cellHeight = 0.8
      hScrollBar = 0    // Off
      height = 4.0455
      left = 1.5714
      top = 0.5455
      width = 41.7143
   endwith

   this.CHECKBOX1 = new CHECKBOX(this)
   with (this.CHECKBOX1)
      onChange = class::CHECKBOX1_ONCHANGE
      height = 1.0909
      left = 44
      top = 3.7273
      width = 31.5714
      text = "Constrain customers to this region"
      value = false
   endwith

   this.CUSTOMERSGRID = new GRID(this)
   with (this.CUSTOMERSGRID)
      onRightMouseDown = {; this.datalink.beginAppend()}
      dataLink = form.customers1.rowset
      columns["COLUMN1"] = new GRIDCOLUMN(form.CUSTOMERSGRID)
      columns["COLUMN1"].dataLink = form.customers1.rowset.fields["CustomerName"]
      columns["COLUMN1"].editorType = 1 // EntryField
      columns["COLUMN1"].width = 42.8571
      columns["COLUMN2"] = new GRIDCOLUMN(form.CUSTOMERSGRID)
      columns["COLUMN2"].dataLink = form.customers1.rowset.fields["Region_No"]
      columns["COLUMN2"].editorType = 4 // ComboBox
      columns["COLUMN2"].width = 24

      with (columns["COLUMN1"].headingControl)
         value = "CustomerName             "
      endwith

      with (columns["COLUMN2"].editorControl)
         dropDownHeight = 9.9545
      endwith

      with (columns["COLUMN2"].headingControl)
         value = "Region                   "
      endwith

      bgColor = "white"
      cellHeight = 0.8
      hScrollBar = 0    // Off
      height = 4.5455
      left = 1.7143
      top = 5.5455
      width = 73.1429
   endwith

   this.INVOICESGRID = new GRID(this)
   with (this.INVOICESGRID)
      onRightMouseDown = {; this.datalink.beginAppend()}
      dataLink = form.invoices1.rowset
      columns["COLUMN1"] = new GRIDCOLUMN(form.INVOICESGRID)
      columns["COLUMN1"].dataLink = form.invoices1.rowset.fields["Customer_No"]
      columns["COLUMN1"].editorType = 4 // ComboBox
      columns["COLUMN1"].width = 30
      columns["COLUMN2"] = new GRIDCOLUMN(form.INVOICESGRID)
      columns["COLUMN2"].dataLink = form.invoices1.rowset.fields["InvoiceDate"]
      columns["COLUMN2"].editorType = 3 // SpinBox
      columns["COLUMN2"].width = 17.1429
      columns["COLUMN3"] = new GRIDCOLUMN(form.INVOICESGRID)
      columns["COLUMN3"].dataLink = form.invoices1.rowset.fields["InvoiceAmount"]
      columns["COLUMN3"].editorType = 3 // SpinBox
      columns["COLUMN3"].width = 18.5714

      with (columns["COLUMN1"].editorControl)
         dropDownHeight = 9.9545
      endwith

      with (columns["COLUMN1"].headingControl)
         value = "Customer                 "
      endwith

      with (columns["COLUMN2"].headingControl)
         value = "InvoiceDate              "
      endwith

      with (columns["COLUMN3"].headingControl)
         value = "InvoiceAmount            "
      endwith

      bgColor = "white"
      cellHeight = 0.8
      hScrollBar = 0    // Off
      height = 4.5455
      left = 1.5714
      top = 12
      width = 72
   endwith

   this.TEXT1 = new TEXT(this)
   with (this.TEXT1)
      height = 0.8182
      left = 23.7143
      top = 16.9545
      width = 29.7143
      text = "Right-click on the grids to add rows"
   endwith

   this.BTNTOTALFORREGION = new PUSHBUTTON(this)
   with (this.BTNTOTALFORREGION)
      onClick = class::BTNTOTALFORREGION_ONCLICK
      enabled = false
      height = 1
      left = 44
      top = 0.5455
      width = 31.1429
      text = "Total $ invoiced for this region"
      value = false
   endwith

   this.PUSHBUTTON1 = new PUSHBUTTON(this)
   with (this.PUSHBUTTON1)
      onClick = class::BTNTOTALFORCUSTOMER_ONCLICK
      height = 1
      left = 5
      top = 10.3182
      width = 43.2857
      text = "Total $ invoiced for this customer"
      value = false
   endwith

   this.rowset = this.regions1.rowset

   function regions1_onOpen
      local r
      r = this.rowset
      r.indexName := "RegionName"
      r.first()
      // Create a calculated field, to be used as the MasterField
      // in the parent-child relationship between Regions and Customers:
      local f
      f = new field()
      f.fieldName := 'cRegion_No'

      f.beforeGetValue := {|| str(this.parent['Region_No'].value,7) }
      r.fields.add(f)
   return

   function customers1_onOpen
      local r
      r = this.rowset
      r.indexName := "CustomerName"
      r.first()
      r.fields['Region_No'].lookupSql := 'Select * from Regions'
      // Now create a calculated field, to be used as the MasterField
      // in the parent-child relationship between customers and invoices:
      local f
      f = new field()
      f.fieldName := 'cCustomer_No'
      f.beforeGetValue := {|| str(this.parent['Customer_No'].value,7) }
      r.fields.add(f)
   return

   function invoices1_onOpen
      local r
      r = this.rowset
      r.fields['Customer_No'].lookupSql := 'Select * from Customers'
      // Now make the invoices rowset a constrained child of Customers,
      // so that at any given time only the invoices belonging to 
      // a single customer will be visible.
      r.indexName := 'Customer'
      r.MasterRowset := form.Customers1.rowset
      r.MasterFields := 'cCustomer_No'  // The 7-character calculated field
      r.first()
   return

   function btnTotalForCustomer_onClick
      local r, bm, nTotal
      nTotal = 0
      r = form.invoices1.rowset
      r.notifyControls := false   // Prevent screen flicker.
      bm = r.bookMark()
      r.first()  // Move to the first invoice for this customer.
      do while not r.endOfSet
        nTotal += r.fields['InvoiceAmount'].value
        r.next()
      enddo
      r.goto(bm)
      r.notifyControls := true
      msgBox('Total for this customer is $ '+nTotal)
   return

   function btnTotalForRegion_onClick
      local rCustomers, rInvoices, bmCustomers, bmInvoices, nTotal
      nTotal = 0
      rCustomers = form.Customers1.rowset
      rInvoices  = form.invoices1.rowset
      rCustomers.notifyControls := false   // Prevent screen flicker.
      rInvoices.notifyControls  := false
      bmCustomers = rCustomers.bookMark()
      bmInvoices  = rInvoices.bookMark()
      rCustomers.first()  // Move to the first customer in this region.
      do while not rCustomers.endOfSet
        rInvoices.first()   // First invoice for this customer
        do while not rInvoices.endOfSet
          nTotal += rInvoices.fields['InvoiceAmount'].value
          rInvoices.next()
        enddo
        rCustomers.next()
      enddo
      // Now restore everything to the way we found it:
      rCustomers.goto(bmCustomers)
      rInvoices.goto(bmInvoices)
      rCustomers.notifyControls := true
      rInvoices.notifyControls  := true
      msgBox('Total for this region is $ '+nTotal)
   return

   function checkbox1_onChange
      // Turn ON or OFF the constraint of Customers to a single region:
      local r
      r = form.Customers1.rowset
      if this.value  // If the constraint is being turned ON...
        r.indexName := 'RegionCustomerName'
        r.MasterRowset := form.Regions1.rowset
        r.MasterFields := 'cRegion_No'  // The 7-character calculated field
      else           // Turn off the constraint...
        r.indexName := 'CustomerName'
        r.MasterRowset := null
        r.MasterFields := null
      endif
      r.first()
      form.btnTotalForRegion.enabled := this.value
   return

endclass