Creating a common pool of serial numbers - how?

[CMI]

Hello,

I need a bit of help with a problem. My company enters dispatch orders into an FM 5.5/6 database. Each time a new record is created, the next available 'Pro' number is automatically input to the PRO field. It's just an auto-entered serial number, obviously.

Now, we have branched out to remote offices from 3 additional cities, all tied into our FM server, using the same database. At management's discretion, each city will soon begin running its own stand-alone clone of this database, but still hosted by our server.

In a nutshell, here is my dilemma: The PRO numbers have to come from the same pool. How can I create a common serial number, incremented by 1, that each city can use?

Example: Houston creates a new record and the pro number is 5000, the next available number for any of the databases should be 5001. If New Orleans creates a record and uses 5001, 5002 should be the next number available for any of the 4 databases. There should never be a duplicate PRO number used in any of the databases.

I hope that I am presenting this clearly. Any help will be greatly appreciated. My database experience is pretty darn shaky as it is.

[Ken Newell]

Let me see if I understand. . . different files for each city but all will be hosted on your server? Why does management want to do that? What happens when a change is to be made to the DB; you are going to have to change it in the cloned copy for each city?

[CMI]

The reason for the separation is that management does not want either location to have access to all of their 'business'. It's a client/agent thing.

None of the data is to be shared. They are simply standalone dispatch databases. The only thing common to them is that they pull from a common set of incremental serial numbers. They do not want duplicates in these numbers. They simply want a common pool that each city will pull the next available 'PRO' number from.

To clarify further, think of the pro number as an invoice number. They simply want to number invoices, incrementally by 1, and have the next available number assigned to the next new record request, regardless of which database requests it. And there should be no duplication between numbers. Once a number is assigned, it's gone.

Hope this clarifies.

[QuinTech]

Suggestion:

Say Houston = 0000000, New Orleans = 1000000 , Pittsburgh = 2000000 , Des Moines = 3000000 , ...

Make one solution that is shared by every city (i know you said you can't do that for security, but bear with me). This solution has a standard serial number, plus a calc number field = ( CityCode + SerialNum ). The first record is created in Pittsburgh and has a modified serial number of 2000001, the second record is created in Houston and has a modified serial number of 0000002, ...

Now give each city a password and restrict access so that they can only see their city's records: Pittsburgh's password is 'Stillers' and can only Browse/Edit/Delete records where ( ModSerialNumber > 2000000 and ModSerialNumber < 3000000 ).

If having incremental serial numbers is really important, you could go one better and calculate the ModSerialNumber from the max of the city range you want. That's a bit complicated, but can definitely be done.

Jerry

[CMI]

Thanks for the suggestion. I actually had considered a similar scenario and it was rejected outright by the powers that be. They really want the main database for each city to remain separate.

The problem with the solution you suggest is that the numbers really do have to remain incremental, from the same set. The company is a transport dispatch company and it is important to remain within our 'set' of numbers to avoid potential conflicts with other similar outfits.

Thank you again for your reply.

[Fenton]

You need a single-record file dedicated to producing serial numbers. It's a somewhat dangerous business. Because, unless you lock the record properly it would be possible for 2 people to hit it at the exact same time, and get the same serial number.

Here's some advice from Eric Scheid, on how to lock that record, in v.6:

---------------------------------------------------------------

The script goes to a layout which contains the field in question, then does a Go To Field [thefield] .. at this point the record is locked from changes by anyone else. This also means that no one else is changing the value while you look at it, so you rely on it being the current value and also do stuff to it. If on the other hand some other user is currently editing that record then you don't get in, you get an error, and thus you know you should wait for the other user to finish .. use a loop to wait.

Go To Layout [contains serial field]

Set Error Capture [On]

Loop

[CMI]

Thank you! I will use this and do my best to adapt it to our system.

I was thinking I would need a separate file to 'generate' the serial numbers, but I wasn't sure on how to push/pull the changes back and forth.

Thank you for taking the time to help me with this dilemma. Thanks to all for the advice.