9 Replies Latest reply on Oct 3, 2013 10:56 AM by philmodjunk

    Script to duplicate record and multiple related records

    MichaelGarner

      Summary

      Script to duplicate record and multiple related records

      Product

      FileMaker Pro

      Version

      12

      Operating system version

      MAC OS 10.8.5

      Description of the issue

      I wrote the following script to duplicate an existing record along with its related sub-records:

      Go to Related Record [Show only related records; Match found set: "Labor Hours"; Using layout: "Related Labor Hours" (Labor Hours)]
      Go to Related Record [Show only related records; Match found set: "Material Items"' Using layout: "Related Material Items" (Material Items)]
      Go to Layout ["Project Input & Detail" (Projects)]
      Duplicate Record/Request
      Set Variable [$ID; Value: Projects::_Project Ref #1]
      Go to Layout ["Related Labor Hours" (Labor Hours)]
      Loop
           Exit Loop If [Get(FoundCount) = 0]
           Go to Record/Request/Page [First]
           Duplicate Record/Request
           Set Field [Labor Hours::_Project Ref #;$ID]
           Omit Record
           Go to Record/Request/Page [First]
           Omit Record
      End Loop
      **Did same loop language for Related Material Items**
      Go to Layout ["Project Copy Layout" (Project)]
      Go to Field [Projects::Customer Ref #]
      Show Custom Dialog ["New Customer ID"; "Enter New Customer Reference #"]

      Script runs fine when I have related records in the Labor Hours and Material Items table, but if there are no records associated with the Project in the sub-tables, then the script copies all of my Labor Hour or Material Items to the duplicate Project.

      Any suggestions would be greatly appreciated.

        • 1. Re: Script to duplicate record and multiple related records
          philmodjunk

               Report An Issue is intended for Reporting Possible Software bugs. Questions on how to work with FileMaker Pro should be posted in the FM Pro Forum Section. (See tab at top of this screen.)

               Go To Related Records silently fails to do anything if there are no related records to go to. If you were using the Show Only Related Records option, you could either check for the existence of related records before the GTRR step or check for a nonzero error code returned by Get ( LastError ) immediately after the script step. But those options won't work consistently with the "match found set" option.

               I identify that situation by exploiting the fact that script will fail to change layouts if there are no related records:

               Set Variable [$LayoutName ; Get ( LayoutName ) ]
               Go to Related REcords [
               If [ Get ( LayoutName ) = $LayoutName // then there were no related records ]

               I use this specific method for checking for a change in layout name as it will continue to work correctly even if I should choose to rename a layout at a later point in time.

          • 2. Re: Script to duplicate record and multiple related records
            DavidJondreau

                 If there are no related records to go to then the Go to Related Record[] (GTRR) step fails with an error. You can check to see if there are related records before each GTRR by using If [ IsEmpty ( Labor Hours::Primary Key ) ] . You could also check for the error after the GTRR but the errors on a GTRR are a little confusing.

            • 3. Re: Script to duplicate record and multiple related records
              Fred(CH)

                    

                   

                        But those options won't work consistently with the "match found set" option.

                    

                   

                        You could also check for the error after the GTRR but the errors on a GTRR are a little confusing.

                    
                   If so, Get ( LastError ) will return 101 if only certain record were found and 401 if no record was found at all.
                    
                   Bye, Fred
              • 4. Re: Script to duplicate record and multiple related records
                philmodjunk

                     The problem is that with the "match found set" option specified, the current record could have no matching related records, but the next record down in the found set could have a set of matching related records. In that case, you get an error returned because the first record does not match even though you get a set of related records and a layout change when the script step executes.

                     And that's why I suggested using code that checks the name of the layout to see if the GTRR step actually changed layouts. wink

                • 5. Re: Script to duplicate record and multiple related records
                  Fred(CH)

                       Yes Phil, i understood your technique as well oui !

                       I just wanted to be accurate. I also have did like you, but now, i generally do with this test after the GTRR step :

                       If [ Not Get ( LastError ) Or Get ( LastError ) = 101 ]
                     # A least one record matched, the layout normally change so i continue the process…
                        

                       But maybe i missed a important detail on yours posts, due to my approximative english ? 

                       If so, please excuse me qui rougit.

                       Bye, Fred

                        

                  • 6. Re: Script to duplicate record and multiple related records
                    Markus Schneider

                         Is valid (myTO::myKeyField) can be used before an operation in the other table/TO starts

                    • 7. Re: Script to duplicate record and multiple related records
                      Fred(CH)

                           Excuse me Markus but i don't see exactly the matter of IsValid with this thread, since this thread is talking about  a search on a related table (GTRR). In the FM12 Help, they talk about many cases and one of them state :

                           IsValid(table::field) returns 0 if the related table was renamed and the relationship isn’t updated with the new filename.

                           Could you please clarify ?

                           Bye bye, Fred

                           PS: or i maybe misunderstood all this discussion... qui rougit

                      • 8. Re: Script to duplicate record and multiple related records
                        Markus Schneider

                             maybe I got it wrong - but in the original post Michael Garner said:

                             

                        Script runs fine when I have related records in the Labor Hours and Material Items table, but if there are no records associated with the Project in the sub-tables, then the script copies all of my Labor Hour or Material Items to the duplicate Project.

                              

                             So, I got it in the way that there is a problem if there are no related records.

                             -> If this is the problem, You can check with isvalid(...) if there are any related records. If not, just don't go any further in the script

                             isvalid(..) results in 1 if there are records, 0 if there are no related records

                        • 9. Re: Script to duplicate record and multiple related records
                          philmodjunk

                               Markus,

                               Yes, but he is using the "Match found set" option. To check for the existence of related records requires looping through the found set and performing this test on each and every record in the found set. wink

                               Fred,

                               Interesting idea. I ran a test cause I expected to get a 101 error if no record in the found set had a matching related record, but in that case you get 401 returned and thus your method also works.