Serial Number Field

[pt2huynh]

Hi,

How do I make a serial number field so that every time a record is deleted, this serial number field will automatically be adjusted for this deletion whenever a new record is being created? I am using FM Pro 4.0. Thanks

[BobWeaver]

You mean that if a record in the middle is deleted, then you want to renumber all the records from there to the end? Why would you want to do this with serial numbers?

Answer: No you can't do this with built-in auto-enter serial numbers. But, you could concoct a script to re-number the serial number field in the database you would have to execute this every time you delete a record. From a data integrity point of view, this is generally considered to be a bad idea. Once a record has a serial number or other unique identifier, it should never change. But then it depends what you want to do with the numbers.

[tmanning]

If you truely want them renumbered, then use status(currentRecordNumber) for your serial number. This way when one is deleted, all of the numbers from the deleted one on, will renumber. It seems to me, though, that this defeats the purpose of having a serial number if it is going to change.

If what you meant to say is that you want to replace the missing number with your new record you can do this instead...

Use scripts when deleting and adding records instead of adding and deleting with key short-cuts and set up a global field. Each time you delete a record, past the serial into the global field. If you create a new record and the global field is empty, assign a new serial number and if there is something in the global field, then use that number instead. Of course, you would have to reset your seial number down by one if you don't use the number otherwise assigned. FileMaker 5.5 handles this but earlier versions don't. If you need to adjust your serial number on the fly and have a version earlier thatn 5.5, there are serveral plug-ins that can help you do that.

Hope this helps.

[pt2huynh]

Thanks a bunch for all your help!!!