Duplicate serial numbers on network

[Peleka]

I have a point of sale system with two G4s networked, one as host the other as guest. On creation of a new invoice the invoice number is created through the auto-enter serial number parameter in the field definition. On occasion, maybe once every 50 invoices, and always when both machines are creating invoices at the same time, we will end up with the same number on more than one invoice.

Thoughts?

Peleka

[DanBrill]

I notice that in FM 7 you can have the auto-enter serial number created at the time of record creation or at the time of record commitment. I'm not really sure the implications of either setting, but this might be the problem. At least worth checking on.

[Peleka]

I noticed that too. I left it at th default setting, which sets the number on creation of the record as opposed to commit, which I imagine is more like it was in previous versions. But then, I could be wrong.

[DanBrill]

Hmmm. That's what I assumed too. I only have one copy of FM7 at this point, so I can't do network testing yet. Did you have this function running in a FM6 version of your database? Did you ever have the probelm?

[stanley]

I never noticed this before, but it seems like a dangerous piece of programming by FMI. If the s/n is set for record creation, it can erroneously dupe them; if set for commit it acts as before? That's what it seems like it's doing.

-Stanley

[DanBrill]

Oh, this is a bit ugly. But it seems counter-intuitive -- doesn't creation come before commitment? It seems that if it assigns the ID at commitment then if I create a new record and start entering data, then you create a new one, but I haven't committed mine yet, one of us is going to have a problem.

When does the ID get incremented? That seems to be a crucial part of the problem.

As I expiriment, I can't seem to see a difference. If I set up 2 auto-enter ID fields, one set to assign at creation, and the other set to assign at commitment, they both get filled in as soon as I create a new record. Commitment doesn't seem to have much to do with it.

Can anyone explain?

[Reed]

This doesn't happen for me (reply to dan)

If I set up the field for "on commit" nothing appears in the serial field until I click out (commit) the record.

It seems like this would make the biggest difference in cases where one user creates a record and then deletes it before committing. "On commit" would not use the next serial value so records deleted before committment wouldn't leave gaps in the sequence.

I haven't seen any duplicate serials myself (I alway use "on creation") but then I only have two copies of fm7. Hosting on 10.3.4 and client on XP sp1. Are you using FMP to host, or do you have FM server? Maybe there's a bug in this new product.

[DanBrill]

DanBrill said:

If I set up 2 auto-enter ID fields, one set to assign at creation, and the other set to assign at commitment, they both get filled in as soon as I create a new record. Commitment doesn't seem to have much to do with it.

Disregard that bit. It does seem to be doing what it ought to in that respect -- creating the ID at creation or at commitment. But it still doesn't explain the original question.

My theory -- though I can't test it as I only have the one copy of FM7 so networking is out -- is that if you have it to assign the ID at record creation it does, but it's not actually incrementing the next value until commitment. So if the record is creted but isn't committed yet and another user creates a new record, they get the same ID.

Does that make any sense?

If this is the case, then we'd want to assign the record at commitment in order to avoid duplications. But the down side of that is that you may want the recrod to have the ID set as soon as the record is created so that self-referential relationships will work.

The only workaround for this is to make sure that the creation of a new record is always scripted so that you can commit the record on creation, or to create an ID that increments without using the auto-increment feature.

Thoughts?

[Reed]

I just did a test where I used 7.01a preview on my Dual G4(host), and 7.02 Dev on my ibook(client)

I made two tables in a file one where the serial is created on creation, one on commit.

I sat with my finger on both keyboards hitting command-N in a random fashion. I created about 1000 records in each table this way. (carpal tunnel.... setting in) No dupes for me.

Were you changing the field def (for the serial) while people were adding records? Maybe the bug is related to that?

[DanBrill]

Well I can't reproduce it, but it concerns me that 2 people are seeing it since this if this step doesn't work, nothing else you do will matter much. The whole of relational theory pretty much hinges on it.

Peleka? Stanley?

[Reed]

stanley said...

I never noticed this before, but it seems like a dangerous piece of programming by FMI. If the s/n is set for record creation, it can erroneously dupe them; if set for commit it acts as before? That's what it seems like it's doing.

FM6 (and below) increment on creation there is no way to wait until commit. (unless you use globals and script record creation from a separate interface file. Are you actually able to reproduce the behavior of the original post? Dan is right, if this is really a bug, it's a doosy

perhaps you could post the file that's giving you this problem...

[cellmaker]

I don't have a network setup, so I can't test what you're experiencing.

However, you might try a different approach to setting a new serial number, rather than using the built-in auto-increment function. I set up a separate self relationship (here, allrecs) and then use this calculation to set a new serial:

If(isempty(SerialKey),1,max(allrecs:serialkey)+1)

In theory this should force FM to check all of the existing keys and then assign a new key 1 higher than the greatest existing.

Don't know if this solves your issue not.

[DanBrill]

Hey cellmaker,

That's what I meant by "or to create an ID that increments without using the auto-increment feature."

However, the max function is really slow. So just take it out. Make sure that the allrecs relationship is set to sort by the serialkey, descending. This will grab the first related serialkey, which will be the max by virtue of the sort order.

I just picked up this trick from Queue a few days ago.

But I'm still hoping not to have to resort to this just to assign ID numbers.

Dan

[cellmaker]

Dan -

Holy Schmite. I had no idea that the "internal" sort order now dictated which related record would be considered "first". I assumed that the old condition for "first" (the first-created record that was valid) still held. Many thanks for that info.

There must be many more uses for this new state.

Thanks. And thanks to Queue, too.

c

[cellmaker]

Dan -

Follow-up to the above.

Well, I attempted to implement the "non-max" (i.e., the sort version) type of incrementer. It doesn't want to update. (It did update at one point, but I'm not sure what caused it to do so.) I have tried adding records, deleting, resorting, everything.

Anyway, I wonder if I'm thinking of this all wrong and there's a much easier way of doing this.

In my "max" version, I have set up two table occurrences ("base" and "allrecs") for the purposes of this internal calculation, independent of where I am in the solution. They are "x" joined so that all records are related to each other. Then the calculation looks like:

c_nextKey = If(isempty(base:SerialKey),1,max(allrecs:serialkey)+1), evaluated from base.

(New record auto-enters c_nextKey.)

This works without fail.

When I change the calculation to:

If(isempty(SerialKey),1,allrecs:serialkey+1) with the relationship sorted descening for serialkey, as you suggested, it has only once properly calculated the value. (c_nextKey is in any event unstorable because of the "x" join dependency.)

Anyway, am I overcomplicating this? Undercomplicating it?

[-Queue-]

Charlie, AFAIK the sort order for a relationship has always determined the first related record. At least it holds back to 4.1v3.

I tried duplicating your issue and discovered that, at least for 7.0v1, it doesn't seem to sort at all! The v2 update may have fixed this issue; but I haven't updated my version at work yet, so I can't say for sure. Someone else may be able to confirm whether this is true or not. If it doesn't, this is quite disturbing and I would see no alternative but to stick with your Max solution.

[DanBrill]

If I recall correctly I learned this trick after getting 7.0v2. It works for me now. In fact, my solution now depends on it heavily. In following the suggested Separation Model that requires fields each data record to have a corresponding calculation record in another file, I've used this as a substitute for Max so that the greatest ID number in the data table is sure to have a matching record in the calc table.

Thanks again for the tip, JT. It surely does work.

[-Queue-]

Ok, phew, glad to hear that. Thanks, Dan.