I am working on a system to manage orders and deliveries. I have built the Master Detail applications to accept orders from customers. I now need to create delivery tags for those orders. One issue I have is that Orders may be entered for a longer time frame that the deliveries. For example the customer might order 100 widgets to be delivered in four separate deliveries (one month apart).
I have the orders in my order detail table and would like to use those quantities to fill the delivery application. Then, using the notes, the delivery amount would be adjusted to only account for those being delivered on this particular delivery (e.g. 25 of the original 100). In the database the original order will be 100, the delivery quantity will be 25 and the “on-order” would now be 75 (100-25).
I am thinking I could create a view that would calculate the “on-order” values and use that to populate the application grid, (I plan to have a delivery header as the Master). The use would then adjust the values from the view and then using a button the adjusted quantities would be inserted in the deliveryDetail table in my database.
Any thought son better ways to approach this? Thank!