9 Replies Latest reply on Nov 20, 2014 9:56 AM by FredH

    Duplicate records from several tables?

    FredH

      Title

      Duplicate records from several tables?

      Post

      Hi experts,

      I have a "project" (record in table A) and for this project, I have created a "base scenario" (record in table B) which includes financial info (per record in table B, there are 5 records in table C).

      I have therefore created a layout linked to table A and I have 2 portals on this layout in order to show both the "scenario info" (record in table B) as the "financial info" (records in table C).

      What I would like to do now is for the shown record in table A, duplicate both the record in table B and the 5 records in table C.  The new record in B should still be linked to the original record of table A and the 5 new records in table C should be linked to the new record in table B.

      I have tried several variations of scripts that I have found on this forum but for the moment, none gave correct results.  Or I have my script that does not execute, or it loops and creates thousands of records until I stop the script manually... :-p

      Do you know how I could solve this?

      Thanks in advance for your input!
      Kind regards,

      _Fred_

        • 1. Re: Duplicate records from several tables?
          philmodjunk

          Is this one of the scripts that you tried to use? Duplicating Bill Of Materials (duplicating portal line items)

          You might need to post your script along with a more detailed description of your relationships before anyone can offer much assistance.

          To post a script to the forum:

                 
          1. You can upload a screen shot of your script by using the Upload an Image controls located just below Post a New Answer.
          2.      
          3. You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here. (with this approach, you can get multiple script steps on the same line, please edit the pasted text by inserting some returns to separate those steps.)
          4.      
          5. If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
          6.      
          7. If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format.
          • 2. Re: Duplicate records from several tables?
            FredH

            Hi PhilModJunk,

            In attachment you will find a screenshot of my script.
            In fact, it stucks as from the beginning as it goes to my "Scénario" layout and says that it cannot handle the records.

            What I would need : I am on Layout 1 showing a record of my Project Table (A).  This layout is also showing one record of the "Scénario" table (being the first scenario : base) as also the NOP results (6 years thus 6 records).

            Purpose is here to copy the base scénario & give it another name (Simulation 1, 2, etc) as also its linked NOP records.
            I will use this copy to change some parameters and check the result.

            What should I change?

            Thanks in advance for your input!
            Kind regards,

            _Fred_

            • 3. Re: Duplicate records from several tables?
              philmodjunk

              Your GO to Related records script step should specify the Scenario layout instead of <Current layout>.

              The goal is to bring up a set of all related scenario records in a found set so that you can duplicate them.

              The go to layout step immediately after it should be removed.

              But you should also test to confirm that at least one related record in that table exists or your script can end up on the wrong layout.

              Your next Go to Related Records step has the same error.

              • 4. Re: Duplicate records from several tables?
                FredH

                Hi PhilModJunk,

                Thanks for your reply!

                I adapted based on your suggestions but it is still not fully functioning.  Basically, I first forgot to add the "duplicate record" for the NOP part and I added an additional "if Not Empty(NOP::_fkScénarioID)".  I also took out the lines referring to $K and tried to replace it by "Exit loop if get (FoundCount) = 0 but when I do that, my scenario record gets duplicated a thousand times until I stop the script.  Without this "get" part, the scenario record is duplicated correctly but my NOP records are not ok.  Depending on the tests I did, it only duplicates the first and the last or only the first.

                Any idea what could still be wrong?  

                Also, another additional question : in case my project already has a couple of scénario's, how do I make sure that the script duplicate the one that has "base" as value for the Name field?


                Thanks in advance for your input!
                Kind regards,

                _Fred_

                • 5. Re: Duplicate records from several tables?
                  FredH

                  Hi PhilModJunk, 

                  In case you read my answers via mail, I edited the previous one.

                  KR,

                  _Fred_

                   

                  • 6. Re: Duplicate records from several tables?
                    philmodjunk

                    You need to take another look at the example script that I posted a link to.

                    Your record duplication code must be on an unsorted group of records and looks like this:

                    Exit Loop if [ Get ( FoundCount ) = 0 ]
                    Duplicate Record
                    Set Field
                    Omit Record
                    Go to Record/request/page [first]
                    omit record
                    End Loop

                    Take a found set of records in a list or table view and watch what happens when you duplicate the first record in the found set. The newly created record appears as the last record. You need to omit it and then omit the record you duplicated to create it--the first record. If you don't omit both records, your loop never gets a found set of 0 records.

                    And make sure that you refer to the script at the end of Duplicating Bill Of Materials (duplicating portal line items) and not the one at the beginning of that thread as it has a flaw in it that was corrected in the new script posted near the end of the thread.

                    • 7. Re: Duplicate records from several tables?
                      FredH

                      Ok, I was indeed focusing on your first post in that thread and not on the last.  I adapted it and it works perfectly, thanks a lot! :-)
                      As I know that my "base scenario" is always the first one that gets created (created automatically when a new project is created), what is my best option if I want to make sure that I always duplicate that scenario?

                      Going for a "Go to Record first" in the beginning of my script when I look for the related records in "Scénario" or should I perform a search on "Base" in the Name field?

                      Kind regards,

                      _Fred_

                      • 8. Re: Duplicate records from several tables?
                        philmodjunk

                        If your relationship is unsorted, the first related record will be the first record of the set to be created. In the found set produced by GTRR, this will be the first record. But there are ways this might get changed. For example, if your file becomes damaged and you import records into a new copy of the file or you deploy an updated design and import the records into the file. The order of the imported records might no longer be in creation order in some cases. Thus it would be safer to sort your records by a field that puts the oldest record first or last (such as by sorting on an auto-entered serial number field or creation timestamp field) and then go to that first or last record.

                        • 9. Re: Duplicate records from several tables?
                          FredH

                          Hi PhilModJunk,

                          Thanks a lot!  I indeed added a "sort" to my script in order to be sure to select the correct record to duplicate.

                          KR,

                          _Fred_