9 Replies Latest reply on Jul 17, 2009 7:07 AM by mrvodka

    Adding auto-serial tweak to a duplicate record?



      Adding auto-serial tweak to a duplicate record?


      I see the functionality already exists to create a duplicate record. Is there a simple way to have the duplicate record append a number to the auto-serial that is assigned?


      For example, I would like a button to create a duplicate record of a record with serial 10001. The duplicate record should then be assigned a serial of 10001-1, the next one is 10001-2, etc.


      Creating a brand new record should work as normal, the next one should just be 10002. If I go back and create another duplicate of 10001, the next one would auto-assign 10001-3. If I make my first duplicate of 10002, it should be 10002-1.


      Hope this makes sense!



        • 1. Re: Adding auto-serial tweak to a duplicate record?

          i think this must be scripted.

          create a field "dupcounter numerical" to your fields.


          use script triggers (needs fm 10), or your "add new record button", to check if the id field is a duplicate. be sure to search for id field contence without the "-something".


          if duplicate,  add 1 to your dupcounter  field. add "-"& dupcounter  to your id field. and at last, replace the current dupcounter value in *all* found duplicates. (so if one of the dups will be deleted, all others will keep the dupcounter information)


          greetings from gemany 

          • 2. Re: Adding auto-serial tweak to a duplicate record?

            Whichever method you use to create these serials, I would highly recommend that they only be used as a display serial. You should keep FileMaker auto-serial as the primary serial. A true serial should remain meaningless.



            • 3. Re: Adding auto-serial tweak to a duplicate record?

              Thanks, I am going to try this now.  The only thing I think I cannot do is to replace the current dupcounter value on all existing duplicates if one duplciate gets deleted. That is because the id will be tied to other records in other tables once it is created, and needs to be referenced.


              So, I believe it is best to keep the counter incrementing regardless of any gaps or deleted records in the count. 


              For example, if I had the following records:






              and 10019-2 got deleted, the next duplicate of 10019 that gets created should still be 10019-4, resulting in:






              I believe this is the way to go, any comments are welcome.

              • 4. Re: Adding auto-serial tweak to a duplicate record?

                When I do this, if I have an auto-increment serial as the estimateid, is there a way to ensure the next non-duplicate record will use the next sequential serial number?


                The issue is, even if I replace the auto generated estimateid in a duplicate record with the estimateid & "-" & $dupcounter value, it still acts as if I used a serial number and puts the next auto increment serial number to the next value.


                For example-

                I create estimate 10019 , with EstimateID 10019

                I create a duplciate of estimate 10019, with EstimateID of 10019-01

                I want to create a new Estimate, and have the auto increment serial be 10020 (not 10021 like it is doing now)


                It is incrementing that value even when I create a duplicate (which I do not want), and i am not sure how to stop that (or set it back to what it was before the duplicate was created, if that's easier).


                Any suggestions would be great.



                • 5. Re: Adding auto-serial tweak to a duplicate record?
                     Do you have FileMaker pro advanced?
                  • 6. Re: Adding auto-serial tweak to a duplicate record?
                       Yes, FileMaker Pro Advanced 10.0v1
                    • 7. Re: Adding auto-serial tweak to a duplicate record?
                         It might help if you use one field for 'parent_id', in your example, 10019, and have the table self-relate based on that field.  The create a auto-entered number field = count ( Table Self:: Parent ID ).  Then, a third field, which is an concatentation calculation of the two, ( parent_id & "-" & dup_id ).
                      • 8. Re: Adding auto-serial tweak to a duplicate record?

                        I apreciate that suggestion, but is there any way to simply have that auto-serial ID not be used or incremented when creating a duplicate record only?


                        I have the rest of the script working, so that would be nice if it was possible......



                        • 9. Re: Adding auto-serial tweak to a duplicate record?

                          Since you have advanced, you can use custom menus to replace your duplicate command for that layout. The new duplicate command would run your script. In your script you can script it to grab a count of the related records using the self join method that etripoli stated earlier, into a variable ($sjcount), then duplicate the record, and then you can set your text field to add the "-" & $sjcount to your base number.


                          I agree with etripoli that you should have a field to store what base it is. Because if you dont, you will have issues when you use the Max () command to set the next number.


                          For a new record, you will have to use Max () on the self join to figure out what the highest value you have in your base field and then add 1. 


                          base ( 1,2,3,4,5, etc )

                          display ( 1, 2, 2-1, 2-2, 3, 4, 4-1, 5 ) The display will take the base and add the self join ( keyed on base ) to it.




                          Please keep in mind what I said iin my earlier about keeping a true unique serial I. This kind of mock id system should only be for display. You may run into issues in a multi-user environment as people can create new and duplicate records at teh same time.