9 Replies Latest reply on Sep 20, 2013 8:40 PM by BruceHerbach

    Seq Key counter that will not create BS records

    kojmichael

      I am trying to figure out a way to have a Seq counter for my PK and have it as the default value BUT:

       

      I need to be able to enter in a Invoice # and pull up a previous invoice without the system saying "Save current REC?"

      I need it so that when i manually enter in a KEY and save a REC it does not try to OVER ride my KEY value and save a REC with its KEY.

       

      Basicly guess it should operate as "I am suggesting that you use this KEY" since it is the next Key avail. And I wont overide your entry.

       

      Any ideas? As I have tried several different ways to get it to work correctly and it just loves to fight me.

        • 1. Re: Seq Key counter that will not create BS records
          erolst

          "It" probably just doesn't understand you – nor do I, for that matter.

          • 2. Re: Seq Key counter that will not create BS records
            mikebeargie

            Allowing any manual entry in a primary key field is just plain bad practice. I too can not understand what you are trying to achieve.

             

            If you just need some sort of suggested field for a reference field that isn't being used as a primary key, then that's different, you can just use an auto-enter calculation, which would allow override.

             

            do you have some screens, or perhaps a better explanation of why you need to do something like this?

             

            Also, are you sure you're using the phrase "primary key" correctly?

            • 3. Re: Seq Key counter that will not create BS records
              kojmichael

              LOL hahaha, Well ErLost,  It confuses me as well..let me see if I can clear it up some.

               

              In my invoices the PK field had a Serial Key counter on it.

              But no matter what i did it would create new rec's and adv the key to the next in line even when i was just trying to edit a existing invoice.

              So I changed it to Serial (On Commit) Increment by 1., It then would still Increment the counter even though I did not use it's suggested key

               

              I.e. I go into Invoices it suggest key 1054  I key in 1034 becuase it had to be updated. And I resave, Invoices would replace my 1034 with 1054

              and save the invoice out.

              So what I am asking is there  a way to let it show the suggested next Key val but NOT force it to be used in the save.

               

              I am thankful for any help.!

              • 4. Re: Seq Key counter that will not create BS records
                kojmichael

                @ Mike, Yes the PK = the invoice #,  so yes it is the Primary Key.

                As far as the auto calc process, if this is a scripted option other than being serialized it may be what I am looking for.

                The reason for Manual entry for the Key field is for later revisions to prev rec's.  Trying to prevent the constant saving of

                NON-used Keys as well..Which FM seems to be willing to do for you no matter what as well as Auto Increment without

                there being a real SAVED key in place.

                • 5. Re: Seq Key counter that will not create BS records
                  erolst

                  You're confusing the functions of primary keys and invoice numbers. The former (can) play a critical part in your database, the latter are part of your business logic.

                   

                  Unless you're a-r, you shouldn't really care about gaps in your PKs. Invoice numbers are of course something else, but those two things have no inherent connection; use a separate field and/or counter to calculate/set your invoice numbers.

                   

                  As Mike said, it's best to leave the PK in peace (don't display the field on the layout, don't write to it (as opposed to read from it) in a script), unless you have good reason and know what you're doing.

                  • 6. Re: Seq Key counter that will not create BS records
                    kojmichael

                    Wish I could leave it alone, but the charity has strict state rules to follow and there can be no gaps in their Inv#'s (at the moment are the Invoice Keys as well)

                    So I have turned off the Auto increment serialization of the field and will just try to come up with something to look in the table and see what the next highest

                    record key is and suggest it as a default.

                     

                    Not sure how to do this in FM atm, But it does seem to be surprising at it's strengths at times.

                    • 7. Re: Seq Key counter that will not create BS records
                      mikebeargie

                      Have you considered this:

                      http://www.filemaker.com/12help/html/func_ref1.31.40.html

                       

                      What erolst is suggesting should not violate your policies. We are talking about a backend primary key that is never displayed to the user, and never changes.

                       

                      What happens when you change an invoice # and you strand any records in related tables, because they are now no longer related?

                       

                      These are the types of things that are handled by true primary keys.

                       

                      What you have described, regardless of requirement or not, is a reference number, not a primary key. Primary keys do not change from the time a record is first created.

                      • 8. Re: Seq Key counter that will not create BS records
                        kojmichael

                        I feel that there is some misunderstanding in that I am not neededing to change the KEY/s.

                        ATM we are using the current Invoice # as the PK, We have need to occasionally going back

                        and updating and invoice from time to time to fix data entry errors.

                        In doing so when we try to go back and pull up a invoice via the PK and we update the rec

                        it saves out the update as the NEXT PK in the increment set.  (This we cant have)

                         

                        Sorry I have confused you, I am not used to the FM way of doing things and the way it behaves.

                        But I do thank you ALL, for your help in adv.

                        Erolst has been a great source of intel on FM and things so far, my hat goes off to ya.

                         

                        Also on the Link you posted is exactly what I need.

                        Can this be put into the Data box for a default value? or some var to act as a default value so

                        that we can use it to save.  Then we can keep serialization off and it wont self increment with out

                        actually saving records. (Great link Mike)!!!

                        • 9. Re: Seq Key counter that will not create BS records
                          BruceHerbach

                          When you go back to update an invoice,  you should be doing a search for an existing record.  So enter find mode,  enter the invoice number and perform find.  This will not create new record and will not increment the counter.   Once you have the record you should be able to update other fields. 

                           

                          While the invoice number is the primary human key. You may want a primary key field that is either numerical or a text UUID value as the real primary key for the record. It should be this key that is used in relationships.  Something to consider.