Duplicate entire found set

[kmtenor]

Hello, all.

I need to find some way to duplicate an entire found set of records, changing only the foreign key that assigns the record set to its parent record along the way. Here's the scenario:

I work for a large sawmill producing pine boards. Every month, our mill managers use a complicated set of Excel files to determine what their mill outputs will be for the upcoming month. They work primarily with three lists: a list of what they intend to produce, a list of that production by percentage (based on historical averages), and a list of the value of those products (also based on history). The list of intended production calculates volumes based on the list of historical averages.

We're trying to simplify their lives by using Filemaker Pro. We have a proof of concept that shows we can do the necessary calculations to generate volumes based on percentages by simply entering two numbers into a master file (daily output and number of production days). I've successfully imported this month's percentage data, and everything lines up perfectly from Filemaker to Excel.

When planning for May begins, we want our managers to simply go in to the database and COPY the previous month's data into a new set that they can manipulate. The idea is to preserve the previous month's budget, so that we can use it for performance evaluations (how close did we come to budget, etc).

I cannot find an easy way within Filemaker to duplicate a found set of records back into the same data table. Right now, I'm working just with the historical percentages - I figure if I can get this working there, I will be able to duplicate the process for the other two tables. Here's the process I want a manager to be able to use:

1. Click a button "copy previous history"

2. Select the history to be copied

3. Click "duplicate"

4. Be shown the duplicated history, with "Copy" appended to the previously used Description

Here's how I see the pseudo-code:

Go to Layout ("Select History")

set variable $selected_history

go to table history_headers

find $selected_history

set variable $selected_history_description = history_description

add new record to history_headers

set field history_description = $selected_history_description & " Copy"

set variable $new_history_pk = history_pk

go to table history_details

enter find mode

set history_fk = $selected_history

perform find

loop

copy record

set copied record history_fk = $new_history_pk

commit new record

next record

go to layout "History display"

For the most part, it's the copying of the records that has me stumped. The only solutions I can see are:

1. Export the found set to a file, and re-import the file, performing a "replace field contents" after the import to re-set the foreign key

2. Copy each field individually (though I still haven't thought this through entirely)

What frustrates me the most is that to do this type of operation in a more traditional RDBMS setting (I have a strong background in LAMP website development) is very simple:

insert into history (fieldlist) select (fieldlist) from history where history_fk = $selected_history

So, how do I do the same, seemingly simple operation, in Filemaker? What am I overlooking?

Any help is greatly appreciated!

-Kevin

P.S. I'm hoping to not post the file, since the data I've entered is "real", and I consider it confidential. If we determine that showing my code is essential to a solution, I'll do some work on the data to make it somewhat less "real"

[AudioFreak]

I think the easiest way I can think of offhand would be to have a storage table to hold the records you wish to duplicate.

Once you have the found set you would like to duplicate just have a script something along the lines of:

Go to Layout[storage Table]

Import Records[Table with the records you wish to duplicate]

Perform Script[import script in the original table]

Once you have the new records in the original table you will be working with only the imported records. Then you can just add to the import script a loop that goes through the founds set changing the field you would like changed using the Set Field script step.

That's basically how I would handle it as it would all happen so fast the user would not see anything other than the new set of duplicated records.

HTH

Michael