4 Replies Latest reply on Aug 10, 2009 10:54 AM by billk1radius

    Modifying the Record ID using a Script



      Modifying the Record ID using a Script


      Dear Helpful Filemaker Gurus


      I am creating a Sales Order system tied to our Customer Contact and Show databases which is so far working really well.

      A key feature here is that we will have people with separate copies of the database at different trade shows at the same time.

      They have a pull-down to select which show they are at as well as the chosen price list.


      An issue is that 2 people at shows SHOWA and SHOWB can be created Sales Orders at the same time and so when I go

      to merge the orders I have multiple records with the same record id.


      In order to avoid duplicate record numbers I set the record id to the showname - #  so person at SHOWA will have record

      ids of SHOWA-1, SHOWA-2 etc and the other show will be SHOWB-1, SHOWB-2, etc.  Then I can import them both in without

      conflict after the shows are over.


      I have a script to change k_ID_show in the current record that works well.  However, when I create a new record after that

      it defaults to incrementing the last k_ID_show instead of the replacement I just set.


      Is there a way in scripting to do this so the system sets the new record id as though I am using Manage Database and doing

      it manually?  I don't want the users to have to go into Manage Database.


      Thanks so much for your help!


        • 1. Re: Modifying the Record ID using a Script

          Howdy billk1radius,


          Good call on not wanting users into "Manage Database"...ugliness waiting to happen.


          Answering rather around your question than to it, if you choose your ID prefixes well...why change them?  Since the ID for each record should be a meaningless code, just prefix the FMP installation itself and let it ride.



          Homesite ID series = "Base" & #"

          Laptop A ID series = "LA" & #

          Laptop B ID series = "LB" & #



          Now you won't have to update the Dbases every time someone goes to a show, or client, or wherever.  After all, the ID# is just a linking tool.  The only need for it is to be a unique identifier...you can get this by having each program installation have it's own series.


          Did I misunderstand something?

          • 2. Re: Modifying the Record ID using a Script

            Ninja - thanks for the response.


            The thing is that when show season is over we are all back in the office sharing one database on our server - but

            your suggestion does give me an easier out if I can't do it the premier way.


            And I am assuming with your experience that since you did not pop back with a solution that this might be

            the best or only alternative??




            • 3. Re: Modifying the Record ID using a Script

              Howdy billk1radius,


              I won't tout my experience since many on this board have much more than I, but I will say that intentionally altering or changing primary keys in volume is inherently dangerous.  What happens when you want to import related tables of data...that could get ugly real fast.


              If your preferred method is to re-ID batches of records on import...you'll want to re-think the preference IMO.  Can it work?  Sure it can...but it is avoidable risk.


              My experience with FMP is that there is rarely an "only way"...there's easy/hard, low/high maintenance and low/high risk of data corruption.  Your proposed method strikes me as easy, high maintenance, moderate risk...I'm proposing easy, low maintenance, low risk...I've inadvertantly answered questions here without giving perspective and had folks choose the insecure/unstable options...


              At the end of the day, all that is necessary is that the ID# be unique...it doesn't matter what it actually is...K.I.S.S.


              Others on the board are more than welcome agree/edit/disagree/add.

              • 4. Re: Modifying the Record ID using a Script



                Actually your advice seems pretty reasonable and I totally agree with keeping is simple and clean.  You have also

                given me a way to set up every laptop so even if they add new contacts while in the field I can easily do an import

                without duplicating records.


                This fixes another issue I had not addressed yet and is a simpler solution!


                Appreciate the help and also just want to say that the more I get into working with relational tables the more

                I am respecting the simplicity of the Filemaker interface and tools.  Not always easy to find an answer with Help

                because as you say there are usually a number of ways to implement a solution.  But that's what I come to you for ;^)