3 Replies Latest reply on Dec 21, 2012 11:04 AM by philmodjunk

    Purchase Orders and Serial Numbers

    okachick@hotmail.com

      Title

      Purchase Orders and Serial Numbers

      Post

           I am making a database that will generate Purchase Order numbers (Serial, incretment 1). However, what I would like to do is I have only 4 people in the company that can give out a Purchase number. Ex: John, Jill, Brian, Tim. Each of these people I would like to have only a set number of purchase orders they can give out . Ex: John from 1000 to 1999. Brian 2000 to 2999, Jill 3000 to 3999 and Tim 4000 to 4999. I would like that when they put in their userID that it generates the next number within there allotted numbers. Would this be a script, calculation or two different tables connected? I am using the new FMP 12

           Thank you

        • 1. Re: Purchase Orders and Serial Numbers
          philmodjunk

               Why would you want to restrict the number ranges--which greatly complicates the design of your database, to specific users? Not only will you need to put in design effort to assign the numbers like this, you have a build in upper limit, what do you do with your number series after a user has created 999 purchase orders?

               If you need to identify the user that created that purchase order record, simply set up the purchase order record to auto-enter information in a field that identifies them as the person that created that record. This can be the user's account name, or you can use the user's account name to look up a name or ID number from a table where you log acount names and this additional info.

          • 2. Re: Purchase Orders and Serial Numbers
            okachick@hotmail.com

                 Yes this is what I suggested, however, some people seem to think that something more complex is better. I'll just do it as I had originally suggested. Thanks for the response. wink

            • 3. Re: Purchase Orders and Serial Numbers
              philmodjunk

                   Don't get me wrong. You CAN do this, I just recommend against it if you can possibly avoid doing so.

                   It's a pretty common issue that database developers have to deal with from time to time. Your client/boss/manager wants to take an ID number and "encode" hidden information that knowledgeable people can then decode when they see the ID number. This might be a date, it might identify a particular department, or as is specified here, a particular person. While you can't always convince the powers that be of this, such "encoding" just isn't very often necessary with modern relational databases. The system can easily be set up to display the same information in plain English via relationships so that no "secret decoder ring" is needed to access that information. (And yes, such encoding IS still needed for cases such as a shipping label where space is extremely limited or you don't want the info to be obvious to the uninformed.)

                   If you find that you have to encode "meaning" into an ID number or string, make sure NOT to use this field as your primary key. (A primary key uniquely identifies each record in your database and will be the most frequently used match field in relationships.) Use either and auto-entered serial number or the UUID (FileMaker 12 only) as your primary key, but put the user demanded ID in a separate field in the same record. This allows you to print the information on labels and reports and to use it for searching and sorting records, but if--as is all too often the case--the powers that be require a change in the content or format of this ID field at some future date, you can make that change without having to also make identical updates to records in related tables in order to keep them linked to the correct records.