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]

Setting the auto-incrementing serial number

Last updated: 12 Jul 2002

Unfortunately the auto-incrementing serial numbers that you can set up in the Options>>Default values part of the field definition of a numeric field cannot be accessed or changed using a macro or LotusScript. The only way of changing it is via hand, or by using the SendKey command.

Alternatively you can ignore the auto-increment function altogether and create your own auto-incrementing serial number. This would allow you to also us a much more meaningful serial number. For example in the following section Sue Sloan explains how to create an auto-incrementing serial number which starts with the year (eg 2002-00001). She write:

1) Set up a new database - I would call it "control" - with a field in it to use for the "next number", a field for the "current year", and a field "link", text, 1 character defaulting to an X and validated as "unique". Enter a starting number in the "next number" field (not a default number or serial number - enter the starting number on a form view). Enter the current year in the "year" field.

2) Add a similar "link" field to your database where you have the field to be numbered. The default value also would be X. Fill that field with X
using Fill Field on a worksheet view, Remove the serial numbering default option from the number field in this database and validate it to not be blank.

3) Join the control database to your database using the "link" fields. Set all join options off.

4) Now you must force the user to create new records in your database using a macro and a button. The macro would be:

. SET yearfield = control.year
. SET numberfield = control.nextnumberfield
. SET control.nextnumberfield = control.nextnumberfield + 1
add whatever you need here

This works even in a multi-user system because two users cannot get the
same control.nextnumberfield value.

5) Now all you need to do is add a macro named OPEN to your application. It goes to a view based on the "control" database and compares Year(Today) to the "year" field. If it is not the same, the "year" is changed and the "next number" is set to 1.

. VIEW switch to control worksheet
. RUN If(Year(Today()) = control.year then end macro (or go to whatever
macro you wish to go to)
. else continue this macro
. SET control.year = Year(Today())
. SET control.nextnumberfield = 1
. do whatever else you need to do here

Note that the only danger in having this run automatically when the
Approach file opens is that you may have a user with his/her computer date set incorrectly. You may want to add a test to make sure the Year(Today()) is > control.year, for example.

[Return to contents]

© Copyright, JohnBrown, Trademarks, Disclaimer, Acknowledgements.