Duplicating Records from Related Table

[Jonathan Smith]

I have a portal (in the table "Quotes") that shows related (by "QuoteID") records from another table, "QuotedItems". I want to duplicate these records only, and display them on a new table. What is the best way to go about this?

[Jonathan Smith]

It seems this is more difficult than I expected. I've read other posts that state that, when attempting to copy related records, ALL related records (not just the ones in the desired portal) with be duplicated. Is there a way to specify the exact fields to duplicate and then insert these into duplicate fields on another table?

[Jonathan Smith]

anyone?

[Inky Phil]

Hi Jonathan

Forgive me for asking but why are you duplicating this data. Why not just set up a relationship between the table that you want to see this data in and the table that it is already in?

Phil

[Jonathan Smith]

Here is the situation:

The Quotes table is accessible by both salesmen, project managers and the estimators. When the initial record is created, the original information needs to be saved. As the file is edited by the various individuals, different information is "lost" without some sort of a backup. I have contemplated exporting files as excel files, and writing a simple script to import, however, this causes conflicts with the quoteID; all of the different "versions" of the record need to have matching QuoteID's, but quoteID is an indexed serial field. If there is another way of doing this I'm definitely open to any suggestions.

[mr_vodka]

See if this helps

link

[Jonathan Smith]

ahh very nice, thanks!

[Søren Dyhr]

I have evangelized something similar to Phils message over and over, until a day when a brewer told me that he wished to have a table with recipees, which then should be duped into a production table - which as a whole should contain identical data, but since the actual need is to track slight changes in composites, wheather or not it turns into better flavoured beer, can't the fields be shared.

Reinheits angebot, dictates only 3 types of ingrediences, but you could pick different yeasts and hops types in the same brew and the same goes with the malt.

This means that 3 portals are likely to store these permutations and the exact measures of each applied. Strictly speaking are we here dealing with 3 join tables, but these can be merged into one single table with several foreignkeys....

Take a look at the scripting in my attached template i just made to show what we did, which deals with data splayed over several portals.

--sd

Beer.zip

[Jonathan Smith]

John-

I understand the idea behind the script you linked me to. One thing, though; for some reason FileMaker won't allow me to recreate that script exactly...

The "Set Field" command is only allowing me to specify a single field; the script is showing

Set Field [ Parent_ID, gNew_Parent_ID ]

How can I specify multiple fields?

[mr_vodka]

Set Field [ Parent_ID; gNew_Parent_ID ] does not represent multiple fields. It is setting the parent_ID with the value in gNew_Parent_ID.

[Jonathan Smith]

Hmm, it is duplicating the record now but it seems to be in an endless loop. I hvae to escape to stop the script, but the record is duplicated. SHould I display what I have in my 2 scripts?

[Jonathan Smith]

arg, i justed realized too that there are portals from multiple tables, setup in a tabbed layout. Im thinking I would have to set the scripts to run on each of the related tables as well? Maybe I should figure another way all together to do this... I have a feeling it may be a little bit beyond me :)

[Søren Dyhr]

justed realized too that there are portals from multiple tables

This is exactly the issue my template seeks to solve, bearing in mind that portals often displays jointable records. If they however isn't join tables doesn't it hurt to insert one in between.

It's a slight cheat to use the same table for all joins, but it sure simplifies the scripting.

--sd

[Jonathan Smith]

PARENT SCRIPT

go to related record [from table: "QuoteItems"]

duplicate record/request

set field

perform script ["duplicate_records_child" from file: "quoteitems"]

CHILD SCRIPT

unsort records

go to record/request/page [first]

loop

duplicate record/request

set field

[Jonathan Smith]

soren-

I looked at your "beer" file, and at the production script; im relatively new to filemaker, so i dont fully understand each script step.

when i run the script with the above code, it duplicates the single record but goes into what seems to be an endless loop. i have 6 tabs setup, each with a related portal, and i am attempting to duplicate the record with all of these portals duplicated as well. any suggestions?

[Søren Dyhr]

If you make a GTRR(SO) to a yet a TO via a relation sorted backwards, can you start from the last record and then ussue two Omit Records just after the dupe and then exit the loop when found set is empty, a tad faster since no fields or variables needs to be set to something ...since internal measures takes care of it.

The fault I seems to see in your script is that you need to remove both master and copy record to get a new ID for the exit.

But as such do you need to facilitate each of your portals with it's own keyset in the unified jointable. You could of choose the loop your way thru every portal, but the key to understand what I did, is the 7th join TO, where I make a multilinekey to point at every record tied to one of the portals, via an unstored calcfield...

--sd

Edited April 18, 2007 by Guest