But why do you need to copy this data at all?
Given the relationship linking Transactions to Lots, anytime you need this ID from the context of a Transactions record, this relationship makes it possible to simply refer to this field in the Lots table.
Thought I explained why in 2nd to last paragraph of my post.
The ID_Seller needs to be static for the same reason the selling price needs to be static on an invoice. It can't change when the price of an item changes later.
Logically, the ID_Seller in the TRANSACTION table should be the ID_CurrentOwner in the LOTs table. To avoid mistakes, in a new transaction, I want to automatically populate TRANSACTIONS::ID_Seller with the value of LOTS::ID_CurrentOwner.
LOTS::ID_CurrentOwner is calculated as TRANSACTIONS::ID_Buyer of the most recent transaction. LOTS::ID_CurrentOwner will change so this live relationship in LOTS works OK.
But, the values for ID_Seller and ID_Buyer in TRANSACTIONS should not change.
Summing up - when I create a new transaction, I open a layout based on LOTS that contains a portal into TRANSACTIONS. The portal contains fields for DATE, ID_Seller, ID_Buyer, ID_LOT (populated automatically because of relationship). I want ID_Seller to lookup the value of LOTS::ID_CurrentOwner.
Thanks for your help.
But unlike a price. The ID code that identifies a seller is data that should never ever change as it should be a reference to a primary key in the table where this value originates.
But if you still want to copy this data, it should be something that can auto-enter via a looked up value or calculation option: Auto Fill
Thank you. To your point, yes. The ID's of the Seller and Buyer should never change in the Transaction record.
The ID of the Current Owner does change in the LOT record.
The ID's are pulled from a third table OWNERS that contain unique ID's, names, mailing addresses, etc.
When a new person buys a LOT, I add their contact info into OWNERS. Then, I create a new TRANSACTION starting from a layout based on LOTS with a portal into TRANSACTIONS. ID_Seller is the ID_CurrentOwner copied from LOTS. The ID_Buyer is picked from a value list from OWNERS and should be copied to TRANSACTIONS::ID_Buyer.
Thanks for your help.