Continuous numbering

[Creek]

I am looking for a hundred-per-cent-effective method how to number continuously (avoiding gaps and repetitions) subsequent records in multi-users environment?

I am sorry – I am not sure whether my question is clear or I use proper wording. So let me use example to show my problem.

Suppose, we have a database of invoices in the form of a table. Each record in the table represents one invoice. Many users have access to the database at the same time and can create new records (register invoices). Each record must be given a unique ordinal number. The number should be given at the moment of saveing (entering) record, not at the beginning of it's creation – thus I don't use the Serial Number.

Now imagine that a user creates a new record and suddenly abandon the task – his record, as non-existent will be not given a number. And there is another user who two second later start the similar task and achieves it successfully. His record will receive number.

Let imagine now that both users will finish their registering task in the same second. And the will register their entries in the very same moment. One of them must be given the priority by the program – entries must be queued by the program. How the system will work out such a decision?

I am sure that this is a well-known problem. And I think that it must be a well-known solution for it. Unfortunately I do not know how to do it in FileMaker.

[mr_vodka]

Did you try the generate on commit option under the serial auto enter options?

[Creek]

Creating a serial number after the commit is a good method but rather not for me.

Why? - More details:

Serial number is just a number. You can not specify the structure of this number. In brief, the typical structure of the invoice number YYMMXXX (YYear + MMonth + XXX that stands for number of invoice issued in current month.)

To create such a number some calculation must be made.

I can't find how to insert the result of the calculation in the serial number.

[Vaughan]

I am looking for a hundred-per-cent-effective method how to number continuously (avoiding gaps and repetitions) subsequent records in multi-users environment?

I am sorry – I am not sure whether my question is clear or I use proper wording. So let me use example to show my problem.

Suppose, we have a database of invoices in the form of a table. Each record in the table represents one invoice. Many users have access to the database at the same time and can create new records (register invoices). Each record must be given a unique ordinal number. The number should be given at the moment of saveing (entering) record, not at the beginning of it's creation – thus I don't use the Serial Number.

Now imagine that a user creates a new record and suddenly abandon the task – his record, as non-existent will be not given a number. And there is another user who two second later start the similar task and achieves it successfully. His record will receive number.

Let imagine now that both users will finish their registering task in the same second. And the will register their entries in the very same moment. One of them must be given the priority by the program – entries must be queued by the program. How the system will work out such a decision?

I am sure that this is a well-known problem. And I think that it must be a well-known solution for it. Unfortunately I do not know how to do it in FileMaker.

Use an auto-entered serial number and commit on record creation.

Note that "to number continuously (avoiding gaps and repetitions)" is a lot harder than you think, not from a development perspective but from a user perspective. Particularly when you also want them in order, which you haven't explicitly stated.

Once FMP uses a serial number IT IS GONE FOREVER so be sure to only let users create new records when they actually need to, and prevent them from deleting records so that gaps do not appear.

The problem with issuing the serial number on record commit is that it is possible in a multi-user environment to get the serial numbers out of order. User1 makes a new record and leaves it open. User 2 creates a new record and saves it immediately, gets ID=11. User 1 now commits their record and gets ID=12. The record are numbered out of order: 10, 12, 11, 13 etc.

Do NOT use an invoice numbering system that encodes the year or other information into it. It is a disaster. January 1 comes around and the database invoice number gets rolled over. Come January 2 and people need to back-enter invoices from last year. Fail.

[LaRetta]

Serial number is just a number. You can not specify the structure of this number. In brief, the typical structure of the invoice number YYMMXXX (YYear + MMonth + XXX that stands for number of invoice issued in current month.)

Vaughan is correct - primary keys should be straight serial number and meaningless. If a primary key has meaning and the meaning changes, you have just broken your relationships. You can use a calculation, a merge field or merge variable to display what you want humans to see which would be concatenation of what you think gives meaning to that invoice. I understand the request but the keys are not where to do it.

Don't think for a moment that your bookkeeper won't come to you and say, "Well, this was mistyped and that invoice must be posted in last month instead of this month. Please change the InvoiceID from 1204136 to 1203699". If you change it now, your current month will have a hole where the 136 sat. But even worse - it means that those lineitems are now orphans. And probably other tables now have orphans as well, such as the Shipping Module and the Sales Commissions where this primary entity (Invoices is one of the big five) has its primary key residing as a foreign key. You might not catch them all particularly since Users are merrily typing away and creating more records ...

I strongly suggest that you disallow meaning in the keys which bind your relationships together. :)

[Creek]

Thank you for your answers. I fully agree with you both – Vaughan and LaRetta. However I would like to ensure me to be understood well.

To this end let mi describe the problem again – this time it's whole, not the fragment.

First: I do not confuse invoice number with the ID (primary key). I am fully aware that they are quite different values.

The problem starts with the Polish system and regulations concerning the invoices issuing by (small) companies. For our discussion the crucial are the date of issue and the number of the invoice.

This is a typical structure of the invoice number:

TYYMMXXX

Where:

T – stands for Type of invoice. Ex. U – invoice for services; Z – prepayment invoice; K – corrective invoice; PF – fictitious (in Polish: Pro Forma). Every type has its own sequence of numbers.

Y – year

M – month

X – current number

During the whole account period (month) invoice numbers must be continuous (there must not be gaps). Only in the day-period the sequence of numbers could be transposed.

Sample of invoices numbered during one day.

U1205001

U1205003 (example of transposition in sequence)

U1205002

Z1205001

U1205004

U1205005

Z1205002

K1205001

According to the Polish accountancy law no invoice issued could be deleted (withdrawn). There is one exception – the so called Pro Forma (fictitious) Invoice could be both deleted or modified, but according to the law PF is not an accounting document. That's why it became a common practice to create a PF Invoice and then replace it by a “true” invoice. Otherwise if one make a mistake (of any kind) in the invoice a corrective invoice must be issued and special statement is issued and submitted to the Tax Office.

There is one additional difficulty. Users should have a possibility to issue invoices during some first days (usually up to 5) of the month dated on the very last day of the previous month.

Example: W are May 01. We issue several subsequent invoices:

For May – number U1205001

For April – U1204301 (suppose till the end of April we have issued three hundred invoices)

For May – U1205002

For April – U1204302

For April – U1204303

For April – U1204304

For May – U1205003

After all balance sheets will be filled, closed and submitted to the Tax Office, no changes or amendments will be allowed.

Thus let me ask my question again: do you have any idea how to create such a numbering functionality in File Maker?

[Vaughan]

The big question is: can the invoices be non-sequential in the database?

[Creek]

The big question is: can the invoices be non-sequential in the database?

This is a big question, indeed.

From the point of view of users: the sequence of records (invoices) in the database does not matter. So the ID (primary key) hasn't to be sequential.

For the user ONLY the invoice number counts. As I've mentioned earlier the numbers of invoices (not the records) must be sequential and meet requirements (as explained in my previous post).

[RodSierra]

We do this by posting the transactions via a server side script. The user posts the document, it is then queued for server side action. Once posted the user has no further edit access to the document. A server side script then does the formal posting, and thus has sole control over issuing formal document reference numbers.

[cereal_killer]

I think that a layout containing only global fields allows the user to input data

and then, with the corresponding tecnique, can choose to to create, modify or cancel it.

If he chooses to create or modify it, the global info replaces the actual info. Until then,

the serial number will be affected, because that is the moment when the New Record step is

executed.