13 Replies Latest reply on Dec 6, 2011 10:30 AM by disabled_winfried

    Creating auto primary keys but with different prefixes to avoid duplicates

    amylawgill

      Hi

      I am using FileMaker pro 11 for creating my database to be used with FileMaker go. THis will be used for storing new customer details when out on location. I have created an auto field which is a customer ID primary key auto generated number. The problem lies with having myself and my colleague using the iPads and the same database on FileMaker go.

      When I am out on location and generate a new customer FileMaker will auto generate a customer number let's same customer ID number 22 Mrs Jones, but my colleague will also create a new record that will be a different customer which also would generate the next number in the Customer ID being number 22 this would then cause a problem when plugging the iPads back in at the office.

      I have thought of one way around this being an auto number customer ID which is unique and prefixed with the representative ID code such as ALW0022, ALG0022 (ALW0023, ALW0024, ALG0023, ALG0024) and so on. Therefore ensuring that each representative all have different prefixes. I am not sure how to do this auto generated primary key with a prefix.

      Or failing this I guess that I would need to have the iPads connected to a server, (again I am not sure how to do this, because I am knew to FileMaker) but what happens in the event that signal is a problem in some remote locations and the iPad A could still auto generate the Same number as IPad B

      Please advise...

      Thanks

      Amy

       

      Sent from my iPad

        • 1. Re: Creating auto primary keys but with different prefixes to avoid duplicates

          Ami,

           

          Please see < http://fmdiff.com/fm/serialnumber.html> for a viable solution for the problem you describe.

          Especially when you create records in the field, you will (statistically) never create the same record ID within the exact same second.

           

           

          Winfried

          • 2. Re: Creating auto primary keys but with different prefixes to avoid duplicates
            RayCologon

            Winfried Huslik wrote:

            Especially when you create records in the field, you will (statistically) never create the same record ID within the exact same second.

             

            Improbable, yes, but not impossible, Winfried - as we've discussed in the past.

             

            Not personally being comfortable with possible duplicates (though they may be relatively improbable) - and having seen a client system actually produce duplicates in a similar scenario - I took the trouble some years ago to develop and publish a system I consider more robust.

             

            If you're interested, you'll find the details (and a free demo download) here:

             

            http://www.nightwingenterprises.com/demos9/demo910.html

             

            The system encodes timestamp, recordID and the device NIC address into a compact string. It requires you to instal a custom function (provided), using FileMaker Pro Advanced, but after that it's very straightforward to use and works on all platforms, including FM Go. Additional (optional) custom functions are provided to extract the embedded data, if needed.

             

            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: Creating auto primary keys but with different prefixes to avoid duplicates
              jrenfrew

              There is some interestign discussion on the whole UUID thing here as there are some privacy issues in Europe with encoding the NIC and time as it makes it possible to identify the data source.

               

              http://filemakerstandards.org/display/bp/Key+values

              • 4. Re: Creating auto primary keys but with different prefixes to avoid duplicates
                DrewTenenholz

                Amy --

                 

                At our local user group here in Boston, Ernest Koe from the Proof Group gave a talk last month and strongly suggested (among many other things) that we all should be using UIDs or UUIDs or GUIDs in every table, all the time.  It's just becoming a design imperative.  It's not all that hard, either, and there are lots of benefits that come from having them.  One that doesn't is human-readability; it is a pain to try reading the keys and making sure the right records are linked in relationships with these.  You get over that, though.  He has some sample files that he wants to to crib from at (http://proofgroup.com/blog/2011/aug/devcon_2011_recap#attachments> with custom functions for a UID.

                 

                If you use UUIDs on all of your records, merging in new records from offline users and updating existing records using these keys will simply work without you having to ever worry about overlapping sequences or duplicate values.  That's a pretty big benefit.

                 

                I've done some work in the electronic medical records arena, and the standards there insist on a UUID with a specific 'shape', if you will.  Ray's excellent custom function doesn't fit that model, but the free ScriptMaster plugin from 360Works did.  I think you will also find UUIDs in SyncDek (http://www.synkdek.com>

                 

                As for a discussion on what is and is not encoded in a UUID and why it should or shouldn't be, I got some good info from Wikipedia actually:  (http://en.wikipedia.org/wiki/UUID>

                 

                -- Drew Tenenholz

                • 5. Re: Creating auto primary keys but with different prefixes to avoid duplicates
                  DrewTenenholz

                  Amy --

                   

                  At our local user group here in Boston, Ernest Koe from the Proof Group gave a talk last month and strongly suggested (among many other things) that we all should be using UIDs or UUIDs or GUIDs in every table, all the time.  It's just becoming a design imperative.  It's not all that hard, either, and there are lots of benefits that come from having them.  One that doesn't is human-readability; it is a pain to try reading the keys and making sure the right records are linked in relationships with these.  You get over that, though.  He has some sample files that he wants to to crib from at (http://proofgroup.com/blog/2011/aug/devcon_2011_recap#attachments> with custom functions for a UID.

                   

                  If you use UUIDs on all of your records, merging in new records from offline users and updating existing records using these keys will simply work without you having to ever worry about overlapping sequences or duplicate values.  That's a pretty big benefit.

                   

                  I've done some work in the electronic medical records arena, and the standards there insist on a UUID with a specific 'shape', if you will.  Ray's excellent custom function doesn't fit that model, but the free ScriptMaster plugin from 360Works did.  I think you will also find UUIDs in SyncDek (http://www.synkdek.com>

                   

                  As for a discussion on what is and is not encoded in a UUID and why it should or shouldn't be, I got some good info from Wikipedia actually:  (http://en.wikipedia.org/wiki/UUID>

                   

                  -- Drew Tenenholz

                  • 6. Re: Creating auto primary keys but with different prefixes to avoid duplicates
                    RayCologon

                    jrenfrew wrote:

                     

                    There is some interestign discussion on the whole UUID thing here as there are some privacy issues in Europe with encoding the NIC and time as it makes it possible to identify the data source.

                     

                    http://filemakerstandards.org/display/bp/Key+values

                     

                    Yes - I agree that it's an interesting debate.

                     

                    Regarding privacy - if the issues are to be taken as having the merit they purport to - they would presumably apply equally to any database that captures creation timestamp, workstation and/or login account. Which many, if not most, professionally designed databases do for reasons that have to do with data management, data integrity and data ownership/attribution. At which point we do indeed have a problem...

                     

                    Compliance issues aside, I think there are circumstances when it's appropriate to think carefully about what is captured, how it's stored and to whom it's available. In that respect, the encoded form of the uID I mentioned may be preferable in some cases than a form that captures the raw data. But there may well be occasions - such as when EU compliance is at issue - when neither would be desirable, in which case an alternative approach would be to include a one-way hash or otherwise adapt the methodology. In general I think that's preferable to random generation, since a random "UUID" is, by definition, not in fact unique.

                     

                    As an aside, the post you referenced includes some comments on the performance of different systems - which comes down to a trade-off between speed and convenience/flexibility. Encoding and storing values as numbers is fast, but human readability (and 'transcribability' etc) suffers, as does flexibility when it comes to multi-line keys etc. All are legitimate considerations in the choice of a unique ID system for a given application - though perhaps somewhat beyond the scop of the question asked here...

                     

                    Regards,

                    Ray

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

                    R J Cologon, Ph.D.

                    FileMaker Certified Developer

                    Author, FileMaker Pro 10 Bible

                    NightWing Enterprises, Melbourne, Australia

                    http://www.nightwingenterprises.com

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

                    • 7. Re: Creating auto primary keys but with different prefixes to avoid duplicates
                      CarstenLevin

                      This is a very interesting theme, and with our existing iPad/FMGO solutions we have used another approach, instead of trying to generate an unique ID on creation. We just use ordinary serial numbers in FMGO, but in an extra "temporary" primary key field. And we do not care if this is existing in an other instant of the same table on another unit.

                       

                      When synchronizing with the server the real primary key is set as a serial number in the main table on the database. Running from 0 and into infinity, incrementing one at a time.

                       

                      Thus a new record on the individual unit is having a status as not yet included in the system, and synchronizing changes that.

                       

                      In one of the cases we are actually creating the records in the central system and then deleting them from the local unit. And then synchronizing with the main table. Records without the real primary key are just temporary records waiting to become real

                       

                      And then there was this question

                      "There is some interestign discussion on the whole UUID thing here as there are some privacy issues in Europe with encoding the NIC and time as it makes it possible to identify the data source."

                      If you do this with a generally distributed solution witout telling people, and if you are gathering the data, then you are probably violating different privacy regulations. Probably not just in Europe. But I am rather confident that doing this within a company or an organisation is very much legal and unproblematic.

                      • 8. Re: Creating auto primary keys but with different prefixes to avoid duplicates
                        amylawgill

                        Hi win

                        Thanks so muh for the link this looks like it will solve my problem

                        I am going take a look around the rest of the site because I have oh half solved how to get signatures on FileMaker go

                        But so far the test signature I did seems to save and remain in all the signature boxes instead of being unique to one record.

                        So it saved more like an image file that stayed in the container box as I switched through records.

                        Maybe you also know the answer to that too.

                        I am very grateful

                        Thank you

                        Amy

                         

                        Date: Tue, 29 Nov 2011 14:57:15 -0800

                        From: technet@filemaker.com

                        To: amylawsongill@hotmail.com

                        Subject: Re: Creating auto primary keys but with different prefixes to avoid duplicates

                                                                                                        created by Winfried Huslik in General - View the full discussion

                         

                         

                         

                        Ami,

                        Please see < http://fmdiff.com/fm/serialnumber.html> for a viable solution for the problem you describe.

                        Especially when you create records in the field, you will (statistically) never create the same record ID within the exact same second.

                          Winfried

                         

                         

                         

                             Reply to this message by replying to this email -or- go to the message on FileMaker Technical Network

                             Start a new discussion in General by email or at FileMaker Technical Network

                                                 

                                                      Manage your email preferences.

                         

                         

                        FileMaker Developer Conference 2012 • Miami, Florida • July 16-18 • www.filemaker.com/devcon

                        • 9. Re: Creating auto primary keys but with different prefixes to avoid duplicates
                          amylawgill

                          Hi all,

                          Thank you all so much for replying to my dilemma. In fact you have made it sound so easy to solve using the uuid's etc.

                          I will dive into my database and give this a try tomorrow hopefully it will be straight forward to do assuming I can do it in FileMaker pro and that it is not restricted to FileMaker pro advanced which I don't have.

                           

                          Really appreciate all of you giving guidance

                          Thanks Amy

                          • 10. Re: Creating auto primary keys but with different prefixes to avoid duplicates

                            Ami,

                             

                            Could it be your container field is defined as global?

                             

                            Winfried

                            • 12. Re: Creating auto primary keys but with different prefixes to avoid duplicates
                              amylawgill

                              Hi Winfried,

                               

                              Thanks you were right the container field was global so have solved that. Thank you!!!!!

                               

                              Thanks for your link and advice. I dont know whether I am asking a dum question here. But when I try and use filemaker to create this UUID I seem to hit a stumbling block.

                              I am not quite sure how to do it. I have consulted my manuals, the internet, even contacted filemaker themselves who were most unhelpful of guiding me to any white papers or any other info.

                               

                              It may seem like an idiot friendly question but please advise how I go about doing the uuid as a primary key. If you guys wouldnt mind I would be very grateful.

                               

                              Thanks

                              Amy

                              • 13. Re: Creating auto primary keys but with different prefixes to avoid duplicates

                                Hi Ami,

                                 

                                Unfortunately I don't know where I can pick you up. What is your current field definition?

                                 

                                If you use my suggestion, you need a number field (not a calculation). Under Options for exactly THAT field you then define an Auto-enter calculation, result type number.

                                 

                                Winfried