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?
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.
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,
- 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.
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.