1 2 Previous Next 23 Replies Latest reply on Dec 5, 2011 11:28 AM by philmodjunk

    Duplicating manies of one into new records

    MikeF

      Title

      Duplicating manies of one into new records

      Post

       

      Need to automate duplication of one record in a one-to-many, then duplicate all records of two related “many” tables that contain the foreign key of that previous record.

      Example:

      Go to layout Projects

      Duplicate Record/Request

      … Let’s say the original ProjectID is 2, and the duplicated ProjectID is 3.

      The above works perfectly, but the related portals are blank.

       

      So now the script needs to go to table Revenue, “duplicate” all records that contain the foreign key 2 in field ProjectID into new records [in the same Revenue table], and make the ProjectID field 3 in those records.

      Then same thing for the Expenses table.

       

      Have made a few attempts at passing script parameters, but am dead in the water.

      Any assistance will be greatly appreciated.

      Thanks,

        Mike

        • 1. Re: Duplicating manies of one into new records
          philmodjunk

          Here's a link to another post where I helped a user do this: Duplicating Bill Of Materials (duplicating portal line items)

          • 2. Re: Duplicating manies of one into new records
            MikeF

             Ok, it's working for the most part.

            There's a kink I have to work out, will get to that in a few minutes.

            But when the script is initiated, a dialog box comes up asking ...

                Go To Record  2 of 3

            ... Where 2 is the current ProjectID.

            Since the button will say "Duplicate THIS Project", that dialog box is redundant and [potentially] confusing.

            Can it go away??

             

            ** It does not occur when simply DuplicateRecord is run on its own.

             

            Thanks,

              Mike

            • 3. Re: Duplicating manies of one into new records
              philmodjunk

              Find the go to record step responsible and check the "perform without dialog" check box for it.

              • 4. Re: Duplicating manies of one into new records
                MikeF

                 Thanks, I had actually done so in the interim between messages.

                 

                Now for that kink, which I'd like to run by you prior to constructing a huge script.

                Once the script was working, noticed that I'd over-simplified the description.  There's a table in between Projects and Revenue/Expenses.

                 

                Projects contains multiple Events.  That is now working with your script.

                 

                But each Event contains multiple Revenue and Expense records from their related tables.

                So, the script would be, to paraphrase ..

                Duplicate Project, then duplicate new Event records for each new ProjectID.  [Again, presently working.]

                Duplicate *each new Event* in multiple records of the related Revenue and Expense records in each of those tables.

                 

                Believe this should work using a variation on your script, hauling together multiple loops?

                Thanks,

                  Mike

                 

                 

                 

                 

                • 5. Re: Duplicating manies of one into new records
                  philmodjunk

                  yes it should work as long as you keep track of the current set of records and make your duplications in the correct order.

                  • 6. Re: Duplicating manies of one into new records
                    MikeF

                     

                    Well, seem to be stuck already.

                     

                    At the end of your script, need to find the EventID's that match the ProjectID's.

                     

                    GoToLayout Events

                    Show All Records

                    PerformFind  -- Events::ProjectID = $ProjectID

                     

                    Dialog box comes up, says "No records match this criteria". But when I hit the Modify Find button, the correct ProjectID number is there.

                    If I hit Show All Records, then attempt to find the same ProjectID number, it works.

                    ?????

                    Thanks,

                      Mike

                    • 7. Re: Duplicating manies of one into new records
                      philmodjunk

                      I'd need to know more.

                      Are these your basic relationships?

                      Projects---<Events----<Revenue
                                          |
                                          ^
                                      Expenses

                      ---< means "one to many".

                      (If revenue and expenses are a single table, that's even simpler for the duplication script.)

                      If so, please post your script. I'd guess that you are either on the wrong layout or your step to specify find criteria is referencing the wrong table.

                      To post a script to the forum:

                      1. You can upload a screen shot of your script
                      2. You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF.
                      3. If You have FileMaker advanced, you can generate a database design report and copy the script as text from there.
                      4. 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 look to your script. (Use the HTML option and paste the text into the HTML editor.)
                      • 8. Re: Duplicating manies of one into new records
                        MikeF

                         

                        Projects::ProjectID - one      to Events::ProjectID - many

                        Events::EventID - one      to Revenue::EventID - many  and also to Expenses::EventID - many

                         

                        So one Projects::ProjectID  has many Event::ProjectID's.

                        Then one Events::EventID has many Revenue::EventID's and many Expenses::EventsID's.

                        Revenue and Expenses are two separate tables related to Events::EventID by their foreign keys in field EventID.

                         

                        Does this make sense?

                        Thanks,

                          Mike

                         

                        • 9. Re: Duplicating manies of one into new records
                          philmodjunk

                          It's what I posted and what I expected.

                          • 10. Re: Duplicating manies of one into new records
                            MikeF

                             

                            So to get started, don't I need to find all the Events with the new ProjectID ??

                             

                            Thanks,

                              Mike

                            • 11. Re: Duplicating manies of one into new records
                              philmodjunk

                              You'll need loops nested inside each other. The outer loop steps through a set of events records and then two inner loops step through and duplicate the expense and revenue records. Note that I don't use Perform Find anywhere in the original demo script either. You can use it but you get a longer script and since these are releated tables of records, we can use Go To Related Record instead.

                              #Starts from Projects based layout
                              Freeze Window
                              Set variable [$RecordNumber ; Value: Get ( RecordNumber ) ]
                              Duplicate Record
                              Set Variable [$ProjID ; Projects::ProjectID ]
                              Go To Record/Request/Page [$RecordNumber]
                              If [ Not IsEmpty ( Events::ProjectID  ) /* there are events to duplicate */]
                                 Go To Related Record [Show only related records; From table: lineItems; Using layout: "Events" (Events)]
                                 Go To Record/Request/Page [first]
                                 Loop
                                    #Add GTRR and loop here to duplicate the expenses records for this event if there are any to duplicate
                                    Go to layout [Events (Events)]
                                      #Add GTRR and loop here to duplicate the revenue records for this event if there are any to duplicate

                                    Go to layout [Events (Events)]
                                     Duplicate Record
                                    Set Field [ Events::ProjectID ; $ProjID ]
                                    Omit Record
                                    Go To Record/Request/Page [first]
                                    Omit Record
                                    Exit loop If [ get ( FoundCount ) = 0 ]
                                 End Loop
                                 Go To Layout [original layout]
                                 Go To Record/Request/Page [Last]
                              End IF

                              • 12. Re: Duplicating manies of one into new records
                                MikeF

                                 

                                It's looping everything into the first project, as if the second loop is too early in the chain.

                                 

                                I can upload a very simple mockup database if you like, just to have a look ...

                                 

                                Thanks,

                                  Mike

                                • 13. Re: Duplicating manies of one into new records
                                  philmodjunk

                                  Or you could post your script. "looping everyting" as in all events records or the grandchild records in expenses and/or revenue?

                                  All of this runs from the context of the current record on the Projects layout. Is that the case when you test your script?

                                  To post a script to the forum:

                                  1. You can upload a screen shot of your script
                                  2. You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF.
                                  3. If You have FileMaker advanced, you can generate a database design report and copy the script as text from there.
                                  4. 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 look to your script. (Use the HTML option and paste the text into the HTML editor.)
                                  • 14. Re: Duplicating manies of one into new records
                                    MikeF

                                     

                                    It's the Test-ProjectsEvents file ..

                                     http://www.4shared.com/folder/NwTTN1my/_online.html

                                     

                                    I've removed all the various iterations of attempts at the nested loop, so this one now works like it did.

                                    Puts the ProjectID's in Events table fine, but does not put the EventID's in Revenue or Expenses.

                                     

                                    Thanks Phil, extremely, for your assistance with this one.

                                       Mike

                                    1 2 Previous Next