Hi, I'm sure I'm being a bit stupid, I hope that someone can help.

I have 4 tables "estimate" "orders" "Delivery Notes" and "invoice"

I need to have many estimates but not all estimates become Orders, 1 have resolved this by using this script:-

set field [orders::__fkEstimateID = Estimate:: _pkEstimateID

Which gives a unique order record related to just one specific estimate record

My problem is that now I have to take that order information including all data for the related estimate table and show it in multiple delivery notes (i.e. each order may have many delivery notes relating to just one order) 

I think the Invoice should be more straight forward as each order has just one invoice.

Thank you 

ron

 

Just use the UUID from the Estimate for that of the ORDER as it is a 1:1 once the the estimate is accepted you move the data in to the Order.

In practice I would create a JSON array of the Estimate / Estimate Items / and then in a script extract this array into appropriate Order / Order Item records from there you can add or remove items to the order that perhaps that have changed from Estimate (or insert your business rules here) - if they share the same UUID they would naturally align with a separate occurrence of the Notes - you wouldn't have to "move" or create duplicate notes.

This same logic could apply to the invoice  - it may seem duplicitous but each instrument (document) is a snapshot record that could have other items such as shipping fees or processing fees that get added at each state -  applies credits / or payments to reflect amounts to pay.