Parent/child relationship retention

[Jana]

My database is set up to create parent records of Orders with a child table related for Line Items. I'd like to be able to create and track new parent records of a revised Order while retaining the original Line Items of the child records in the new revised parent record.

What's the best way to approach this? Is it simply a matter of duplicating the parent record and the original child Line Items will rmain attached?

[SlimJim]

It depends how the relationship is set-up and any auto-entering that goes on when you duplicate or create a new record.

To carry the line-items through the match fields still have to match which means you cannot match on a unique serial number or any other unique key unlkess you delete the original records and re-use the key. There are other problems with this. For example if you ever prepare invoices or reports at the line-item level then you will have more than one associated order.

How often are you going to revise an order? Do you need to keep the original order for auditing purposes? Without some further information it is difficult to give advice.

[Jana]

The orders are for media insertions. And we need to keep track of the information that there has been a revision and what revision number it is, in general terms (ie. Revision 1 is for change in the size of the ad ordered, Revison 2 is for change in the date of the insertion). Often there will be no changes to an order. And, no, they would not be used for auditing purposes.

What's your take on the best approach?

Thanks

jana

[SlimJim]

On the basis of what you have said. I would suggest you use an order number which is the same for all revisions and provides the match field for the line items. To achieve this you could use a button and script to duplicate the record and have the revision number either as an editable field or set up the script to enter the Revision number for you. The line items will still be attached to the order. If you want to see what revision have been done at any given tiem you could set up a self-join via the Order number and then a portal into that will show you all the revised records for that order number.