4 Replies Latest reply on Dec 23, 2009 5:32 AM by comment_1

    Different Sequential Numbers in same Database



      Different Sequential Numbers in same Database




      I am setting up a database for my distributors in Filemaker 9 which allows them to create their own quotes. However the problem is I want them to have their own sequential numbers so they cannot see how many other quotations or orders are being placed by other distributors

      So if I have three distributors called Jack, Jill and Pail I want Jack to create a quote and it to be JackQuote1, he then creates another quote and it is called JackQuote2 - which is easy to do as the database allows for this as standard. However when Jill creates a quote, on the same database, I want this to have the numbers JillQuote1, 2 and so on.

      Is there a way I can do this?

        • 1. Re: Different Sequential Numbers in same Database

          Each Quote must have a unique ID number.

          This number does not need to visible to users.

          There could be another quote ID number that is visible to users.


          • 2. Re: Different Sequential Numbers in same Database

            Hi David,


            It is the one that is visible to others that I am having trouble with as this will be the one that Jack, Jill and Pail will reference to me when discussing the quotes. I do not want each of them to know how many quotes are on the system. It would be easy to set a field whereby the CreatorName of the quote was added to the next sequential number, but if Jack had the first two quotes on the database they would be JackQuote1 and JackQuote2 but if Jill had the next quote she would have JillQuote3, which to Jill would not mean anything.


            I would like to be able to add the CreatorName to a sequential number relevant to that CreatorName, regardless how many other quotes are on the database.



            • 3. Re: Different Sequential Numbers in same Database

              I'm no expert on this aspect, but it seems that by using Accounts and Privileges properly Jill would never see any records created by Jack and vice-versa. The UserName concatenated with a serial value would work. However, should Jill delete "JillQuote4" I believe the next quote created would be 5 so the serials won't always be consecutive if they're created automatically. The name could be created OnRecordCommit with a script as well. Under no circumstances should this field be a match field. As David said, the serial used as the match field needn't (shouldn't) be visible to the user.



              • 4. Re: Different Sequential Numbers in same Database

                In general, it is very difficult to maintain several indepedent series in the same table and to do so reliably. However, this case seems to be an exception, because the two main dangers do not apply here, namely:

                • two users cannot be creating a new record in the same series at the same time;
                • a record cannot be re-assigned to another series.

                So it would seem quite safe to:

                1. Define a field AccountName as Text, Auto-enter Creation Account Name;

                2. Define a self-join relationship matching on AccountName;

                3. Define a field PrivateSerialID as Number, Auto-enter Calculation, Evaluate Always =

                Max ( Quotes 2:: PrivateSerialID ) + 1

                Be warned, however: just because I cannot foresee any danger of getting a duplicate with this method, it doesn't necessarily follow that it doesn't exist - it could be just a limitation of my predicting abilities. In any case, you should maintain a common QuoteID serial number and use it exclusively for relationships and such.