Query regarding the sale price per item in Sale Order
Hello friends,
This query might sound very basic, but I have some doubts about it.
I am starting to develop a small sale-order system for my own food business.
-> I have a Menu table with price for each item.
-> Now I want to make SaleOrders table and a child table OrderItems.
->In OrderItems I will pick up the price of each item from the reference of Menu table (I will setup the reference in OrderItems table).
Everything is fine according to me till here. Problem starts from here. In the future if I change the price of any item in the Menu table, it will make effect (change item price) in all the historical orders as well (since there is a reference to menu table in the OrderItems table).
So how do I go about this. I do not want the price to change for items in the historical orders, thus changing the total of all the historical orders and create problem in accounting. But I do want the new orders to pick up the latest price from the Menu table always.
Please help !!
8 replies
-
The only way I can think of right now is that the rate from the Menu table will only be copied to the Rate field in OrderItems table if the it is empty. It will only be empty for the first time when the OrderItem is being created in the SaleOrder table.
This way we can change the rate in the Menu table in the future and it will not affect the historical transactions.
Please let me know if I am using the right approach !!
-
Agree with you.
You want to copy the prices from the menu table into new form. That way you can freely alter the master table prices
see very quick sample !!!!
-
Also bear in mind that when entering orders you do not have to keep opening the sub table to enter records
you can all add "copy" fields onto the master form that are identical to those on the sub form as a line input then have a script on a button to enter the data directly into the sub table...then clear those fields ready for the next item.
see image shots form one of my other databases
Main form and sub lines
then when press the ADD Product Button
Hidden fields - show!
Fill these in and press say and they are copied directly into the sub(child)form
after the SAVE button pressed
Can make life easier !!
-
But with this setup won't we have to create duplicate fields (of OrderItems table) in the main SaleOrders table ?
-
yes - its just an alternative if you want a different way to input the sub line items - like in our case we need to enter anything from 10 - 40 lines on orders. thus having the fast entry method on the main form saves opening and closeing all those lines)
my quick sample database - does not use this method
-
I REALLY like this Mel. The fields are always in the same place on screen. Super effective.
-
My God I've actually done something useful !
basically i'm a lazy sod so like the idea of pressing a button to open an order pad so to speak.
The hidden fields when activated then simply shuffle the form downwards. You need a judicious use of a SPACE element to shuffle the form down enough so it does not appear truncated at the foot.
although my 3 buttons on the form probabaly are obvious
1. Save (pass the date into the child form and LEAVE order pad filled to allow a minor edit to enter a similar product/desc
2. Save and then clear the order [pad to enter a different item/desc.
3. Simply clear the pad with no save
I have found this to be preferable than to opening a child table /enter date close open next etc.
Then I ensure that key price fields etc are on the table view to do the double click edits.
I have also included a reshuffle option to allow the entry of day say midway in the child form. when a client does the usual - you know you quoted me 20 and 40 of product X can you add in qty 30......
-
Content aside
- Status Answered
- 4 mths agoLast active
- 8Replies
- 51Views
-
4
Following