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


#1

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.


#2

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.

#3

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.

#4

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.