If Function and Lookup
I am creating a point of sale database for a school cafeteria. It is very much like an invoice database. There are three possible prices for lunch based on a student's meal status. Meal status is stored in the students table. Price is stored in the items table. There are three price fields in the items table (price_1, price_2, and price_3).
The sales table and sales_lines tables collect the lunch sales information (much like invoices and invoice_lines). I initially created a lookup for the price field on the sales_lines tables from the price field of the items table, then I realized there were three possible prices based on the field in the students table. So, I created an If calculation station, using lookup to return price_1, price_2, or price_3 based on the meal status in the student table. That works just fine. Unless prices are changed in the item table. Then each previous sale_lines record is changed to have the updated price. I wanted the original price from the time the line was created to be saved in the sale_lines table as that was the price on that day, but if the price of lunches is changed, then they are changed in the lines table records for all recrods, not just those created from the price change forward.
I tried to tell the price field in the sales_lines table to store the data, but it says that is not a valid option.
Please help if you can.