14 Replies Latest reply on Mar 28, 2017 1:38 PM by marksealey

    Resetting primary key numbering when the sequence is 'off'

    marksealey

      I have a multi-table FMP Advanced 14 file where the consecutive numbering of the index field which I created as the primary key for the main table (properties as in the attached grab) has gone awry. The sequence 'jumps' in several locations, leaving gaps in the numbering.

       

      The harmless result is that the records' apparent numbering system no longer corresponds to the actual numbers - e.g. for totals as returned by 'Get(TotalRecordCount)' etc.

       

      The more serious result is that I am no longer able (correctly) to display PDFs in containers linked to some of the records.

       

      Is it safe to browse to the field in question and reset its numbering using this procedure, please?

       

      Yes, the field in question is the Primary Key handling relations to other tables. But it's also a field I created. Good idea? Bad idea, please?

        • 1. Re: Resetting primary key numbering when the sequence is 'off'
          Mike_Mitchell

          Bad idea. BAD idea. Did I mention ... BAD idea?

           

          Resetting a serial ID is trivial. The impact it has on your relationships is most definitely not. As you've discovered, if you change the primary key on your parent, it will break your joins to the child records and you'll have an unholy mess on your hands.

           

          One of the requirements for a primary key is that it be immutable - it never changes, once established. If it disturbs your karma to have gaps in a serial sequence, then use something else for a primary key and a serial number for visual purposes. (One of my requirements for a primary key is that we don't attempt to use it for user identification. We keep it hidden from the user precisely so there's no temptation to monkey with it.)

           

          I generally avoid serial numbers as key fields. They're prone to problems during data migration and require more handholding than a simple UUID does. Yes, they will index faster, but unless you're dealing with a million records, it won't likely matter.

           

          HTH

           

          Mike

          • 2. Re: Resetting primary key numbering when the sequence is 'off'
            philmodjunk

            The more serious result is that I am no longer able (correctly) to display PDFs in containers linked to some of the records.

             

            That suggests that you are not using the correct method for working with these PDF's. A "skip" in the sequence should not have such a result.

             

            It would be better to fix that problem than to try to keep a perfect serial number sequence. Primary keys should be unique and never modified by the user. Hiding them completely from user view is also a good idea and one that many developers insist on.

             

            If you are using the default (on creation) option for serial numbers, gaps normally occur because someone created a new blank record and then deleted it. Importing data from a back up or from an older version into a newer version might also generate a gap if the next serial value in the field of the file getting the imported records is set to a larger value than just the value one greater than the largest value.

             

            So one option is to never allow users to delete a record in this table. When they want to delete it, they instead click a button that marks it "void" or "deleted" by setting a value in a field. You can then account for this field even though your scripts, relationships, portal filters, etc can be set to hide such fields from the user when the system is in normal day to day use.

             

            But that really isn't necessary unless there is some sort of "audit" that takes place that requires you to account for every record in this table by the serial number.

            • 3. Re: Resetting primary key numbering when the sequence is 'off'
              marksealey

              Thanks, Mike - I feared so… :-)

               

              I am convinced that the gap in the numbers must be causing the other bug, though, which I need to fix.

               

              I've put debugging Custom Dialogs into my script to show which record FMP thinks it's going to.

               

              But the Go to Record step invariably goes to the wrong record!Script.jpg

              • 4. Re: Resetting primary key numbering when the sequence is 'off'
                marksealey

                Got it, Phil! I know you're right, so…

                philmodjunk wrote:

                That suggests that you are not using the correct method for working with these PDF's. A "skip" in the sequence should not have such a result.

                What would that be, please?

                 

                I call the above script from a button representing the presence or absence of a PDF in the record.

                 

                I accept that there must - in view of what you and Mike both say, which I knew was a bad idea - be a better way to display PDFs. Do you have docs for that, please?

                • 5. Re: Resetting primary key numbering when the sequence is 'off'
                  Mike_Mitchell

                  You're confusing serial number with record number. They're not the same thing. The record number represents the position of the record in the current found set. It can change anytime you change the found set or even the sort order.

                   

                  If you want to go to a particular serial number, perform a Find for that number.

                  • 6. Re: Resetting primary key numbering when the sequence is 'off'
                    marksealey

                    That makes sense, Mike; thank you.

                     

                    The table in question actually has two such fields: the kp_clip_id in the grab from my first post; and this:clip_number.jpg

                    Even a Records > Go to Record goes to the 'wrong' record.

                     

                    How, then, should I be scripting a button to go to its corresponding PDF for that record, please?

                    • 7. Re: Resetting primary key numbering when the sequence is 'off'
                      philmodjunk

                      Even a Records > Go to Record goes to the 'wrong' record.

                       

                      Again, as Mike said, this refers to the record number, not the record serial number value in the ID. As Mike said, you need to perform a find for that record.

                       

                      Or you use a relationship that matches by that serial number value. That's what primary keys are for after all.

                      • 8. Re: Resetting primary key numbering when the sequence is 'off'
                        marksealey

                        Thanks, Phil, Yes - I (think I) see :-)

                         

                        You're saying that the script step is wrong, and that it should be FINDing the record by its number, aren't you?

                         

                        If so:

                         

                        1. which would be the correct script step(s) to Find
                        2. which value should I search for?
                        3. and should I pass a parameter to the script corresponding to the current record?

                         

                        In the table in question I have a Calculation field defined as follows:

                         

                        If ( IsEmpty ( clipContainerWebPage ) ; "" ; globals::PDF_icon )

                         

                        IOW it displays a PDF icon as a button whenever a record has a PDF in its 'clipContainerWebPage' container field. About 10% of the several thousand records have PDFs.

                         

                        The script above is currently attached to this PDF icon button. But you're saying to change that?

                        • 9. Re: Resetting primary key numbering when the sequence is 'off'
                          philmodjunk

                          What I lack here is the "big picture". I need a wider view of exactly how this needs to work for you.

                           

                          Please take a few steps back and provide more explanation as to what you have on your layout, what tables are involved and what you are trying to do.

                           

                          To repeat, since we are talking primary keys here, we should be matching records via relationship to get what's needed here. That's the whole point in having them after all.

                          • 10. Re: Resetting primary key numbering when the sequence is 'off'
                            marksealey

                            Of course; I'm sorry. Yes, you're working in the dark, Phil.

                             

                            For the sake of argument, may I concentrate on the one table (from a database of press clippings).

                             

                            Two Layouts:

                             

                            1. the first with all the fields for each record displayed - except the field which indicates whether or not each record contains a PDF associated with it; that's for those records where a PDF of a magazine article has been scanned, instead of the more usual URL to an online article
                            2. the second Layout with two fields only: the article's title and its PDF as determined by the calculation described in my 8th post etc in a container

                             

                            When there is a PDF, the field in question displays an Adobe 'PDF' icon. It's a button which runs the script to switch Layouts and display the associated PDF image on that Layout.

                             

                            Trying to implement what you kindly say about FINDing rather than Going to a record, I now have this:Script.jpg

                            where the Record number of the currently selected record is passed as a Script Parameter.

                             

                            It seems to work. Can you see any failings, please?

                            • 11. Re: Resetting primary key numbering when the sequence is 'off'
                              philmodjunk

                              If you have a relationship linking Press Clippings to the table of the first layout, you only need a one line script:

                               

                              Go To Related Records

                              • 12. Re: Resetting primary key numbering when the sequence is 'off'
                                marksealey

                                It's the same table in both Layouts.

                                 

                                I tried that; but it doesn't work.

                                 

                                Do you think my script passing parameter with a Quick Find has any flaws?

                                • 13. Re: Resetting primary key numbering when the sequence is 'off'
                                  philmodjunk

                                  If you see exactly the same text in Layout Setup | Show Records From on both layouts, all you need is:

                                   

                                  Go To Layout

                                   

                                  Both layouts will have the same found set, sort order and current record.

                                  • 14. Re: Resetting primary key numbering when the sequence is 'off'
                                    marksealey

                                    I see that, Phil. Thanks. I'll give it a try.

                                     

                                    Your help much appreciated…