13 Replies Latest reply on Jan 30, 2012 7:09 AM by LauraZ.

    Serial number table - or field?

    LauraZ.

      Hello,

       

      My FMServer served multi-user database has a need for generating a new unique serial number for the user to enter within the confines of a certain filtered part of the data. The numbers can be reused eventually, just in different subsets of the database. To be clear, the number needs to be unique within entries of each term and year pair only, and the entry of those numbers will be within a filtered group, unrelated to any others. My thought is to let the serial number get up to 999 and then revert to 1.

       

      The standard form appears to be: create preferences table + auto entered serial number field. Script creates new record (locking record by Corn's suggestion in another post using Open Record[ ] and checking for any errors - thank you Corn) and collecting the serial number to a variable > continue.

       

      Then, I wondered why do I need a whole table and 999 records? In inventory solutions and transactional solutions, numbers sit in fields and you pick them up, add one and set the field with the addition. Maybe that would be fine? Why not?

       

      Or, would there be any advantage to having my one record utility table have an auto enter serial number field that actually never does anything, but holds the next serial number for me to collect and then I set next serial number to the collected number plus one?

       

      What I don't like is having to go to a layout to collect it, but the global field needs attention everytime I pull it down for development and put it back up. Maybe my one record utility table record can be accessed like a global because the first record is the only record, and that could show up in all relationships to it.

       

      Any thoughts on the best way or why one way is the wrong way?

       

      Thank you.

      LauraZ.

        • 1. Re: Serial number table - or field?
          comment

          One cannot help wondering why wouldn't you simply use an auto-entered serial number field in the table where it's needed - instead of creating it elsewhere and fetching it from there.

           

          I don't know that there is a "standard form" for such prodedure, but in cases where only some records need a serial number (for example, quotes turning into invoices) I have used a "numerator" table. Creating a new number is very easy by:

           

          Set Field [ Numerator::QuoteID ; Quotes::QuoteID ]

           

          This either creates a new record in the Numerator table (and the auto-entered serial number in that table is the InvoiceID) or does nothing (if called from a record that already has an InvoiceID). I see no reason for either locking a record or switching layouts.

           

           

          The problem with incrementing a single value - whether in a global, or in a one-record table - is dealing with two users requesting a number at the same time. The first user must lock the record, and you must provide the second user with a mechanism to come back and try again.

          • 2. Re: Serial number table - or field?
            RayCologon

            LauraZ. wrote:

            What I don't like is having to go to a layout to collect it...

             

            Hi Laura,

             

            You don't need to visit the utility table's layout to collect the serial number - you can do it remotely.

             

            The GetNextSerialValue( ) function will retrieve the serial value for you from any context, without being either on a layout based on the Utililty table, or even on a layout based on a table occurence with a relationship to the Utility table - literally from anywhere.

             

            Similarly, the Set Next Serial Value [ ] script command will increment the serial number in the field in your Utility table from any context - also without requiring a relationship.

             

            What *will* be essential is that you establish a queue mechanism to ensure that two users don't grab the same serial at around the same time. The method Corn suggested is certainly one of the options, but not the only way. If you want to use the Utility table as the gatekeeper for the queue, you will need a relationship to it for that purpose, so that your script can attempt to open the Utility record remotely by writing to it (if the write succeeds the record was available and you can proceed, if the write fails then it is locked by another user and your script will need to loop and try again.

             

            In essence, the principle is similar to the one you're familiar with, but it will be quicker, cleaner and neater than navigating to a different layout for this purpose.

             

            Regards,

            Ray

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

            R J Cologon, Ph.D.

            FileMaker Certified Developer

            Author, FileMaker Pro 10 Bible

            NightWing Enterprises, Melbourne, Australia

            http://www.nightwingenterprises.com

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

            • 3. Re: Serial number table - or field?
              LauraZ.

              Michael,

               

              Thank you for your response.

               

              In answer to your question, this serial number is only serial so that it is unique for the group of records it is used for. The use is to tie sub-groups of records together. It acts like a ticket spit out at the deli line. Then, the user applies it to a few records that need to be found again later as a sub-group. There is no relation at all between the table creating the number and where it is applied.

               

              Thanks again,

              LauraZ.

              • 4. Re: Serial number table - or field?
                comment

                LauraZ. wrote:

                 

                this serial number is only serial so that it is unique for the group of records it is used for. The use is to tie sub-groups of records together. It acts like a ticket spit out at the deli line.

                 

                Can you elaborate on this a bit more? I guess I don't get to hang out at the right places, because 'a ticket spit out at the deli line' doesn't bring an image to my mind...

                 

                If a number is a unique identifier for a group of records - where will it be stored?

                • 5. Re: Serial number table - or field?
                  LauraZ.

                  Ok Michael,

                   

                  This solution is a hands-on type by client's request. There are people requesting to be enrolled together in a class of a certain term and year (Spring and 2012). In order to find people who want this service, a "group_id" number is entered with their requests. If 3 people want to be in the same class, they get the same number assigned to each of their class requests. Another 2 want to be together, same class or different, they get a different number, assigned to each of the two. When it comes time to see if their requests can be accomodated for that term/year, the requests with a number in the field are found, and sorted so that the ones with the same numbers are together. Then, the users make decisions about whether the group requests can be fit into the overall registrations. The numbers are just unique for that purpose, that term/year. The next term/year, the group number finds will be also restricted to the next term/year, so it doesn't really matter if the numbers are the same as some past term/year group numbers.

                   

                  All I am looking for is a number creating function that will produce a unique number on request. That number is then used in a non-unique way.

                   

                  I hope that helps to explain what I am trying to do.

                  • 6. Re: Serial number table - or field?
                    comment

                    Perhaps I still don't see the entire picture, but it seems to me that the proper way to track these groups would be by entering them as records in a table of Groups, where GroupID would be an auto-entered serial, so that:

                     

                    Groups::GroupID = Requests::GroupID

                     

                    I don't see why the GroupID couldn't be unique across all terms/years; there are enough numbers to go around.

                    • 7. Re: Serial number table - or field?
                      LauraZ.

                      Ok Michael,

                       

                      I understand why you might say that, but it is, as I said, a very hands on part of the system, with two steps: a step to add requests and a step where the users look at the whole picture and make decisions. They like to see it all in one place, and these grouped records are really a tiny part of the picture. We have to see these requests as potential grouping only.

                       

                      Regarding the reuse of numbers, you are right, it wouldn't really matter at all if they were all unique and went off into infinity, as far as the numbers themselves go. However, again, this is users manually moving numbers around, and It appears to me easier for the users to manage a 3 digit number than larger numbers. In fact, the numbers could even be letters instead, but letters just seem to add their own dimension, ie. Is Group A superior in some way to Group D?

                       

                      Anyway, that is the way we have it, and the client has been happy using the system locally in older versions of FMP for 2 years. The reworking arises from the solution being moved to FMServer, and the global fields that were in place are not going to work the same way in the new serving environment.

                       

                      Thank you for your suggestion though.

                       

                      Cheers,

                      Laura

                      • 8. Re: Serial number table - or field?
                        comment

                        I don't think the way your users interface with the process is relevant to the question of how the group number is generated. You say it used to work by step-incrementing a global field; that obviously won't work in a shared environment. The closest equivalent, IMHO, would be a non-global field in a one-record table. However - as both Ray and I mentioned - you also need to prevent a potential conflict between two users, so this is not just a matter of changing the field's storage type and leaving everything else the way it used to be. Personally, I would generate a new record for each new group, because it's the simplest approach and I don't see why it should cause any concern. On the contrary, I would be more concerned about running a process that leaves no trail*.

                         

                        BTW, you can use only the last three digits of the generated number, if you prefer, or convert it to letters or a combination of letters and digits - again, that's something that does not depend on or influence the question at hand.

                         

                         

                        ----

                        (*) Speaking of trail:

                         

                        LauraZ. wrote:

                         

                        In inventory solutions and transactional solutions, numbers sit in fields and you pick them up, add one and set the field with the addition.

                         

                        No, I would never do that.

                        1 of 1 people found this helpful
                        • 9. Re: Serial number table - or field?
                          LauraZ.

                          Hi Michael,

                           

                          I am coming around to at least the part where there are plenty of numbers to go around. I am ditching the 999 return to 1 idea, though they are moving through the numbers fast and I don't like the idea of them copying and pasting large numbers. As for the GroupID table, I have done that in the past happily, but just don't see it here -- yet. I will continue to keep that in mind, in case more evidence mounts for it. I am just trying to keep the whole production minimal on this part, as the group table approach would have to encompass the term/year filter, and individual_id/course_id/group_id in a new layout view, and probably including selecting from a list of data that may not be entered at that point. It is a pretty smooth motion as it is, regardless of my 'big number' concerns, and, as I mentioned, a tiny part of the what they are doing.

                           

                          Yes, I am agreeing with your opinion that  "a non-global field in a one-record table" works well as the replacement for the global field. I have now tested a script with error trapping, while leaving an edited number and cursor in the test field in a second window of the solution while running the script with success (Error 301) and graceful looping and dialog if no change. I expect to test it again on my server tomorrow. I used the suggestion I started with (however vaguely) and that Ray took up, where the single record utility table has an auto-enter serial number field. The script first attempts to write to that field, and if successful, takes the next serial number to the variable. If not, it loops 10 times and if still no go, it tells the user someone else is using the feature and try again later.

                           

                          As for a trail, in this case, the trail is with the request records. At any time, in list view and sorted, they can all be seen progressing. Maybe that is not enough trail in some circumstances, but all we really need is for the fields to hold the numbers and a true find result on those numbers. I don't see the client ever revisiting that moment, because the moment cannot be reproduced without starting all over. This moment is a judgement call and gone. This bit is really in the middle of several larger data manipulations, a pause for adjustments and human interaction, then a lottery is run to finalize the rest.

                           

                          And while I can see the fear in the your asterix, please note that a transactional paradigm was mentioned with the inventory solution, and that was meant to imply an audit trail.

                           

                          Thank you again for your helpful reflections!

                           

                          LauraZ.

                          • 10. Re: Serial number table - or field?
                            LauraZ.

                            Hi Michael,

                             

                            I think I didn't answer this concern effectively:

                             

                            You Wrote:

                            If a number is a unique identifier for a group of records - where will it be stored?

                             

                             

                            Right - the group id is attached to each of the requests that need them. That is where they are stored, and can be viewed, with all the requests in a list.

                             

                            A person may have up to say 10 course requests for a given term/year. One of the requests that a person has may be a course that a group of friends is trying to attend together. The group id is attached to that course's request for each individual. Six may want to get in, two might actually get in, based on many weighted factors. After that, the numbers will never have meaning again. The personal grouping may be noted in notes, but not any number. The records are viewed on a course by course and individual by individual basis.

                             

                            Most importantly, there will never be a reason to view these records as Groups, so a Groups table would have no purpose, in this case.

                             

                            Regards,

                            Laura

                            • 11. Re: Serial number table - or field?
                              RayCologon

                              LauraZ. wrote:

                              Thank you Ray, for your response.

                              I like your suggestion. I assume the relationship should just be maybe two "constant" fields?

                              Thanks again,

                              Laura

                               

                               

                              Hi Laura,

                               

                              Good question. For this purpose, you don't really need a dedicated key field for either side of the relationship. If you define the relationship using the Cartesian Product ("X") operator, you can use any field in either table.

                               

                              I'd suggest using the existing primary key field in the data table, and the serial field in the Utility table, so - no need to create any additional fields, and no need for 'constants'.

                               

                              Regards,

                              Ray

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

                              R J Cologon, Ph.D.

                              FileMaker Certified Developer

                              Author, FileMaker Pro 10 Bible

                              NightWing Enterprises, Melbourne, Australia

                              http://www.nightwingenterprises.com

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

                              1 of 1 people found this helpful
                              • 12. Re: Serial number table - or field?
                                comment

                                LauraZ. wrote:

                                 

                                the group table approach would have to encompass the term/year filter, and individual_id/course_id/group_id in a new layout view, and probably including selecting from a list of data that may not be entered at that point.

                                 

                                Actually, the Groups table would need a GroupID field (auto-entered serial) and nothing else (unless you want other stuff there). But it's not my place to try and convince you - I believe by now you have enough information about the pros and cons of each approach to make your own decision.

                                • 13. Re: Serial number table - or field?
                                  LauraZ.

                                  Yes Michael, thank you.

                                   

                                  What I started out wondering was about the pros and cons of having a table, like your group table, generating the ids, as opposed to another way of generating the ids. Obviously, in FileMaker, there are numerous ways to do things, and there is often no right and wrong way, but sometimes right-er and wrong-er ways. I have to let you know that I agree with your suggestion about the table. It is simple and elegant. However, in this case, I was on an exploratory mission to try something that would not increase what looks like is going to be a very large file. The numbers that are used are attributes more than foreign keys in practice. They might as well be text entries like "Horak Family", or "Madison High School", except that using text could produce unpredicatable find results.

                                   

                                  I really appreciate your help on this and other questions I have had. I know your time is valuable and that your advice is sage. I would welcome the opportunity to return the favor if such should arise.

                                   

                                  Best regards,

                                  Laura