1 2 Previous Next 22 Replies Latest reply on Apr 7, 2010 10:30 AM by Pebbles

    Script for Duplicating a record with a portal

    Pebbles

      Title

      Script for Duplicating a record with a portal

      Post

      Hello, I am using FM 10 on Windows XP.I am fairly new to the program but I am trying to learn as I go along.  

       

      Let me describe what i have.  i have created a database with a one to many relationship I have a parent database the has a portal for children data. each has its own auto generated id number on their individual tables.

       Next year we need to duplicate the records to update them with any new information. I do understand I have to create a script to preform the task but every time I try it still doesn't duplicate the children data in the parent table or in the related table.  Please help!

       

      thoroughly confused:smileysad:

        • 1. Re: Script for Duplicating a record with a portal
          philmodjunk

          You need to use either Go To Related Record or a scripted find to pull up the portal records on a layout that refers to the same layout as your portal does. THen you loop through each of these records, duplicating them and updating them with the new Id number or other key used in your portal's relationship so they show up in the new record's portal rather than the original.

           

          "Next year we need to duplicate the records to update them with any new information."

          Chances are good that by changing your approach you can manage this type of update without having to duplicate records.

           

          Go To Related Records is a very useful tool, but which is very poorly documented. To learn more about GTRR, click the following link:

          The Complete Go To Related Record

          • 2. Re: Script for Duplicating a record with a portal
            Pebbles

            Phil, 

             

            Thank You for the response and the helpful information.  Upon reading the link and the book I have I have come up with this script:

             

            Go to layout ["COE"(COE]

            Go to Related Record [show only related records; Match found set; From Table: "Children Data" (Children Data)]

            Go to layout ["COE" (COE)]

            Duplicate record/request

            Set variable [$ID; Value: COE::COE_ID]

            Go to Layout ["Children Data" (Children Data)]

            Loop

             Go to Record/Request/Page [First]

             Duplicate Record/Request

             Set Field [COE::COE_ID[$ID]]

             Omit Record

             Go to Record/Request/Page [First]

             Omit Record

            End Loop

            Go to Layout ["COE" (COE)]

             

            This script duplicates one main record, my parent table which is labeled COE. However, the Children data does not duplicate in the portal.  Instead it duplicates on each of the original orders I have in the data base.  I know I must have something wrong in the script, but I cannot figure out what it is. Can you please help!

             

            Thank You

            Anna

            • 3. Re: Script for Duplicating a record with a portal
              philmodjunk

              What version of filemaker?

              Your GTRR step seems to be missing a layout parameter.

              Not being familiar with your database I'd check two things to start:

               

              1) Is COE_ID an auto-entered serial number? Is it assigned "on creation" or "on commit"? (If "on commit" you need a commit record step just before set variable)

              2) Check to see what records and layout are current immediately after the GTRR.

               

              If you have filemaker advanced, use the Debugger and step through the script one step at a time and watch to see where it goes wrong.

               

              If you don't, insert a Pause/resume step after the GTRRR and at other key places so you can watch the script run step by step. Putting this kind of pause inside your loop (which needs an exit loop step by the way) can be especially helpful in making sure the right records are being duplicated.

              • 4. Re: Script for Duplicating a record with a portal
                Pebbles

                 I am currently working with Filemaker 10. 

                My GTRR is:

                 

                Go to Related Record [Show only related records; Match found set; From table: "Children Data"; Using Layout: "Children Data" (Children Data)]

                 

                My COE_ID is auto-entered "on Creation" and the record immediately available after the GTRR is the COE table.  I tried inserting the pause/resume step but still cannot see where or why the child data duplicates only on the original record and not on the duplicate record.  

                • 5. Re: Script for Duplicating a record with a portal
                  philmodjunk

                  From your code, immediately after your GTRR, you should be on the Children Data" layout with a found set of all the records related to the current record on your COE layout.

                   

                  Put this step in immediately after the GTRR:

                   

                  Show Custom Dialog [ Get ( LastError ) ]

                   

                  I sounds like you don't have any related records as defined by your data and relationships. If so, the custom dialog should return an error code that you can look up in filemaker help to get a clue as to why that's the case.

                   

                  PS. I'm puzzled as to why the first line of your script is Go to layout ["COE"(COE]. Aren't you already on that layout when you trigger this script?

                  • 6. Re: Script for Duplicating a record with a portal
                    ninja

                    Howdy Phil,

                     

                    If I remember correctly (cant find the post), we worked on this issue before:

                     

                    Trap the ID# of the record to duplicate

                    Duplicate it

                    Trap the autoentered ID# of the new record    {now you have both ID#'s at your fingertips}

                    go back to the original with Find=ID#

                    GTRR using a layout based on the child table

                    Loop through the child records duplicating each and setting the ID# to the new ID#

                    Exit loop if FoundCount=0 or after last

                    Go back to the parent table (GTRR or GoToLayout+find)

                    show all records (optional)

                     

                    If you can find that thread, the script is written out in it...can't believe I can't find the thing.

                     

                    Edit:   Found it!      http://fm.lithium.com/t5/Using-FileMaker-Pro/Copy-Records-with-Portals/m-p/49049#M38360

                     

                    • 7. Re: Script for Duplicating a record with a portal
                      philmodjunk

                      Ninja,

                       

                      Thanks for finding the other thread.

                       

                      While the script posted here does things in a slightly different order, The first part of the script: GTRR to find the child records, return to the parent record, duplicate it, trap the ID number go back to the child records. Should work correctly. The fact that it is going off the rails somewhere, is what we are now investigating.

                       

                      Pebbles,

                       

                      the thread Ninja has posted provides and excellent alternative to the version you trying to use here.

                      • 8. Re: Script for Duplicating a record with a portal
                        ninja

                        PhilModJunk wrote:

                        While the script posted here does things in a slightly different order, The first part of the script: GTRR to find the child records, return to the parent record, duplicate it, trap the ID number go back to the child records.


                         

                        Yup.  That's why I posted...the back and forth and back and forth seemed unnecessary and higher risk of going off the rails (though you're right, it shouldn't go off the rails at all).  Trap the info you need before you go, then go and get it done.  Once over to the Child, then once back when it's accomplished.


                        • 9. Re: Script for Duplicating a record with a portal
                          philmodjunk

                          Hmm, that last post "triggers" another thing to check...

                           

                          If this is in filemaker 10 or 11, are there any layout based script triggers that may be firing during this script?

                           

                          A trigger such as OnLayoutLoad (v10) or OnLayoutEnter, OnlayoutExit (v11) could trigger a whole different script right after this script changes layouts during the GTRR or Go To Layout steps.

                           

                          You can check that by clicking the Script Triggers Tab in Layout Setup... for both layouts.

                          • 10. Re: Script for Duplicating a record with a portal
                            Pebbles

                            Thank you both for all the help.... 

                             

                            Ninja

                             I did look at that thread prior to submitting my questions but It didn't work so efficiently for me.  What i did do was it copied all the records in my parent table but only one child in the children data. It was very helpful but it did not work for my database.  

                             

                            Phil

                            In regards to your question about the relationship in my data base.  They are related by COE_ID, so everytime I enter a new record and the child data it creates the child on the children data with its own child_id and the COE_ID number from the main layout.  I did check the script triggers on each layout and they are not checked off.  

                             

                            I'm still confused... I have tried changing and adding fields to the layout but it is still duplicating the child on the original and leaving the duplicate portal blank... 

                             

                            My coworker and I decided to leave it like that for now.. and just manually enter the COE_ID on the child record for the one we are duplicating. That seemed to correct it in the main COE layout.  

                             

                            And we are looking to take a Filemaker Pro training. hopefully this would help. 

                             

                            But again thank you both very much. 

                             

                            Anna

                            • 11. Re: Script for Duplicating a record with a portal
                              philmodjunk

                              I went back and looked at your script again and spotted the problem:

                               

                              Go to Record/Request/Page [First]

                               Duplicate Record/Request

                               Set Field [COE::COE_ID[$ID]]

                               Omit Record

                               Go to Record/Request/Page [First]

                               

                              Change that line to:

                               

                              Set Field [Coe::Coe_ID ; $ID ]

                               

                              and it shoud work for you. You've placed the $ID in the repetition number box instead of clicking the second (calculated result) specify button and entering the varible name there.

                              • 12. Re: Script for Duplicating a record with a portal
                                Pebbles

                                Hello Phil, 

                                 

                                thanks for looking into that for me again... I made the change to the set field but it still did not work... not sure what exactly I am doing wrong.  we still have some time before we need to perfect that script.  we wont be duplicating anything until next school year.  but thank you soo much for all your help. Have a wonderful Easter Weekend!!!

                                 

                                Anna

                                • 13. Re: Script for Duplicating a record with a portal
                                  ninja

                                  howdy guys,

                                   

                                  Don't mean to step on toes, and I've chatted with Phil enough to know he won't take it that way, may I expound on the original concept I posted.

                                  It sounds like when Pebbles tried it, there were a couple of wrong references so it looped through the parent table instead of the related child records.

                                   

                                  From the record in COE {the parent record whose layout, based on Table COE, shows a portal of Children Data:

                                   

                                  SetVariable [ $oldID ; Value: COE_ID]  traps the old ID

                                  duplicate Record/Request                     duplicate the parent record

                                  SetVariable [$newID ; Value: COE_ID]  traps the new ID

                                  Enter Find Mode

                                  SetField [ COE_ID ; "==" & $oldID ]      goes back to the original record

                                  Set ErrorCapture [on]                             keeps the box of bits from beeping at you

                                  Perform Find

                                  GoToRelatedRecord [ show only related records; From Table:"Children Data" ; Using layout: "pick any layout based on your Table Children Data here"]

                                  Go to Record/Request/Page [First]  start at the first record in the found set

                                  Loop                                                          duplicate the children, omitting each as it is duplicated.

                                       ExitLoopIf [ Get(FoundCount) = 0 ]   quit the loop if there aren't any to copy

                                       Duplicate Record/Request                copy the child record

                                       SetField [ Children Data::COE_ID ; $newID ]   give it the new ID

                                       Omit Record                                          omit the newly made copy

                                       Go to Record/Request/Page [First]      go to the one you just copied

                                       Omit Record                                            and omit it too, you just copied it

                                  End Loop

                                  GoToLayout [ "COE" (COE) ]                      go back to the layout you started from

                                  Enter Find Mode

                                  SetField [ COE_ID ; $newID ]                     and land on the record you just made, together with it's children in the portal

                                  Perform Find

                                   

                                   

                                   

                                  Pebbles,

                                  I also notice that your original GTRR included matching the whole found set.  I don't know if you've remedied that already, but you don't want to match the found set, just the record you're trying to copy.

                                   

                                  I truly hope this helps.  If it doesn't work, please describe what it DOES do and we'll troubleshoot it together.

                                   

                                  Edit: corrected a "<" that shouldn't have been there.  Also, as I look at this script, there are a number of ways to simplify it...let's get it working first, then trim it down to the bare essentials (Example: the last find could simply be another GTRR)  But again, let's get the job done, then tidy up the edges.

                                  • 14. Re: Script for Duplicating a record with a portal
                                    Pebbles

                                    Hello Once again Ninja, 

                                     

                                    I tried the script you posted. and this is what it does and doesn't do.  

                                    It does duplicate the new parent table but still no child info in the portal.  It does not duplicate the child in the original parent or in the child table. I also have a question  on the SetField Under the loop command, the note you put next to it " give it a new ID".  Do I have to create a new ID before performing this script? 

                                    I apologize for asking so much.  As I mentioned before I am extremely new to Filemaker Pro, but I am learning as I go.  So please bare with me.  And thank you again for all your help.  

                                     

                                    Anna

                                    1 2 Previous Next