AnsweredAssumed Answered

A Script to Find and Duplicate a given found set.

Question asked by Derrenger on May 9, 2011
Latest reply on Jan 28, 2013 by RashinAlizadeh_1

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.

Outcomes