Serial Number Script

[Kasei]

I need to be able to generate serial numbers in new records. I am aware of the auto-enter serial number features however for this database I would like to use a script to generate a serial number. Does anyone out there have any script suggestions?

Thanks for your help in advance. I am putting the finishing touches on this database and I wanted to add a little more flexibility to the serail number piece of the database.

Edited April 27, 2006 by Guest

[John Mark Osborne]

Can you describe the "flexibility" you need in generating serial numbers? It may be the auto-enter serial number feature can handle your needs. Scripts to create serial numbers can be slow when you get a lot of records and should be avoided.

[Kasei]

I would like my client to be able to issue a serial number after they have filled out their form. I need the script to be able to count then number for records and issue a serial number accordingly. The auto serial number feature issues a number without looking at the current number of files. Hopefully this helps. If you need for me to provide more details I am willing.

[John Mark Osborne]

You can count the number of records using a relationship. I would use a self-join relationship with the operator as a cartesian product (x) instead of a equijoin (=). This will allow the relationship to see all the related records from any parent record. In this case, the related records and the parent reccords are the same since it's a self-join. Anyhow, once you have the relationship, this simple calculation will count the number of records:

Count(SELFJOIN::SerialField) + 1

Add one so you get the next serial number.

Again, I don't recommend this since it can be slow once you get a lot of records.

[comment]

And if a record is deleted, the same number will be assigned twice.

See here for a safer method.

[John Mark Osborne]

Since I have never used a script to generate serial numbers, I was not aware of the issues, although it seems quite obvious now that you point it out. I read through your example but you are still using the auto-enter serial number to create serial numbers. I don't know what the person who originated this post really needs but maybe they need to do something with the serial number once it has been generated. So, it set to create a solution that wouldn't cause the problem you suggest. I have attached a file that should overcome these issues. Basically, it uses a table to keep track of the serial numbers and the script increments the serial number. This could have record locking issues in a multi-user scenario if two people try to create a new record at the same time but is unlikely since it will happen so quickly. Still, you should check for record locking just to make sure.

SERIALSCRIPT.FP7.zip

[comment]

you are still using the auto-enter serial number to create serial numbers

Yes, I am - because I don't know any other method that is 100% safe. True, we don't know what OP intends to do with the numbers. But it shouldn't matter how the numbers were generated, as long as they are guaranteed to be unique and consecutive.

In your file, if two users are creating a new record at the same time, the second user will not be able to increment the value in SERIALS::SerialNext, due to the record being locked by the first user. But there will be no problem with the next script step - so both users will end up with the same value. Yes, it is unlikely. But given enough time and enough records, it will eventually happen.

[Kasei]

Thanks for the help guys. I was able to put together a script that took care of the serial number problems.