You'll need to describe the relationships between your three tables. Also tell us how your layout and portal is setup in terms of this relationship.
I'm attaching the DB relationships. The idea is to draw data from 'Furniture' and 'Parts' and make copies in 'Run' and 'Run parts' where it can be modified. The layout contains all the fields from 'Run' and a portal with the fields in 'Parts' I made Run::desriprion a lookup field and that works fine (select a model from furniture and it's description ends up in Run) The portal displays the correct records from Parts but nothing I do makes them end up in Run parts. I tried makinf the fields in 'Run parts' lookup fields but not even the first occurence works. I tried using a button which executed a script containing SetField's for each field in run parts but, nada. This is my first FP project and I'm stumped.
I have to wonder it you need to copy this data from table to table at all.
It looks like you need this relationship:
Parts::PartID = RunPartsByID::PartID (RunPartsByID is a new occurrence of the RunParts table.)
Then fields in RunParts can look up data from parts when you copy over the PartID (should be an auto-entered serial number) from each record in Parts to a new record in RunParts. Here's a possible script you can use with that relationship:
#From the Furniture layout:
If [Not IsEmpty ( Parts::Model Number ) ]
Go To Related Record [Show only related records; From table: Parts; Using layout: "Parts" (Parts)]
Set Variable[$Model ; value: Parts::Model Number]
Set Variable [$PartID ; value: Parts::PartID]
Go to Layout [Run Parts]
Set field [Run Parts::Model Number ; $Model]
Set Field [Run Parts::PartID ; $PartID]
Go to Layout [Parts]
Go to Record/request/page [next ; exit after last]
Go to layout [original layout]
Show custom dialog ["There are no parts listed for this model."]
Since you can get a new Run record to work, I haven't addressed that part here, just the process of replicating the list of related parts in the RunParts table--which could very well be an unecessary part of your process. A portal to Parts placed on your Run table, for example, would list all the parts with no scripting to duplicate parts records--just to give one example.
Thanks for the input. The reason it is necessary to copy the records from parts to run parts is that parts contains standard configurations. These are typically modified in some way when built. So, make a copy, modify as needed, purge after built. I sort-of see what you are suggesting with the partid scheme. I don't see why the same thing could not be done using model number which is currently the primary key. i mostly can't figure out why the lookup field designation doesn't work. That seems like the cleanest solution. If that can't be made to work, I'd like to be able to commit the records drawn from parts into the portal to the run parts table, I just don't know how to do that.
Thanks again for your help.
Model number uniquely identifies the model, that's why this works for the description field in Run. It does not uniquely identify each record in Parts. An auto-entered serial number in the parts table will uniquely identify each part record. You need a one to one match between the new record in RunParts and an existing record in Parts before the looked up value field option will work. Please note that a looked up value field option would be able to copy over data via the partID based relationship once the newly created record receives a PartID value.