What's the Best Practice for Duplicating Records?

[Myron S.]

Hi there,

I'm trying to figure out the cleanest way to create a set of new records in one table based on the found set in another table.

I have a table containing TEMPLATE records, or records that are used for the sole purpose of populating new records in another table.

From a FOUND SET of records in the TEMPLATE table (3-20 records at any given time), I need to create an equal number of new identical records in a DESTINATION table.

What would be the best way to do this? Is there a way to reach out from the DESTINATION table and step through the found set or does it have to be performed from the context of the TEMPLATE table? I'm hoping to get some ideas on the best practice before I start writing a script.

Any help would be greatly appreciated!

[mr_vodka]

You may find this post very helpful.

[Søren Dyhr]

Perhaps it important to point the crutial issue here, it is wheter the found set is supposed to be historic data, say lines in a an invoice holding a snapshot of the price levels at the very moment the document was issued ...that is exact the same distinctions one should make between the use lookups and unstored calc's or values pulled from a relation away (filemakers 2NF).

--sd

[Myron S.]

Thank you both for your replies. My case is as you described, Soren, whereby a particular single invoice order needs to automatically generate several line items, each being a snapshot of the pricing of the line item at the time the order is created.

Under these circumstances, would you recommend an export of the found set? If so, is it possible to export directly to a table in the same file?

Or Perhaps you can suggest a more elegant way to create these new line item records.

[Søren Dyhr]

Yes import internal between tables is better, take a look at this:

http://www.dwdataconcepts.com/dl/tw/compinv2.ZIP

...although it deal with set or samples. But again does it depend on how fast it needs to be. If the system is served via IWP is imports between tables grayed out, so you have to do it more algorithmic, say like I did in my template Renewal.zip from this thread:

http://www.fmforums.com/forum/showtopic.php?tid/175845/post/228545/hl//

Where the found set is based on an earlier invoice....

--sd