On 21 Dec 2011, at 20:41, Peter Buchanan wrote:

> Users have requested that transport documents (created in FileMaker) should be numbered in a particular way, eg origin port + destination port + unique serial number, so it could look like FEL/BAR/0001.
>  
> On each document thereafter, where BAR is the destination port, the number portion would increase by one eg, FEL/BAR/0002 etc.
>  
> So each destination port would have to have its own sequential number allocation – not sure how to do this,  additional/new destination ports are added all the time, so it would have to cope with that.
>  
> Also, users have (against my advice...) insisted that when a document is cancelled, the number should be released for use again, and it must be used on the very next shipment to that port. So if 0019 was cancelled and 00030 had was the most recent document, then the next document would get 0019, and not 0031.
>  
> They are presently allocating numbers manually, and looking to me for a solution to their suffering.

Forgetting for a moment (for the sake of simplicity) that cancelled numbers are to be reused, here's one way to look at this.

You need a destination port table with one record per port. Among the fields in this table are destination_code (eg BAR) and next_serial_number. I'd suggest also having a separate unique identifier/key for these ports – unless you can absolutely guarantee that your three-letter port abbreviation is unique.

You create some kind of 'picker' system to allow users to choose from the various destination ports. It must be scripted. If there are many possible destinations, my choice would be some kind of filtered portal as a picker. If there are few, it might involve a popup menu.

Anyway, when the user picks a destination port by whatever method you implement, the destination_code and next_serial_number are brought into separate fields in your transport document record (think, perhaps, of using variables for this). At the same time, next_serial_number is incremented by one. The final transport document field is a calculation, using these two fields and another one where the user enters the origin code.

The only gotcha with this kind of system is a multi-user situation. If the file is hosted and used by more than one user concurrently, you have to be extremely careful with your routine for incrementing next_serial_number. You might look around for old posts on this or other mailing lists relating to serial number incrementing for the low-down. Or if you get stuck, ask again and I might be able to find some of these old ideas. (They generally date back to the days prior to us having script control over Filemaker's built-in serial numbering scheme.)

Finally, if you cannot persuade your users to give up the dream of re-using cancelled numbers, you've got more of a programming task on your hands. The basic idea is the same – you'll need a table of destinations. But you'll probably also need an additional table of used serial numbers (the fields would, at minimum, be a used/cancelled flag field, destination_code and serial_number). One possibility is each time a number is used, you create a record in this table, populate it with the serial number and destination code, and mark it as used. If the corresponding transport document record is cancelled, then you change the flag to cancelled.

Now, when creating a new transport document record, once the user has selected the destination the script should check this table for any records matching the destination. If one or more exists, it will take the serial number from the earlier one and mark it once again as used. On the other hand, if there are none, the script will go through the above routine of incrementing the serial number.

I'm sure there are many ways of dealing with this situation, and there may be more modern methods of dealing with the multi-user problem too. But this might get the creative juices flowing.

As for the re-using of serial numbers – I've always managed to find ways to avoid this. I've never found it to be a strict business rule; in fact, often it turns out that it's a bad thing to do. (Like deleting cancelled invoices and the like; way better to mark them as deleted and create a correctional record.)

HTH a little.

Steve