Record numbering, including auto-incrementing serial numbers

[Return to contents]

Record numbering, including auto-incrementing serial numbers

Last updated: 27 Apr 2005 by XpertSS.com

It is often useful to have a unique identifier for each record in a database -- like a customer number or order number, for example. Or to use in joins where other unique fields are not available. For example, you would not want to use a telephone number in a join because the number could change over time. So it would appear useful to just use the record numbers Approach assigns as you add records. You know, those numbers you see in the status bar at the bottom of the Approach screen?

But this is not the case. Why?

The record numbers are not part of the database and are not permanently assigned to particular records.
When you sort the records, the record that is #1 changes to the one that is "relatively" the first one in the current database.
When a record is deleted that record's number is not also deleted.
The record numbers are not part of your data, and you do not have access to the record numbers in calculated fields or finds etc. Not even from a script!

If you want each record to have a fixed reference number that never changes as long as the record continues to exist, then you need to create a numeric 'serial number' field which automatically increments each time a new record is created. To do this, just create a numeric field using Field Definition. Then select the desired starting number and increment value in 'Options'. If you have existing records with no number in this field, you will want to add a unique number to them using the technique described elsewhere in this FAQ in "Looping Macros".

Unfortunately, the auto-incrementing serial numbering options on numeric fields are not accessible from LotusScript, which means that you cannot read or change the next serial number from a script. In other words, if you add a record using a ResultSet in LotusScript, the serial number will not be entered or incremented. In this situation you have two options:

1) you can write your script so it doesn't use a ResultSet and instead adds and updates the record using a view.

2) you can create and use your own auto-incrementing field instead (details of how to do this can be found at http://www.xpertss.com, under 'Maintain auto-serial numbers when creating records with a ResultSet object' in the 'FAQ's - Tips and Techniques' folder).

[Return to contents]