A Script to Find and Duplicate a given found set.
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:
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]
Set Variable [$I ; value: $I + 1 ]
Exit Loop If [$I > valuecount ( $List ) ]
Set Field [Table1::DateActual ; $DateReplace] //this should put Date Replace in the new duplicate record
Go To Next Record [next ; exit after last]
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.