2 Replies Latest reply on Nov 20, 2013 11:57 AM by Mingrl88

    Help Please - Duplicating Record also Duplicates Unique ID so is Not Unique Anymore

    Mingrl88

      Title

      Help Please - Duplicating Record also Duplicates Unique ID so is Not Unique Anymore

      Post

           Hi All, 

           In my database I have several tables (lets call them tables ABC) and for each ABC table when a new record is created it needs a unique ID, thus I have a separate table (lets call that table X) that runs a script when new records are committed in tables ABC to assign a new unique_ID. The field unique_ID is set to be unique and not be modified and until now I notice no problems however I just realized that the "duplicate record" function when you right click in browse mode, which is most helpful in saving time when entering records and I have used a lot in recent record additions, also duplicates the unique_ID field, thus, it is not unique anymore, in fact I have about 100 records now all with the same "unique_ID". 

           I realize if I disabled the "duplicate record" function I could have my own button and script behind it that would first duplicate the record but then immediately delete the value in the unique_ID field so when my "commit record" script ran it did so correctly. Unfortunately I don't know how to disable menu options individually and I don't want to change the other menu options that a user can access. 

           So I thought, well maybe I can just add to the "Create ID" script that runs when the records in tables ABC are committed and the attached screen shot is what I came up with. It works wonderful! except that because it is checking to see if the unique_ID already exists EVERY TIME a record is committed it works even when I don't want it to. What I mean is if the field is not empty then I can guarantee that it already exists because it was generated by that table. So unless there is a way to modify this script I don't know what else to do.

           All I want to do is to make sure when you "duplicate a record" you don't duplicate the unique_ID, or if you do it is very temporary and on "commit record" it assigns a new TRULY unique ID. 

           Any suggestions would be most helpful. Thank you!

             

      Cread_ID_R_Script.png

        • 1. Re: Help Please - Duplicating Record also Duplicates Unique ID so is Not Unique Anymore
          philmodjunk

               You can't remove duplicate record from your menu unless you use FileMaker Advanced to install a custom menu. Once the custom menu is installed this will also be the case when a user opens the file with FileMaker Pro. Let me know if you have or have access to FileMaker Advanced and I'll tell you more about custom menus if you are interested.

               The problem here is that a newly duplicated record and a record that is committed will look exactly the same in terms of whether or not there is data in your ID field.

               What your OnRecordCommit triggered script could do, is check for other records with the same value in this field and then clear and reassign an ID when this is the case.

               A self join would be one simple way to check for duplicates. Make a new table occurrence of your table and link it to your current one by this ID field. Then if Count (NewTableOCcurrence::ID ) is greater than 1, you have a newly duplicated record that needs its own unique ID.

          • 2. Re: Help Please - Duplicating Record also Duplicates Unique ID so is Not Unique Anymore
            Mingrl88

                 Phil,

            "What your OnRecordCommit triggered script could do, is check for other records with the same value in this field and then clear and reassign an ID when this is the case."

            Once I made sure it showed "all records" when it went into the ID layout it seems to be working perfectly! thank you for this insight, I was not aware of this power in a self-join relationship. 

            Unrelated to this topic, I posted a thread on another post you were a part of a while ago, if you have a moment to respond I would appreciate it greatly. Here is the link to the thread: Insert Hyperlink to website in Filemaker Report

                  

                 Thanks again!