Serial Number Duplication after Import

[DanBrill]

Hey,

I'm putting together a solution that I compile into a runtime using FM Developer. I'm always tweaking it. I've got a few users of the runtime solution scattered around. When I create a new tweaked version I want to distribute it to them. So I run a script that deletes all of my records and resets the auto-entered serial number to 1. They should then be able to import records from their old file into this empty file.

The problem is on the serial numbers. If they import 250 records into the empty file, the next serial number for the next record is set to 251. However, it is possible that they have a record that already has the serial number 251. (For example, suppose they had had 251 records and then deleted the 250th. There would be 250 in their database, but the last one would have serial number 251.)

I have the serial ID number set to be Unique, but this doesn't help at all. Their new version is duplicating serial ID numbers anyway.

What's wrong, and what can I do to fix this?

Thanks,

Dan

[Fitch]

If you have FileMaker 5.5 or newer, you can use Set Next Serial Value with a calculation of Max (all::serial number) + 1, where "all" is a self-join relationship based on a constant (e.g. 1).

[DanBrill]

Great. Thanks so much.

But where would I do this? In a script that is run after they import their records into the new empty database?

Thanks again,

Dan

[Fitch]

Well, it wouldn't do you much good to do it before they import.

I was just assuming that you are already scripting the import, so yes, this would just be a Set Field command you add after the import.

Of course the constant or "one" field and the relationship have to be in place as well.

Another method would be to simply Sort descending on the serial number. That will leave you on the first record, which will have the highest number. Then do the script step:

Set Next Serial Value [serial number + 1]

I.e. you wouldn't need a Max() function or a relationship. There's almost always more than one way to accomplish what you want with FileMaker.

[DanBrill]

OK, OK. No, I'd been doing the importing manually up until this point. Its really early on in Beta testing, so I hadn't gotten to automate the import process yet. Looks like I need to get around to that.

The Max() function is easy enough as I already had a self join on a constant set up for something else.

Thanks!

Dan

[cjaeger]

why not use the max()+1 function to auto-enter the serial number itself instead of an auto-serial number? make a lookup based on your selfjoin that inputs an unstored calc field max(serial#)+1 ...

[DanBrill]

why not use the max()+1 function to auto-enter the serial number itself instead of an auto-serial number? make a lookup based on your selfjoin that inputs an unstored calc field max(serial#)+1 ...

Hmm. I had thought of auto entering it based on a calculation max(self::ID)+1, but it won't let me do it because it creates a circular reference. Doing it with a lookup would be another story.... Hang on, I'll try it....

OK, I tried it and it works. But here's a problem I can see. Suppose the user creates record #100. 100 is assigned and Max()+1 goes to 101. Good. They then create some related records that refer back to record #100. Then they delete record 100. When they do, Max()+1 goes from 101 back to 100. The next record they create will be assigned 100 again. The related records will refer back to this new record 100 incorrectly.

I think the scripting method will be more reliable because the auto-serial numbers can never decrease.

Dan

[IdealData]

You will need to do this through scripting. I went through this one my self over a year ago.

[veganboyjosh]

there'sa really smokin discussion of auto-enter uniqe id's in the articles forum. they discuss using time and date funstions as well as inserting a random number into the mix, and they cover what to do when importing and exporting. it gets pretty involved, and seems to have gotten enough attention to be pretty foolproof (so far).