Adding a serial # 3 years later!

[Baylah]

Hi List,

I have a DB with several thousand records that I would like to add serial #'s to, and update the old records.

Is there a simple script or function I can write that will run through the records and add an incremental serial # to each record?

Thanks for your help,

Steve

[LaRetta]

Hi Steve,

Create a serial field, set to Auto-Enter serial number. Place your cursor in the serial field on layout. Show All Records. Then select Records > Replace Contents. Select Replace with Serial Numbers and enter your starting serial number. Click checkbox 'Update Serial in Entry Options.'

LaRetta

[Baylah]

Perfect, could not have been easier.

Thank you. This list is so amazingly helpful.

Steve

[T-Square]

Steve--

I know this is sort of after the fact, but...

Be aware that the Serial Number feature is easy to get screwed up, and that it is possible when using this feature to re-use numbers--even if the Unique checkbox is checked.

Instead if the Serial Number option, I autoenter the calculation:

Get(RecordID)

The RecordID is Filemaker's internal unique record number, and less prone to these errors.

Cheers,

David

[Ender]

Get(RecordID) is not meant to be used for relational keys. If working in multiple versions of a file (like when doing revisions in an offline file and later importing the live records into the revised clone,) get(recordID) could produce numbers that were already used, and there's no way to update the next "recordID" value.

[T-Square]

I find that the duplicate "unique" ID is altogether more common and more damaging than using the RecordID. I can't tell you how many times I have had troubles with clients getting overlapping IDs using the Serial Number method. And, if you've built your relations to do cascading deletes, you REALLY screw things up.

In my experience, there is no foolproof way to manage IDs in a relational system with multiple installations. CaptKurt's Unique Key system works, but it results in substantial file bloat--especially in high-traffic transaction tables.

[Vaughan]

The gotcha for the record id is that though it is unqiue, it is not contiguous. There was, at least in FMP 6 and earlier, a break in the numbers.

[T-Square]

Ender--

To elaborate, my primary need as a developer of a relational database is a truly unique ID. FM's Serial feature doesn't provide that. Every other RDBMS I've used (Access, MySQL, Advanced Revelation, OpenInsight) has a functional Unique Serial Number feature.

Regardless of the Unique Serial Number That Wasn't, the real problem you're describing arises when multiple copies of a database are merged. In this situation, duplicate entries will result--and no RDBMS has a simple fix for the need to cascade changes to foreign keys.

It seems to me that, rather than trying to engineer some Grand Unified Scheme for Unique IDs, it is more effective to work out a principled method for importing records and cascading foreign key updates, which is why I go for the RecordID method.

Vaughan--

The contiguous issue is unimportant in my solutions, although I acknowledge that there are MANY circumstances where the careful management of specific IDs is required. Let's just say I'm lucky there!

Cheers,

David

[comment]

a truly unique ID. FM's Serial feature doesn't provide that.

This comes up from time to time, and I would like to get to the bottom of this once and for all. On the face of it, there's no reason why serial numbering would produce duplicate numbers, and I can't think of any circumstances that would cause this to happen (excluding developers that don't know what they're doing, of course).

Can you provide some REAL information about this - not just "Be aware that the Serial Number feature is easy to get screwed up", or "I can't tell you how many times I have had troubles with clients getting overlapping IDs using the Serial Number method"?

If it's so easy, I would really like to see a recipe for reliably reproducing the problem. Without it, all it amounts to is a rumor.

[T-Square]

Comment--

This is not some rumor, as you put it, it's my hard-learned experience.

It's easy to go into the field definition for a Autoenter Serial Number field and reset the next number to use back to an earlier number, and FM will happily start creating new records from that point--with duplicate entries occurring until someone notices the problem. This is usually after a month or so, when the problem is huge.

When this REALLY gets to be a problem is when I am working on upgrading or improving the application using a snapshot of my client's data as a testbed. The snapshot copy's Next Serial Number is up to, say, 857. If I subsequently take my client's current data (which is up to 1351) and import it into my copy, the Serial Number setting in my copy is at the snapshot level (give or take a few test entries), while the data now goes up to 1357. I return the newly-merged database files to my client (without re-setting the next Serial Number), who starts adding records that re-use the ID numbers. Relationships--both within the database and between me and my client--are strained and broken.

Cleanup is difficult and problematic--e.g.

Which related records relate to which parent? And if the relationship is set to delete related records (which it should be) then deleting the erroneous parent will delete all the good parent's children. [bTW, the workaround is to temporarily allow modification of the record ID, change the bad parent ID to a ridiculously random new ID, and then delete it. It works, but it's time-consuming and error-prone, especially if you're doing this over the phone with your client.]

Moving to the Separation Model has helped this substantially, but there are times when the backend data structure in that model must be modified, with the same issue arising. For a while, I had a startup script that automatically reset the Next Serial number to use to the highest extant ID + 1, but it slows down startup significantly, so I dropped it.

David

[RalphL]

Then it is not the autoentered serial number that is the problem but the developer.

Don't forget that making a copy or a clone may not produce the same Record ID's.

[comment]

Thanks for the clarification. I don't mean to belittle the problems you have described. I know that upgrading an integrated (data+logic) solution is a difficult and error-prone task. But that is a far cry from declaring the serial ID as unfit for use.

All you describe is a user error - someone has done something that they shoudn't have. Yes, it is easy to go into Define Database and reset the serial number. It is equally easy to change the tax calculation from Amount*TaxRate into Random. Should the conclusion be that multiplication formulae are unreliable?

I presume you have a situation where you cannot lock your clients out of messing with the database design. That's too bad - but it has little to do with the issue of serial numbers, which will work correctly, if not abused.

I believe the upgrade problem can be solved by running an import script that also resets the serial number based on the import, e.g.

Import Records []

Go to Record [Last]

Set Next Serial Value [ Table::RecordID; SerialIncrement ( Table::RecordID ; 1 ) ]

---

As an aside, just about every alternative numbering scheme I have seen is GUARANTEED to fail at some point. Mostly they rely on something that has a very small chance of happenning not to happen. But some people do win the lottery, and given enough time and enough records, there will be "winners" in this lottery as well.

[T-Square]

Ralphl--

Thanks. Next time I want a little abuse, I'll ask you.

My experiences with serial numbers in OTHER systems is that when I go to add a new record, the RDBMS simply locates the next available ID number and uses that.

FM doesn't do that; instead, what it does is assign an already-used number, and then throws up an "Invalid Entry: Duplicate Value Entered in Unique Field" error to the user. This causes a serious problem for the user--especially if the developer has carefully (and correctly, IMO) hidden the generic unique ID from the user by not placing it on the layout.

The user is stopped in their tracks because they can't enter a record; the next ID is invalid, but they can't put in a different number because the field isn't accessible to them (and even if it were, that field would be set to be unmodifiable).

This problem propagates into scripts that work at one time and then mysteriously stop working because the script hangs with the duplicate ID. In this situation, there is no indication of what's gone wrong.

So, since OTHER RDBMS platforms seem to be able to handle this without hanging, I DON'T see this as a developer failure or a user failure, but a Filemaker one.

Comment--

Your solution is a workaround--a KLUDGE--to remedy something that the software should handle seamlessly. As I mentioned in my earlier post, I had the same kludge installed, but removed it because it was so slow.

David

[Ender]

Sorry David, but you haven't convinced me. The example you sited where a serial number gets reused because you make changes to an offline copy then give the file back to the client (who has subsequently added records,) could still happen with Get(RecordID).

By using FileMaker's auto-entered serial numbers, the problem can mostly be avoided by adding that Set Next Serial Value[] script step immediately following the Import[], as comment has shown. This would not be a slow process since it only happens once, following the scripted import. I say "mostly" because allowing imports to be performed manually would require manually updating the next serial value, a process that is easily forgotten.

BTW: although the validation to enforce Unique values on serial numbers seems like a good idea, it should not be necessary, and is problematic as it slows record creation in large files.

[T-Square]

Ender--

Well, okay, fine. I think this is at the "Let's Agree To Disagree" point. I've noted what I think are the idiosyncrasies of Filemaker's implementation of serial numbers. You've noted ways to work with (or around) them.

I *still* believe that an "Automatic" feature of the DB app--especially one as critical as the auto-assignment of a serial number ID--ought to monitor itself so that it doesn't attempt to assign a pre-existing ID. I *still* believe that if a client selects New Record or Duplicate Record, that Filemaker ought to be able to find an unused ID number to assign. Without that (IMO) basic functionality, every developer out there has to learn some way to account for the behavior.

David

[comment]

I agree that having a "self-adjusting" serial number would be a useful feature to have.

I do NOT agree that in the absence of such feature one should abandon serial numbers altogether - certainly not in favor of less satisfactory solutions.

since OTHER RDBMS platforms seem to be able to handle this without hanging, I DON'T see this as a developer failure

You work with the tools you have. Filemaker does NOT have this feature. A developer that expects his client to import their records WITHOUT adjusting the serial, is not doing a good job. Or, in your own words, "Without that (IMO) basic functionality, every developer out there has to learn some way to account for the behavior."

In any case, I am grateful to you for clarifying the issue. Now I can go on using serials without that doubt nagging in the back of my head.

[T-Square]

Comment, Ralph, Ender--

With the heat of yesterday passed, I have re-read the entire thread again, and I'll take a moment to apologize to all of you for getting aggressive. In my frustration, I often get into pointless shooting matches with people who actually are my allies.

What I'm taking away from this discussion is that every solution to this problem (mine included) entails compromise, maintenance and vigilance.

Ender--your point about flaws with Get(RecordID) is spot on. As a result, I plan to look into this in my own solutions--yet again!--and consider changing the backend--yet again!--to something else.

I have a haunting feeling I'm like Dorothy in the Wizard of Oz, and I'm about to find out that "there's no place like home" (i.e. serial numbers) after all.

After all the dust has settled (mostly in my brain), I think I'll try to write a white paper of my understanding of all the issues involved in this and send it in for general consumption.

David