Hello, I have been building out an inventory system that shows all products, the amount of inventory and the price per product. Within the base, this inventory table is used to link to a transaction table that allows you to generate a transaction with multiple products with a total value. Following this, the transactions table links to an invoicing table where you select the transactions that were made and puts it all into an invoice with use of the page designer app. This process works fairly well, I’m not super happy with it but it works for what it needs to do. Where my issue arises is that we are constantly changing pricing on parts we sell and I cant change the pricing without changing past records. I cant change the past records because my company wants to use them to track all past sales. I set up a table to put all inventory received into the system. In the receiving table, you input the item and the price that you want to sell the product at now but I don’t know how to figure out how to set price on past invoices so they don’t change when the new price is inputted. included is a video clip and photos so you can see how it functions.
Attached is a video link and photos! Video of the base