How to change inventory product cost, without affecting past transactions?

[Leslie_Maynard]

Hello.
I’m new here and so far I’m quite impressed with all the possibilities Airtable offers. I searched all the help topics and community topics for the answer to this question, but I may not be wording it correctly.
I have set up a Product Inventory and Sales base for my fledgling business this past few week, with no problems. Yesterday I received a notice of price increases from one of my mfrs to take effect 5/1/17. When i change to the updated price, the change affects all transaction from the past. How do i input these price changes to take affect only on and after 5/1?
Maybe i should create an additional column and use updated pricing?
Thank you.

[Emmett_Nicholas]

If you can describe your current schema (i.e. field names + types), it’d be easier to make suggestions, but:

One simple option is to add a “Price” currency field to each record, rather than including the price directly in your formula.

To avoid having to repeat the same price over and over in every record (risking error), a better option may be:

• Add a new “Price history” table with a Price currency field, as well as a primary Date field, representing the time at which this price went into effect.
• Then, you inventory table could include a “link” to a Price history record, as well as a Lookup field with the price, allowing you to reference the Lookup value in your formula.

[Leslie_Maynard]

I dont know what your’e asking so Ill do my best to reply…
I have a base that is Product Inventory & Sales
The tables are Product Inventory, Purchase Orders, Sales Orders.
I have not changed anything from the original Base besides adding Product, a single Mfr, and prices -wholesale and retail.
I began using Air table last week, it seemed very simple. I logged all purchases for the year (same prices). Then Friday i receive a new price list from the mfr, this includes Wholesale and suggested Retail pricing to take effect on 5/1. There does not seem to be a percentage increase across the board, not all products were affected, so a simple formula for the table will not work.
When i change the price of one product on the Product Inventory Table, it changes all of the previous records. So even product purchased and sold in January, now has the 5/1 pricing.
I believe that i have answered all information.
To respond to your suggestions,

1. there is already a Price currency field on the product inventory in the original base. It is not possible to input a different price without effecting prior transactions.
   2)Created a new Price History table with Primary Date field, Product field, and a Price Currency field.
   Anywhere i link Price History table and Lookup ends up with complete errors. I’ve watch the videos and read everything possible. Im not understanding. Any other suggestions would be appreciated. Thank you.

[Mark_Marsiglio1]

One option, depending on your workflow, would be to add new field to your Purchase Orders and Sales Orders bases.

I imagine you have something like this now:
Item Price x Quantity = Total Calculated Price

If you add a new column called “Actual Sold Price” which is a Currency field with no formula, you would copy and past the “Total Calculated Price” field’s contents into the “Actual Sold Price” field to store it in a way that won’t be affected by the changes in Item Price as they are updated.

Item Price x Quantity = Total Calculated Price | Actual Sold Price

Use the new field to total up the line items in the order and the Total Calculated Price becomes just a calculator to help determine the sold price. This would be useful when you discount something also.

[Federico_Ades]

Here in Argentina is common to change prices every week so I had this problem at the start of my first steps in airtable.
At first, there were any feature that could do this with out scripts, but with the automatization you can do this without any problem.
Imagine, for example, that you need to have a static numbers of all the sales you had today.
First create a field where you can put that number (for example, Product profit). This number must stay static with the value that has in the moment the producto o services are in possession of the client.
You may have a Single Option field where you can put “Finished” so you know when the transaction is over (at least that one).
You have to create the automatization where when the conditions of Field = Finished, that sent a sign to put the dynamic value in the static value field.