8 Replies Latest reply on Aug 1, 2017 7:10 AM by Mike_Mitchell

    Duplicating a series of record

    user28271

      You have a Invoice (header) with a series of Invoice Lines (related by Invoice ID).

       

      You now want to duplicate the invoice with all it's lines.

       

      A common request that I solve by....

       

      ...setting  Variable to the Current Inv #

      ...duplicate Invoice (Header) and set Variable to New Inv#

      ...go to Invoice Line List and Find for Inv# original

      ...loop a duplicate and replace with new Inv#

       

      This is a common request so I was curious if there was a better way.

        • 1. Re: Duplicating a series of record
          philmodjunk

          Pretty much how I've done it except that I will return to the original invoice and use Go To Related Records to bring up the found set of line items.

          • 2. Re: Duplicating a series of record
            danielfarnan

            Be aware that the behaviour of Go To Related Records has changed a bit over time - you want to make sure there are some line items before you issue that script step. The quickest way to do that (that I am aware of) is to check if the foreign key in the related table record is empty - e.g. IsEmpty ( LineItems::fk_InvoiceID )

             

            If there are no related records then the IsEmpty will return True, otherwise you have related records and should be able to rely on Go To Related Records to take you to a layout with the proper found set.

            1 of 1 people found this helpful
            • 3. Re: Duplicating a series of record
              Mike_Mitchell

              One option to consider is to use Import Records rather than looping over and duplicating the records one at a time. Using GTRR (as suggested by Phil and Daniel), you can isolate the set of records. Export them to a temp file, then back into the same table. Then you can use Replace Field Contents to update the primary key to match the new parent record.

               

              HTH

               

              Mike

              • 4. Re: Duplicating a series of record
                philmodjunk

                "Be aware that the behaviour of Go To Related Records has changed a bit over time"

                 

                Yes, it changed a very many versions ago, In what may have been my very first such bug report, I reported that change and was told that it was behaving "as designed".

                 

                ;-)

                • 5. Re: Duplicating a series of record
                  user28271

                  I have done the importing method, which I believe is a safer - fast approach, but when you change the name of the file you have to update all those script.

                   

                  And yes, I rename my file all the time which is something I believe you have commented before that I should try to avoid.

                  • 6. Re: Duplicating a series of record
                    Mike_Mitchell

                    I’m confused. If you’re duplicating in the same table, the name of the parent database doesn’t matter. The only file name you need is the one for the temporary file, and there's no need ever to change it; it’s a temporary file that goes away after the operation is complete.

                     

                    Set Variable

                    Export Records

                    Import Records

                     

                    Regardless of whether you change the name of the file this runs in, it will still work. It doesn’t need the name of the file at all.

                    • 7. Re: Duplicating a series of record
                      ninja

                      Just chiming in to clarify wording.

                       

                      Export to a table within the same file.

                      Import from that table...still within the same file.

                       

                      I'm guessing you're referencing renaming because you are exporting to a different file, then reimporting from that separate file...you don't need to do that...stay within the same file with a different utility table for the import/export.

                       

                      ...and don't forget to go back to the utility table afterwards and clean it up...

                      • 8. Re: Duplicating a series of record
                        Mike_Mitchell

                        If you do it that way, I can see where renaming the file will have a problem. But if you export to a temporary text file, and then re import, not only do you not need a table in your main file, but you can rename the database file without breaking anything.