AUTHOR'S MAIN SITE   > > > > >  TABLE OF CONTENTS for Open Office database tutorials.

Open Office database, ooBase:

How to Change the Next Number Put into an Auto Increment Field

You may find that the database being shipped with OpenOffice (ver.2 and higher) delights you as much as it has me. This page tries to help you use it.

Forget anything you may have heard about Adabas, which came with Star Office, the commercial version of Open Office 1. The current Open Office's database, "Base", aka "ooBase", is unrelated. And remember that Open Office, including ooBase, is free! But don't let that fool you. And it's not new. Big organizations, government and civilian, are adopting it as their standard office suite... and saving million$, but still Getting The Job Done.

There's more about ooBase in the main index to this material.

This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window. With most browsers, pressing plus, minus or zero while the control key (ctrl) is held down will change the texts size. (Enlarge, reduce, restore to default, respectively.) (This is more fully explained, and there's another tip, at my Power Browsing page.)

Page contents © TK Boyd, Sheepdog Software ®, 10/08.

——  Automatically Incremented Fields   ————————————

With ooBase it is easy to set up a field which automatically increments. This is often done for a primary key's field. Every time a new record is started, the field's value is filled in with the number one larger than the number in the previously created record; the user doesn't have to do anything. ooBase calls this "AutoValue". As far as I know, in ooBase only an integer type field can be made to do this. It is turned on or off with the "AutoValue" property for the field. I abhor conflating verbs and adjectives slightly more than I abhor ungainly neologisms, so I will be using "auto-increment" and "auto-incremented", to avoid convoluted expressions. I will also write them both with and without the hyphen, to help search engines find this page with either "spelling".

——  The Number in the Auto-incremented Field   ————————

Usually the number will "mean" not much, just as the serial number on bank notes doesn't mean much. In both cases, they merely serve to tell one instance of the item in question from another.

In other cases, you may be able to make the number in the auto-incremented field meaningful. For example, if the field were for an invoice number, you might want to have the invoices for your first year of trading run from 1000 upwards, for your second year from 2000 upwards, etc.

To do this, you would re-set the start value for the auto-incremented field as the year changes. Ideally, you would do this between entering the last record in the old invoices series and entering the first of the new records. However, you can indulge in "by hand" fudging of the numbers, if necessary.

——  Re-setting Auto-increment Start Value   ——————————

The following formula assumes that you already have a table "Table1" set up with an integer type field called "ID", with AutoValue property set to "true". It further assumes that no existing record has an ID value higher than 1999.

* If the table is open, close it .

* Use the menu of the main ooBase project management window to invoke "Tools | SQL..."

* Enter the following into the "Command to Execute" memo...

alter table "Table1" alter column "ID"
    restart with 1000

* Click on the execute button.

Almost immediately, in the "Status" memo, you should see....

1: Command successfully executed.

The commands ("alter", etc.) can be entered as shown, or ALL IN UPPER CASE, (or even iN mIXED cASE!)... but the table name and the field name must be entered exactly as they are defined in the database; their names are case-sensitive. (You will, of course, have to substitute the names of your table, your field.)

Re-open the table; enter a new record. If you gave the command above, that record's ID field should fill with 1000.

Easy when you know how!

——  Acknowledgement   ————————————

I am indebted to Drew Jensen for his post in the Open Office forum for the information at the heart of this tutorial... and for many, many other posts he has written, helping us all to master the wonderful Open Office.

Editorial Philosophy

This material is "browser-friendly"... in particular, you can (and I recommend that you do!) make your browser window narrow, so that you don't have to try to read long lines.

I dislike 'fancy' websites with more concern for a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Of course, an attractive site WITH content deserves praise... as long as that pretty face doesn't cost download time. In any case....

I am trying to present this material in a format which makes it easy for you to USE it. See the main index to this material for more information about the way it is posted.

Search the ooBase tutorials. Results will open in new page.

Custom Search

Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However.. this doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!

PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE

If you liked this ooBase tutorial, see the main index for information other help from the same author.

Editor's email address. Suggestions welcomed!     - - -    Want a site hosted, or email? I like 1&1's services.

Valid HTML 4.01 Transitional Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at

One last bit of advice: Be sure you know all you need to about spyware.

. . . . . P a g e . . . E n d s . . . . .