Copy Fields from one table to another.

[Matt Leach]

Ive done some searches on copying records across tables but have been unable to do so successfully.

Im working on a recipe database that has a few tables that are related:

Recipes

Recipe_LineItem

ProductionRecords

Production_LineItem

My relationship is as follows:

Recipe_LineItem --> Recipe <-- ProductionRecords <-- Production_LineItem

A bit of information on the setup:

The Recipe layout contains a field for recipe name and a portal to Recipe_LineItem. Each Recipe_LineItem is an ingredient for that recipe.

The ProductionRecords database has a drop-down filed that contains a list of all the recipes from the Recipe table. There is also a portal to the Production_LineItem table.

What i am trying to accomplish is on the ProductionRecords table, when a recipe is selected from the drop down menu, i would like to copy all of the ingredients (Recipe_LineItems) that are associated with the Recipe into the Production_LineItem portal (one line for each ingredient)

What I'm assuming i need to do is an import from the Recipe_LineItem table into the Production_LineItem table but i am having a bit of trouble as to how to accomplish this.

I would like to do this automatically once the recipe is selected so i thought about using a script trigger OnObjectModify. I attempted an import with the following script which i put together based on looking at other posts to no avail:

If [ not IsEmpty ( ProductionRecords::Recipe )]

Go to Layout ["Recipe_LineItem" (Recipe_LineItem)]

Enter Find Mode []

Set Field [Recipe_LineItem::Recipe_ID; ProductionRecords::Recipe_ID]

Perform Find

Import Records ["database.fp7"; Add; Mac Roman]

Go to Layout [original layout]

End If

Any assistance on where i am going wrong would be greatly appreciated.

Thanks

[comment]

This part cannot work:

Enter Find Mode []

Set Field [Recipe_LineItem::Recipe_ID; ProductionRecords::Recipe_ID]

When you are in Find Mode, only global field have values. A find is actually unnecessary here - you can simply use GTRR.

See also a very similar thread here:

http://fmforums.com/forum/topic/79864-prevent-changes-from-affecting-previous-records/

[Matt Leach]

I will go through the post you referenced as it appears to be the same thing i am attempting to do.

Thanks again, you're always very helpful and i appreciate it.

[Matt Leach]

After reading through the forum you mentioned and playing around a bit, i think i'm about 90% there. I've setup the following script:

Set Variable [$ProdID; Value:ProductionRecords::pk_Production_ID]

Freeze Window

GTRR [From table; "Recipe_LineItems"; Using layout: "Recipe_LineItems" (Recipe_LineItems)]

Go To Record / Request / Page [First]

Loop

Set Variable [$Ingredient; Value:Recipe_LineItem::Ingredient_Name]

Set Variable [$Amt; Value:Recipe_LineItem::Amount]

Go To Layou ["Production_LineItem" (Production_LineITem)]

New Record / Request

Set Field [Production_LineItem::pk_Production_ID; $ProdID]

Set Field [Production_LineItem::Ingredient_Name; $Ingredient]

Set Field [Production_LineItem::Amount; $Amt]

Commit Record

GTRR [From table; "Recipe_LineItems"; Using layout: "Recipe_LineItems" (Recipe_LineItems)]

Go To Record / Request / Page [Next, Exit after last]

End Loop

Go To Layout ["Production Records" (Production Records")]

Refresh Window

Lets say that i have 2 related records in the Recipe_LineItem table, it will create a record in the Production_LineItem table for the first ingredient, then it will create a record for the second ingredient until infinity unless i escape out of the script.

I'm assuming that i need to somehow fine out the count of related items and then tell the script to look x amount of times or decrease the count after each loop but i'm not 100% sure how to move forward.

Also, im pretty sure this is not the best way to accomplish this so any input on editing the script to work more efficiently would be appreciated as well.

Thanks

[Fitch]

That's not a super-efficient script, but it will work if you take out that last GTRR. Instead, use Go to Layout. That will return you to the Recipe_LineItems found set and record you were on.

[comment]

I have posted a demo file in the other thread - see if it makes thing clearer.

[Matt Leach]

Are you referring to this tread?

http://fmforums.com/...evious-records/

--- Disregard, i found the sample file