I was working at the optical shop, where I set up the entire Airtable for any form of tracking our customers such as dioptries, eyewear they chose, and complex calculation so you can add the info right in front of th customers and tell them the final price. The table also got own portfolio for eyewear and its own price and specification. It was great but the only thing I struggled with was changing the price of the product (in portfolio) that was sold and reordered by me again with the higher price. If I wanted to change original price (in portfolio), it will be changed in all of customers that purchased specific product.
My question: Is it possible to somehow change the price without adding the same product with a different price? for example lock the record so it does not change or any other options ?
Oct 11, 202307:41 AM - edited Oct 11, 202307:43 AM
Hi Pannfresh! If I'm understanding your question correctly, you're saying that in your base you have a table that tracks purchases and another table (portfolio) that tracks prices; when you update the price of an item in your portfolio table to reflect a new price, it will back-edit purchases in your purchases table with that new price, even though that is not historically correct.
If this is accurate, one way I could see you adjusting this without too much hassle would be by creating a separate base as a sort of "purchasing archive." In that new base, create a synced table with your purchasing table in your main base, and set the rules such that records deleted in your main base are preserved in your archive base. At certain intervals (such as a certain calendar time or maybe after a sale is completed) delete the purchase record from your main base - you could set up an automation to do this if you choose. This will bake the price of the historical purchase into its record information in your archive base, so that it won't be affected by future price changes.
Let me know if this was helpful, or if I'm off-base in how you've set up your tables!