1 2 3 Previous Next 30 Replies Latest reply on Mar 3, 2012 9:25 AM by nancyhorne@centurytel.net

    serial number range for multiple users

    nancyhorne@centurytel.net

      I've created a database for 5 users.

      While this has been in use for years, we're adding a different calculation for the serial number so everyone has their own number range.

      The serial is not the key field.

       

      I've defined a base number range per user log in account

      ab_10000

      cd_20000

      ef_30000

      gh_40000

      ij_50000

       

      When a new record is created, I'd like the serial number to reflect the person's log in account (for example-ab) and their last used number- maxSerial + 1 .

      I'm thinking the best way to track this is to create a table to calculate where the users are at and pull the correct field calc in to other table via set field?

      It seems simple but I can't think of the best way to set this up.

      Please help!

      Thanks in advance,

      Nancy

        • 1. Re: serial number range for multiple users
          comment

          IMHO, the best way is not to. Why would you even need such thing?

          • 2. Re: serial number range for multiple users
            ariley

            Just store the following in different fields: login account, time stamp and perhaps the Nic (Ethernet card address). That should be enough for identification.

             

            Sent from my iPad

            • 3. Re: serial number range for multiple users
              vidkid98

              Assume that the serial number is to be incremented each time the user logs in.

               

              1) A global field for each user, numeric; or one field for all that contains the next value to use for each user

              2) at login, a script is called that does the following:

              2a) fetch the user's serial number field

              2b) combine userID with serial number for whatever use you have planned as local var $userIDserial

              2c) increment the user's serial number field

              2d) apply the $userIDserial var to your planned use

               

              The only really tricky part is storing and fetching the user's serial number field.  If your user base is very static, hardly ever change users (add or remove), then I'd go with one global field for each user.  If, however, you need to be able to add users often, then you'll need a way to store the number for each user so that you can recall each without having to remember to add a new global field.  A single field that you parse the appropriate user out with a string function or two ought to do the trick.

              • 4. Re: serial number range for multiple users
                nancyhorne@centurytel.net

                Need - no. The client really WANTS this. We had a long discussion and talked about options- bottom line is each person having their own entries in their own sequential list based on the number is important and helps them analyze their data.

                • 5. Re: serial number range for multiple users
                  nancyhorne@centurytel.net

                  Thank you - I believe this is what I'm looking for, although I may have more questions. Variables aren't a strong skill for me yet.

                  This will work with all users logging in via remote log in?

                  Thanks again - will work on it this afternoon.

                  • 6. Re: serial number range for multiple users
                    vidkid98

                    I'm working up an example, but don't have time to finish before I go to my day job; will have it wrapped up tonight or tomorrow morning if you are still intereseted.

                    • 7. Re: serial number range for multiple users
                      comment

                      nancyhorne@centurytel.net wrote:

                       

                      Need - no. The client really WANTS this. We had a long discussion and talked about options- bottom line is each person having their own entries in their own sequential list based on the number is important and helps them analyze their data.

                       

                      The things is that this is rather difficult to set up reliably. OTOH, it is very easy to mark each record with the account name of the user that created it. It is also very easy to sort the records by their creator, and show them numbered sequentially, restarting the number for each group. The catch (or advantage, depending upon how you look at it) is that the numbers are generated ad hoc: deleting a record for example, or even omitting one, will cause subsequent records to renumber.

                       

                      What exactly do the records being numbered here represent in real life?

                      • 8. Re: serial number range for multiple users
                        nancyhorne@centurytel.net

                        yes - I am interested. That would be so helpful! Thanks!

                        • 9. Re: serial number range for multiple users
                          nancyhorne@centurytel.net

                          ah, this brings up an important point - The numbers - once set - CAN NOT CHANGE, unless changed manually via replace field contents, even if other records are deleted.

                          The number is used (but not linked to via FM) to reference video's and articles. Sometimes there are 4 numbers in a row that represent a 'series'. The numbers somewhat represent 'their spot in line'.

                          • 10. Re: serial number range for multiple users
                            comment

                            nancyhorne@centurytel.net wrote:

                             

                            The numbers - once set - CAN NOT CHANGE ... even if other records are deleted.

                             

                            Which means that in the event of a record being deleted, the numbers will no longer be contiguous. So why not simply use a common serial number in conjuction with account name, e.g.:

                            ab_10001

                            cd_10002

                            cd_10003

                            cd_10004

                            ab_10005

                            ef_10006

                            gh_10007

                            ij_10008

                            ...

                            • 11. Re: serial number range for multiple users
                              nancyhorne@centurytel.net

                              Because the client does not want this sequence! I agree with him after hashing this out for 20 minutes.

                               

                              It doesn't matter if a record is deleted and one or two numbers are missing. Records are very rarely deleted anyway.

                              If it's a 'set field'  with Acct & Max ((serial #) + 1) kind of thing it will be perfect.

                              • 12. Re: serial number range for multiple users
                                comment

                                The reason I am giving you such a hard time with this is that it's difficult to foresee all possible scenarios that will generate a duplicate - see:

                                https://fmdev.filemaker.com/thread/62671?tstart=0

                                 

                                In your case the chances are lowered by each user having their own series; so perhaps you could use the simpler method of auto-entering Max ( SelfJoin::Serial ) + 1.

                                 

                                Still, if you want to be safe, I'd suggest you keep the serials in a field of the Users table and restrict the creation of new records to scripted only; have your script (a) lock the user's record in the Users table, (b) increase the serial number by one, and (c) get the increased serial to the new record.

                                • 13. Re: serial number range for multiple users
                                  jason.delooze

                                  Nancy,

                                   

                                  One problem with using Max( User::SerialNumber ) + 1 type of unstored calculation is it gets slower as the number of records increase, since each record will need to be downloaded to the user in order to perform the Max() evaluation.  The remote user will see the greatest impact.

                                   

                                  I can think of 2 ways to handle this.  Both ways require a User (or Account) table to hold, for each User (or Account), the next Serial Number string to use.  I wonder, however, if 10,000 Serial Numbers per User will be enough over the lifetime of the database; perhaps you should set aside a larger range or think about how you will handle the situation when a User hits "ab19999" and needs another Serial Number.

                                   

                                  The 1st method is similar to the one proposed by vidkid98, in which an "On open" script loads a User's next serial number into a $$ (file) variable [$$UserSerial]; an "On Close" script writes the $$ variable back to the User/Account table when the file is closed; and the record's SerialNumber field is auto-entered via a calculation which (1) stores the $$UserSerial into the SerialNumber (by returning its value), and (2) increments the $$UserSerial to the next value.  This method will correctly fill the SerialNumber field for the User, no matter how the record is created.  One drawback is if the User loses connection with the FMS server before closing the file since the "On Close" script will not have executed.

                                   

                                  The 2nd method uses a script to create the new User records, using a script that fetches the SerialNumber to assign from the User/Account table, increments and saves the SerialNumber for the next new record, and locks the User/Account record before fetching, incrementing, updating to guarantee uniqueness (in case 2 Users are using the same Serial Sequence - not likely in your case since it appears you have a single User using each serial sequence).  Since the updated "current serial number" is immediately updated when used, a lost connection by the User will not cause problems (other than the usual problems when a lost connection happens).  But each record will need to be created via this script since the SerialNumber is generated via script.

                                   

                                  The 2nd method is probably what you are looking for.

                                   

                                  Jason

                                  • 14. Re: serial number range for multiple users
                                    vidkid98

                                    okay, I've posted an example on my documents folder here, multiple_serial.fp7 if I can figure out how to include a link to it for you, I will.  Otherwise, you'll have to navigate there yourself, it will be the only document there called multiple_serial.fp7.

                                    1 2 3 Previous Next