Re-Incrementing Record Numbers?

[Mark L]

(Fortunately this question is more theoretical rather than some immediate crisis – which is often the case with my other postings:)

I have a medical application that uses a long list of Vitamins that I’ve imported from Excel into FMP 7 to create a “Vitamins” table. I specifically used auto-increment to assign a record number to each of a couple hundred records. This was intended as a “one time” import – I shouldn’t need to do it regularly.

As part of the application, I use the record number to do look-ups: The record number gets stored in a “Medical Tests” table – I use a portal to display the actual Vitamin names. All works fine – but here’s my concern:

At one point I deleted the contents of my “Vitamin Table” – and re-imported it. Auto-increment started from the last number of the previous, now deleted set of records. (Example: Original set of Vitamin records had Record #’s 001 to 200. After deleting them and re-importing them, the new records were 201 to 400.) Fortunately for me, I hadn’t done any “assignment” of Vitamins to the Medical Tests records – but if I had, I would have been in trouble – as numbers in the Medical Tests records would no longer match those in the Vitamins table.

Is there an easy way to prevent problems like this?

The only thing that come to mind – and I’m not even sure if it’s possible - is to have a script that would re-increment the record numbers?

What other solutions are there?

Thanks,

Mark

[Mike D.]

Mark,

There are a couple ways to reset the auto numbering for a field. The manual method is to into the database definition and click on the Options button when that field is highlighted. You can then change the next number to be whatever you want.

There is also a script step, Set Next Serial Value, that can do that for you if you script the import process.

Mike

[Lee Smith]

As Mike stated, you can do this in a couple of ways.

However, I caution against re-incrementing a serial number or record ID Number, as it could have an effect on things such as relationships, and it can not be [color:red] Undone

Lee

[Mark L]

Mike & Lee-

Thanks for the ideas - I will make sure to make use of them next time this circumstance arises.

And yes, Lee, you warning makes good sense.

Thanks again,

Mark