0

How to maintain rates in old transactions

 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.

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 !!

Please help !!

7 replies

null
    • John_Halls
    • 4 mths ago
    • Reported - view

    Hi  Keep a reference between OrderItems and Menu so that you can keep track of sales of each item historically but create a price field in OrderItems and in the Trigger after update section of this reference put something like this

    Price := Menu.Price

    Base all your calculations on this OrderItems.Price field.

    I'd also save the description, as these often change over time.

    I have written dozens of ordering / invoicing / ERP systems so please feel free to ask any questions on this subject.

    Regards John

      • Database App Developer
      • vermau81
      • 4 mths ago
      • Reported - view

       Thank you for your reply. I was going to do the same but just confused that when I change the price in Menu say next year, and I open the SaleOrders table, it will pickup the latest price from the Menu table and update all the transactions for that OrderItem in all SaleOrders. That's why I thought ut that I will keep an internal field.

      In "Trigger after update" no matter what I write in the Rate field on OrderItem, as soon as I change the focus from the field, it will copy the latest price from the Menu table. Sometimes you know you give special rate to some customers...

      So I feel I have to lock the field for writing once the SaleOrder is closed and payment is made by the customer so that no-one can edit/update the price of any OrderItem in old SaleOrders

      • John_Halls
      • 4 mths ago
      • Reported - view

       yes, but only once. When you have a Menu item linked to a OrderItem you would need to remove the link and re-assign the Menu item for it to revert, which would be unlikely or deliberate. 

      • Database App Developer
      • vermau81
      • 4 mths ago
      • Reported - view

       I am not sure if I am correct or not, but I am only going to have a link for the Item name from the menu. For the price I will use a text field that will autofill by picking the price from the Menu table for the respective item

    • John_Halls
    • 4 mths ago
    • Reported - view

     Yes, that sounds correct although the link links the whole record so you can see any of the fields if you want to.

      • Database App Developer
      • vermau81
      • 4 mths ago
      • Reported - view

       I thought over it and I think I have a solution.

      1. Reference field to show Item Name only

      2. Formula fields to autofill the Rate. Formula fields are non editable. Autofill the rate only in 1 condition.. If the formula field is empty.

      3. Changing the rate of any item will not be allowed by the system as it is against the business logic. Instead do adjustment in the total SaleOrder (-ve or +ve) whatever.

      This way even after changing the rates in the Menu Table when we open previous SaleOrders, it will not change the Rate in the OrderItem (formula field) since it will not be empty. Hence it will retain the old value in it.

      No need to lock the formula field, it is already locked for editing. I think this should work..

      Thank you for your advice John and thank you for your offer to help in this regard. I will definitely contact you in case I need help.

      Thanks,

    • Mel_Charles
    • 4 mths ago
    • Reported - view

    see comment in your other thread

Login to reply

Content aside

  • 4 mths agoLast active
  • 7Replies
  • 52Views
  • 3 Following