Approach User Support homepage Approach User Support
Answers to Frequently Asked Questions about Lotus Approach
Examples LotusScripts
Example databases
Links to other Lotus Approach support services

[Return to contents]

Record numbering, including auto-incrementing serial numbers

Last updated: 27 Apr 2005 by

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?

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, under 'Maintain auto-serial numbers when creating records with a ResultSet object' in the 'FAQ's - Tips and Techniques' folder).

[Return to contents]

© Copyright, JohnBrown, Trademarks, Disclaimer, Acknowledgements.