Jump to content
Sign in to follow this  
CMI

Creating a common pool of serial numbers - how?

Recommended Posts

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. smile.gif

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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. smile.gif

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By Tumma K
      Hello, All!

      I am an aspiring developer for Filemaker. The company I work with is stuck in the past working off of Filemaker Pro 4.1

      I was given the task of bringing us up to Filemaker Pro/Server 13. So far my conversion prototypes are successful but we recently had a layout issue that can only be fixed in versions 3-6 (as the file is an .fp3) I work off of a macbook while our network is all Windows 7. In order for me to repair the layouts without tampering our active database, I decided the best option is to repair a copy of our solutions off the network. Unfortunately, when I go to download the trial version of Filemaker Pro 6 off of the respected website, the file is corrupt! I've tried multiple times, with different extraction apps and in different directories.

      My question is;

      Does anyone know a place where I could obtain version 6 (or better yet, 4.0) for an OSX computer? I've looked everywhere!
       
      Thank you for your time,
      Tumma K.
    • By MrEddByrnes
      I'm hoping my question can have a happy ending. In the mid-90's, I purchased Filemaker 3. When Filemaker 5.5 Pro was released, I bought the update CD, which requires the user to either have FM 3 installed or to have the installation CD for FM 3. I've used it all these years, most recently with Windows XP Pro, and it has worked just fine. The databases I began with were long ago converted to FM Pro 5.5 databases.
       
      I'm still using FM Pro 5.5 on a laptop with WinXP Pro, but in 2013, I purchased a PC with Windows 8. I haven't been able to install FM 3 on it, therefore can't install FM Pro 5.5. I am retired and rarely use Filemaker, but I have a few Filemaker databases I'd like to add to my Win 8 machine. I don't feel it's worth upgrading FM for the sake of using a couple of databases.
       
      Has anyone else run into this situation and/or have a (possible) solution? Is there perhaps any other software that can read FM 5.5 databases? Thanks in advance for your help.
       
    • By bmill
      I am using a custom filemaker solution for medical office billing written with fp5 running on a mac with snow leopard. In addition, I have a patient management db (which I wrote) that is linked through pt. ID number to the billing program allowing transfer of some demographic information (name, DOB, etc).
       
      Other than being limited by hardware restrictions, the billing program serves our needs for now and upgrading to fp12 will take some time (and money).  In the meantime, I am upgrading my pt. management program to fp13 and would like to move new patient demographic information from the billing program ( fp5 running on snow leopard through Parallels) and the new pt management program ( fp13 running on OS X 10.9) on the same mac.   
       
      Ideally, demographic information would be entered once into fp5 and then a scipt would make the data available for fp13.
       
      Any ideas on how to make this work?
    • By randyinla
      Hi, can anyone tell me why my on-line database might have stopped allowing me to delete records?  All of my access privileges and passwords are correct.
       
      thanks!
    • By randyinla
      Hi, I still use filemaker 5.5 and am very happy with it and don't want to upgrade.  Does anyone still host filemaker 5.5?  Thanks!
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.