On 22 Dec 2011, at 05:29, Peter Buchanan wrote:

> So any further ideas on the gaps?

Well, as I suggested earlier, you've got to keep a record of which numbers in each sequence have been used (and/or which have not been used). Again as suggested, this would be best done in a separate table. The table would, at least, need fields to identify the port, the serial number, and a used/unused flag.

You could populate the table in advance – fill it with hundreds or thousands of future serial numbers. When a new transport document is created, it takes the lowest available number. When a number is taken, it is marked as used. If the transport document is marked as cancelled, the number is freed up again. Disadvantage: you will have a mess if the table runs out of records; but this could be worked around somehow.

Or you could populate the table only when a transport document is created, marking the new record as used. If the transport document is cancelled, the record is freed up. The transport document creation script must look in this table first to see if there is a freed up number to use. Disadvantage: scripting is probably more complex.

There are many ways to skin this cat (or to place this cup in the cupboard). As to details of actual scripting – well there are even more variants on that. You are going to have to work out what method suits you and your situation best, then come up with a basic approach before honing the details.

I would repeat though that if you have multiple users, any selected method is likely to need careful consideration of the possibility of duplication. This is something that we don't normally have to deal with in Filemaker work, but creating sequences of unique numbers like this one area where you have to face it. At least, I think that's true – it is possible that with new tools in recent versions of Filemaker the task is easier or clearer. (I haven't done anything like this for some years at least.)

HTH

Steve