How to prevent Missing Serial Numbers

[Scott Pon]

We have a sales order system. The Sales Order Numbers are Serial Numbers. Somehow there are times that the Sales Order Numbers will have gaps in them, ie Serial Numbers will disappear. I have a feeling that the records are not being saved or undone.

1) Anyone know how this might occur?

2) Any way to prevent this from happening?

Thanks in advance. I will follow up any additional questions.

[bcooney]

Can you delete a sales order? If so, that would explain a gap. Perhaps, if all numbers must be accounted for, you could Void sales orders instead of deleting them.

[mr_vodka]

Allowing a method to go back and fill in missing incrementing serial number can be very dangerous. You should rely on FileMaker's internal auto-enter serializing. If you try and go set it to Max (ID) + 1 if on the chance that 2 people run the script at the same time and the record hasnt been committed, you can get duplicate serial IDs.

If you must have some kind of incrementing consecutive number for the invoice, DO NOT use this as the primary key.

[Scott Pon]

Can you delete a sales order? If so, that would explain a gap. Perhaps, if all numbers must be accounted for, you could Void sales orders instead of deleting them.

No you can NOT delete any records/sales order numbers.

Thats part of the reason why this has be baffled.

[Lee Smith]

Are you using FileMaker's Serial numbering system? or do you have one that you made up.

[Scott Pon]

Allowing a method to go back and fill in missing incrementing serial number can be very dangerous. You should rely on FileMaker's internal auto-enter serializing. If you try and go set it to Max (ID) + 1 if on the chance that 2 people run the script at the same time and the record hasnt been comitted, you can get duplicate serial IDs.

I've been careful when I put them back in. I have to do it due to audits and such.

If you must have some kind of incrementing consecutive number for the invoice, DO NOT use this as the primary key.

Invoicing has not been a problem since it is accomplished in a different (accounting) system. Only creating Sales Order Numbers.

[David Jondreau]

You have a setting on the auto-entry options for a serial number, to generate the number "on creation" or "on commit". The default setting (and the only one I've ever used) is "on creation".

If this is the setting and a user Reverts a record before the record is committed, that serial number will "be lost".

I find users rarely intentionally revert a record without prompting, but often revert records because of a failed validation and the option is presented to them.

[Scott Pon]

Are you using FileMaker's Serial numbering system? or do you have one that you made up.

When we were in version 5, the original programmer (not me) set up a field "Sales Order Number" as an Auto-Enter Serial Number

Since I upgraded to FM version 8, "generate on Creation" is selected (I have not looked into "generate on commit").

[David Jondreau]

As far as a solution...

As I said, I've never used the "on commit" auto enter, so I can't speak to of that. But I'd certainly look into why users are reverting records.

Doesn't entering the numbers in afterwards defeat the purpose of auditing?

[Scott Pon]

You have a setting on the auto-entry options for a serial number, to generate the number "on creation" or "on commit". The default setting (and the only one I've ever used) is "on creation".

If this is the setting and a user Reverts a record before the record is committed, that serial number will "be lost".

I find users rarely intentionally revert a record without prompting, but often revert records because of a failed validation and the option is presented to them.

I am aware of the Revert feature. Yes, you describe what is happening. However, I have watched people using who create the new Sales Orders. I have never seen them use the Revert feature. I will double check though.

Somewhere there is a way to revert, without using the revert command.

[David Jondreau]

Somewhere there is a way to revert, without using the revert command.

Like a failed validation?

You've watched them create Sales orders and the numbers have skipped? How many numbers are we talking about?

Edited March 5, 2008 by Guest

[Scott Pon]

Like a failed validation?

YES! I do know they have fields that are required so they MIGHT do the revert feature here...

You've watched them create Sales orders and the numbers have skipped? How many numbers are we talking about?

I have watched them enter in Sales Orders and it works properly. I have never seen them enter in Sales Orders and then have the sales order number go missing.

But I will check this Failed Validation that leads to reverting...

[mr_vodka]

I would check your scripts to see if any of them are setting this field to something.

[Scott Pon]

I would check your scripts to see if any of them are setting this field to something.

We have a script that creates a new record. In the new record we have a bunch of auto-entered data and a few required fields. I'm going to look into those.