Hi, I could use some advice on the following use case. One base, four tables:
- Products
- Orders
- Order Line Items
- Out of Stock Products
Orders are linked to its Order Line Items, and each Order Line Item record is linked to 1 Product record from the Products table. Additional lookup data of the Product record is gathered in both the Orders and the Order Line Items table.
Now here is the main issue: Product records are automatically (via scripting) moved to the Out of Stock Products table once they hit a quantity of 0 (and let’s assume we manually adjust the quantity of these product records). Once a product record is moved though, the link between the Product record and Order Line Item record is removed, causing all the additional lookup and summed up data of my final Order data to be flawed.
Somehow, I need to retain that product data in the order.
I’m looking for any type of suggestion to deal with this use case to retain the product data in the orders.
Could be going anywhere – from a scripting solution (eg to make the Order Line Item look for the same product in Out of Stock and create a new link), to a different setup of my base.
I am experienced in crafting custom Airtable scripts and extensions, so feel free to delve into that realm.
Thanks in advance!
EDIT: keeping in stock and out of stock products in one table and thus not moving any records at all is not an option (for various unimportant reasons).