14 Replies Latest reply on Feb 29, 2016 12:23 AM by CarstenLevin

    Best practice for fixing key field problems?

    bigtom

      I am fixing a solution for a client that they had originally built off of the Invoices starter solution. The problem is the InvoiceID in that solution. When they figure out after they created an invoice and add line items that the invoice number is wrong and change it in the invoice layout it breaks the relationship to InvoiceData table and all the line items disappear. They end up with a bunch of orphaned records when they simply enter everything again and if the incorrect entry ever gets used in the future the problem is worse. Why FM decided to allow the direct use and edit of a primary key field like this in a starter solution is confusing, but they do as they wish I guess.

       

      So I need to add a UUID key that users cannot interface with and not break anything. I came up with some ideas.

       

      1. Change the current InvoiceID field to an auto enter Get(UUID) that would be used for all new records. Change the layout field from InvoiceID to a new field named InvoiceNumber. Does not break anything and new records get UUIDs while old records hold the Invoice numbers as the Key. Not consistent but a working solution.
      2. Create a new field named InvoiceNumber. Create a layout for the UUID migration with InvoiceID, InvoiceNumber and a portal to InvoiceData that has the InvoiceIDMatchField in it. Then have a script loop through every record setting the InvoiceNumber to InvoiceID and then running through the portal rows setting the MatchField to a variable Get(UUID) for the related records, then setting InvoiceID to the same UUID variable. If the key field changes in Invoices first the related records fall out of the portal all at once. If I change the Match field first they drop off one at a time and then changing InvoicesID at the end fixes the relationship again. Seems like the better way to fix this.

       

      Is there an even better way to do this that I am not seeing?

        • 1. Re: Best practice for fixing key field problems?
          CarlSchwarz

          I would say

          1) add a second "Invoice Number" field that the user can change.

          2) Lock down the old invoice number and take that off of all layouts so the users can't see it and won't edit it (replace it with the new invoice number).

           

          When you do this replace the new user facing invoice number with the old invoice number so that all of the old records look the same.

           

          The users will not notice any difference and won't have to change their workflow.

          • 2. Re: Best practice for fixing key field problems?
            CarlSchwarz

            I'm trying to edit my answer to add a bit more information but JIVE isn't letting me.

            Another reason I think your #1 is a good idea is that it leaves the structure the same and doesn't rely on any assumptions that would be required to run #2 scenario.  For #1 a DDR can be used to ensure that you change all layout occurrences and it's a complete solution.

             

            But not having seen the solution then I'm also making assumptions so... it's just a suggestion.

            • 3. Re: Best practice for fixing key field problems?
              CarstenLevin

              Yes!

              Yes!

              Yes!

               

              Never change the primary keys.

               

              Consider a few basic rules for primary keys:

              • Do not give them any meaning (like telephone number, invoice number ...)
              • Actually, consider not showing them to avoid misunderstandings.

               

              Any field with a meaning will inevitably need a change one day ... and then hell arises.

              Any meaning used in a key will probably change over the years ... and then hell arises again.

               

              Consider using one of these two types of keys, or both:

              • A serial number in a number field starting with one, unmodifiable and alway increasing with one.
              • UUID in a text field, being set by calculation on every new record.

               

              The UUID has a very important advantage when used with solutions that are not run from a server (mobile Go solutions with the file on your unit, Pro locally on your PC etc.): They will not create duplicate keys.

               

              Best regards

               

              Carsten

              • 4. Re: Best practice for fixing key field problems?
                CarstenLevin

                I can not edit either, this JIVE is a strange creature.

                 

                Just wanted to add to the beginning of my post, just after 3 x Eyes: "Carl Schwarz is right!"

                • 5. Re: Best practice for fixing key field problems?
                  bigtom

                  The current InvoiceID only appears in 5 places on different layouts. Not that bad. That is kind of what I was thinking would be easiest, but there are still all the old invoices that might get edited by accident. I might have to control the data entry somehow so that if there is a current value there cannot any editing, but still allows for entering new Invoice numbers.

                  • 6. Re: Best practice for fixing key field problems?
                    CarstenLevin

                    Hi Bigtom,

                     

                    Consider not using keys as invoice numbers at all. Clean up, transfer the number to a new field for invoice number ad delete all key fields from the layouts. You will, as everybody else, regret any compromise here.

                     

                    Best regards

                     

                    Carsten

                    • 7. Re: Best practice for fixing key field problems?
                      bigtom

                      I know how this should be done. If it just a matter of the best way to actually fix it. That is what I am looking for. I very well know that key fields should not be changed ever and should never be used for any human use. I am a big fan of UUIDs.

                       

                      What was FM thinking when they started using this poor practice in the starter solution files?

                      • 8. Re: Best practice for fixing key field problems?
                        bigtom

                        Carsten Levin wrote:

                         

                        Hi Bigtom,

                         

                        Consider not using keys as invoice numbers at all. Clean up, transfer the number to a new field for invoice number ad delete all key fields from the layouts. You will, as everybody else, regret any compromise here.

                         

                        Best regards

                         

                        Carsten

                        Pretty much what my second option was in the original post.

                        • 9. Re: Best practice for fixing key field problems?
                          CarstenLevin

                          Yes:

                          1. Create a new field: Invoice number
                          2. Transfer the values from the key field to this field
                          3. Handle any inconsistensies
                          4. Remove the key field from all layouts

                           

                          And then consider, how to set the invoice number in the future.

                          I assume that you want a sequential number, moving forward one by one, but that for some reason you sometime will have a record that is not in use .... or?

                          If not, I can not se how an invoice number could end up being wrong?

                           

                          Have a settings field centrally placed in your solution called "Invoice_number_next" or whatever. Have a script getting this and adding one to it whenever you want to set an invoice number. This way you do not need to go into the schema to change the next number.

                           

                          Will this solve your problem?

                           

                          Best regards

                           

                          Carsten

                          • 10. Re: Best practice for fixing key field problems?
                            keywords

                            I think what I would do is:

                             

                            1.     Create a new UUID field, which you will soon use as your PK.

                            2.     Populate this field for all existing records using Replace Field Contents, and then set it to auto-populate from then on.

                            3.     Create a new FK field in the Line Items table to take the new UUID key (see below)

                            4.     Use the pre-existing relationship to pass the new UUID across to matching Line Item records. That will work for all records that have not had the InvoiceID interfered with.

                            5.     Find all Line Item records that have no UUID in the new FK field. Fix these up, probably manually if there are not too many to deal with, as figuring out a way to automate the fix-up could take just as long.

                            6.     Once all Line Item FK's are correctly set, repoint the pre-existing relationships to the new UUID fields. The original InvoiceID fields are now redundant as match fields.

                            • 11. Re: Best practice for fixing key field problems?
                              CarstenLevin

                              Sounds logical, and here at Codeo Denmark and Codeo Norway we are discussing UUID or UIID + Serial number.

                               

                              The problem with UUID is that it is completely unreadable, at least if you are not a very special person

                              The problem with Serial numbers is that they are dangerous if records going into the same entity are created by offline incidents of your solution.

                              Therefore my personal opinion: If it is a server based 100% online solution I would use serial numbers as keys. And UUID if there are offline elements.

                               

                              But the decision at Codeo Denmark & Norway is to use UUID for all keys. We have then added an extra key, the serial number, which we are not using for relationships. We are also adding a lot of zz fields for household, some of them establishing where a record is created so that we are not confused if more than one end up having the same serial number.

                               

                              So, the conclusion: I hate UUID, but they are the solution:-)

                              • 12. Re: Best practice for fixing key field problems?
                                keywords

                                Re: "The problem with UUID is that it is completely unreadable"

                                 

                                This is both an annoying weakness, AND their greatest strength (FM key fields should be non-meaningful).

                                • 13. Re: Best practice for fixing key field problems?
                                  bigtom

                                  keywords wrote:

                                   

                                  I think what I would do is:

                                   

                                  1.     Create a new UUID field, which you will soon use as your PK.

                                  2.     Populate this field for all existing records using Replace Field Contents, and then set it to auto-populate from then on.

                                  3.     Create a new FK field in the Line Items table to take the new UUID key (see below)

                                  4.     Use the pre-existing relationship to pass the new UUID across to matching Line Item records. That will work for all records that have not had the InvoiceID interfered with.

                                  5.     Find all Line Item records that have no UUID in the new FK field. Fix these up, probably manually if there are not too many to deal with, as figuring out a way to automate the fix-up could take just as long.

                                  6.     Once all Line Item FK's are correctly set, repoint the pre-existing relationships to the new UUID fields. The original InvoiceID fields are now redundant as match fields.

                                  It seems like I pretty much had the right idea already and there is no real better or easier way to get it done. They at least have the current InvoiceID set to have a unique value so that helps a little. As you might guess this is not the only issue, but it seems to cause the most problems.

                                   

                                  FileMaker should really set a good example with better design and not let people get sucked into these kinds of things in their starter solutions.

                                  • 14. Re: Best practice for fixing key field problems?
                                    CarstenLevin

                                    I will of course have to say that you are right ... but in the past, before mobile units, it was nice to be able to look for the record with the key 1024 ... :-)