4 Replies Latest reply on Nov 18, 2009 4:32 PM by Sorbsbuster

    Generate Keyfield Serial Number by Auto-entry or Calculation?

    Sorbsbuster

      Title

      Generate Keyfield Serial Number by Auto-entry or Calculation?

      Post

      I got my fingers burned a long time ago with importing records in FM3, and have been wary of this characteristic ever since.  Suggesting I 'go test it' would be logical, but I am hoping someone can assure me, one way or the other.

       

      All The Manuals say that one should create a KeyField by setting it as 'Auto-enter, increment by 1, prohibit modification, etc.'  I understand all that, and used to use it regularly.  But then one day I imported a set of existing records into an updated clone, and when I checked the details for that field (in 'Define Fields...') it showed that the 'Next value..' box had NOT correctly anticipated, or updated, what the next value should be.  Had I continued to use the file as usual it would have generated a short series of duplicated key values.  Ever since then (about 1886, I confess) I have auto-generated the key serial number field using a SelfRelationshipByConstant, setting the Key Field to be Max(SelfRelationship::KeyField) + 1.  I have never experienced any problem with importing existing data into a new file since.

       

      Can someone re-assure me that I have been wasting my time?  I recognise that another parameter is whether or not, upon import, I select 'Update Look-ups, etc...'.  I admit the horrible experience of all those years ago scarred me.  I have studiously designed all of my files to allow me to avoid - at all costs - ever ticking the option to 'Update Look-ups...'etc.

       

      In other words: if I resort to generating my KeyField using 'auto-enter a serial number', will importing existing records into an updated clone correctly set the 'Next Incremental Value' correctly, EVERY TIME?

       

      Thanks in anticipation of sleeping easy again,

       

      Alan

        • 1. Re: Generate Keyfield Serial Number by Auto-entry or Calculation?
          philmodjunk
            

          I would think performance would suffer with your method when the number of the tables in the record starts to get pretty large.

           

          When importing records into a clone such as you describe, you either have to manually update the serial number setting or use a set next serial value in a script to update your serial number settings.

           

          PS. the enable auto-enter options check box you refer to will assign new serial number values to all your imported records--which is likely a recipe for disaster.

          • 2. Re: Generate Keyfield Serial Number by Auto-entry or Calculation?
            Sorbsbuster
              

            Thanks for the speedy reply, Phil - you have confirmed to me that my horrible memory was not a figment of my imagination, but that my solution to it is not the most elegant, maybe.  Your recommendation that I have to manually update the serial number setting was exactly what I stumbled across all those years ago, and I have always wondered was that 'just me' or had I missed something.

             

            Your warning about "...will assign new serial number values to all your imported records" is exactly (yet another) reason why I have a morbid aversion to selecting it - ever.

             

            I never thought of using "use a set next serial value in a script to update your serial number settings."  Paranoia had taken over, y'see...

             

            So, if I:

            - Set the KeyFields to be by 'Auto-enter serial number, etc...'

            - Import without selecting 'Update values...' etc

            - Run a script to set all KeyField values (ie: in various tables) to be Max ( SelfRelationship::KeyFieldValue ) + 1

             

            ..it'll work the way I'm trying to make it do now?

             

            (Even if you say 'Yes' I suspect it will still take months of expensive therapy to wean me on to it.  The awful fear of that late night still haunts me.)

             

            Thanks again,

             

            Alan

            • 3. Re: Generate Keyfield Serial Number by Auto-entry or Calculation?
              philmodjunk
                

              Sorbsbuster wrote:

              So, if I:

              - Set the KeyFields to be by 'Auto-enter serial number, etc...'

              - Import without selecting 'Update values...' etc

              - Run a script to set all KeyField values (ie: in various tables) to be Max ( SelfRelationship::KeyFieldValue ) + 1


               

              That's essentially how I do it, though a summary field set to comput "maximum of" does the same thing and doesn't require the self join relationship.

               

              The biggest criticism I have of the "Allow auto-enter..." check box is that it's all or nothing. You can either enable all auto-enter settings on all the fields in your target table or none of the settings. You can't enable these settings for some fields and not for others.

               

              Never forget your safety net when working with such mass modification operations such as Import Records: Make a back up copy just before you do it. Then if all else fails, you can toss out the file and try again with your back up copy.

               


              • 4. Re: Generate Keyfield Serial Number by Auto-entry or Calculation?
                Sorbsbuster
                  

                I'm clear on the way forward: thanks, Phil.

                 

                Alan.