9 Replies Latest reply on Jan 28, 2013 3:36 PM by RashinAlizadeh_1

    A Script to Find and Duplicate a given found set.

    Derrenger

      Title

      A Script to Find and Duplicate a given found set.

      Post

      I am on a mac (OS 10.6.7) using FMP 11.0v3. I am trying to create a week ending report that is date driven, I am primarily working with 3 fields in the same table.

      Table1:DateFind - global date
      Table1:DateReplace - global date
      Table1:DateActual - week ending date

      I want to set up an automated script to Find a given number of records with the same DateFind (which searches the DateActual field), then Duplicate all of those records in the found set and replace the DateActual with the DateReplace date (but only in the new Duplicate Records). This way I can Dublicate over a weeks worth of payroll entries from a prior week, and change the DateActual without having to Duplicate and update each record manually.

      I am still wrapping my head around Loops, but I'm guessing I'm going to need to utilize one or two here.

      I tried to write the following script:

      Freeze Window
      Show All Records

      Loop
         Set Variable [$DateFind ; value:Table1::Date Find] //this is the date I want to find in DateActual

         Constrain Found Set (Table1:DateActual:=$DateFind)
         Set Variable [$DateReplace ; value:Table1::Date Replace] //this is the date I want to replace in the new duplicate records

         Set Variable [$I ; Value: 0]
         Loop   
            Set Variable [$I ; value: $I + 1 ]
            Exit Loop If [$I > valuecount ( $List ) ]

            Duplicate Record/Request
            Set Field [Table1::DateActual ; $DateReplace] //this should put Date Replace in the new duplicate record
          End Loop
          Go To Next Record [next ; exit after last]
      End Loop

      But I'm clearly missing something, since it only Duplicates the first record (and changes the Table1:DateActual to Table1:DateReplace appropriately, but only once), and then keeps running in circles... Duplicating it over and over until I cancel the script entirely. What it's not doing is cycling through the found set, duplicating each individual found record and replacing the Table1:DateActual on that duplicate record with Table1:DateReplace on each new Duplicate record (while leaving the original record intact).

      Any advice or guideance at this point would be greatly appreciated.

      Thank you in advance for your time and assistance.

        • 1. Re: A Script to Find and Duplicate a given found set.
          raybaudi

          "Exit Loop If [$I > valuecount ( $List ) ]"

          Where did you define $List ?

          • 2. Re: A Script to Find and Duplicate a given found set.
            Kays

            heyy,

            since $List is not defined, ur loop is never gonna end

             

            K|Z

            • 3. Re: A Script to Find and Duplicate a given found set.
              philmodjunk

              And what is the purpose of the Outer loop?

              Since you constrain the found set with the same criteria each time around, I don't see how that will do anything useful for you.

              Also, duplicate record will change which record is the current record. In an unsorted found set like you have here, duplicate record adds the newly created duplicate to the end of the found set and this leaves you with the last record as your current record each time you duplicate a record.

              • 4. Re: A Script to Find and Duplicate a given found set.
                Derrenger

                Thanks for the input, I actually wrote my script as:

                Freeze Window
                Show All Records
                Loop

                   Set Variable [$DateFind ; value:Table1::Date Find] //this is the date I want to find in DateActual

                   Constrain Found Set (Table1:DateActual:=$DateFind)
                   Set Variable [$DateReplace ; value:Table1::Date Replace] //this is the date I want to replace in the new duplicate records

                   Set Variable [$I ; Value: 0]
                   Loop   
                      Set Variable [$I ; value: $I + 1 ]
                      Exit Loop If [$I > valuecount ( $DateReplace) ]

                      Duplicate Record/Request
                      Set Field [Table1::DateActual ; $DateReplace] //this should put Date Replace in the new duplicate record
                    End Loop
                    Go To Next Record [next ; exit after last]
                End Loop

                But this still isn't running correctly...

                • 5. Re: A Script to Find and Duplicate a given found set.
                  philmodjunk

                  Here's how I'd do it:

                  Freeze Window
                  Enter Find Mode[] clear pause check box
                  Set Field [Table1::DateActual ; Table1::Date Find ]
                  Set Error capture [on]
                  Perform Find []
                  Loop
                     Exit Loop if [ get ( FoundCount ) = 0 ]
                     Duplicate Record/Request
                     Set Field [Table1::DateActual ; Table1::DateReplace ]
                     omit record
                     go To record/request/page [first]
                     omit Record
                  End Loop
                  Enter Find Mode[]
                  Set Field [ table1::DateActual ; Table1::DateReplace ]
                  Perform Find[]
                    

                  • 6. Re: A Script to Find and Duplicate a given found set.
                    Derrenger

                    Yes, I guess I'm obviously still struggling with loops... and getting a bit loopy at the same time.

                    So I've tried modifying the script to remove the outter loop, and moved the GoToNextRecord inside the loop as such...

                    Freeze Window
                    Show All Records
                    Set Variable [$DateFind ; value:Table1::Date Find] //this is the date I want to find in DateActual
                    Constrain Found Set (Table1:DateActual:=$DateFind)
                    Set Variable [$DateReplace ; value:Table1::Date Replace] //this is the date I want to replace in the new duplicate records
                    Set Variable [$I ; Value: 0]

                       Loop   
                          Set Variable [$I ; value: $I + 1 ]
                          Exit Loop If [$I > valuecount ( $DateReplace) ]

                          Duplicate Record/Request
                          Set Field [Table1::DateActual ; $DateReplace] //this should put Date Replace in the new duplicate record
                          Go To Next Record [next ; exit after last]
                        End Loop

                    but I'm still only getting the first record to Duplicate... how do I get it to duplicate all records in the found set?

                    • 7. Re: A Script to Find and Duplicate a given found set.
                      Derrenger

                      Thanks Phil, just wrote back, please ignore based on your new post... I will test and report back!

                      • 8. Re: A Script to Find and Duplicate a given found set.
                        Derrenger

                        Phil,

                        Once again you've nailed it... I do wish my brain worked so logically, but now I see what you mean about Omitting the records after they are duplicated so as not to repeat.

                        Very clean and concise, and just what I was hoping it would do... Thanks so much (again!) for all of your help and guidance.

                        cheers!

                        • 9. Re: A Script to Find and Duplicate a given found set.
                          RashinAlizadeh_1

                               This worked great for me, however I need something else added to the script:

                               I want to script the button to automatically find certain information that the user specifies (in field called replace). Then I want that information to be replaced with the content of field (specified by user) called "year to replace". I only want part of that field (year to search) changed with "year to replace" (ie. if there is other text in that field I want it to remain there. I tried the find and replace and it did not work as I intended (maybe I did something wrong

                                     

                                    Example:

                                    I have:

                                    "Hello 2012" as initial text in a field, then

                                    dialog information that is specified by user:  a "year serach" field = 2012 and "year to replace" field = 2013

                                    I want the new result (after the scropt has run) to read "Hello 2013"

                                     

                                    Thanks!