Table 1 is integrated with our online store (via Zapier for example), and we are fetching all line items from orders. So, it could look like this:
In Table 2, we have our material inventory
In Table 3, we are determining how much material each line item needs
So far so good, but here comes my problem…
I know how to do the calculations, to basically subtract the amount of fabric used for the ‘Red dress’ and ‘Blue dress’ in Table 2.
So with 1 x ‘Red dress’ ordered, and 2 x ‘Blue dress’, that should leave our Table 2 at
But, let’s say that I run a very busy tailor shop with hundreds of orders each week. My Table 1 will quickly become very big with all the incoming orders.
But… If I then decide to delete all the orders that have the status “complete”, my inventory (Table 2) will no longer be correct since the Quantity column is calculated dynamically, (not shown in my examples to keep it simple).
So, how do I deal with this situation, so that I don’t have to keep all my line items in Table 1 forever?
I think the simple answer to your question is to use automations - and to ensure that your tables are linked. - rather than formulae.
So that when you complete the production of a red dress your automation updates a numerical field in your material inventory by subtracting the quantity is f each material required.
Trigger = when order marked complete
Action - find find record (via material used)
Update record (id of above) - change field of inventory by the amount of material used in order (a calculated field from order via a look up field on product with the calculation of number of items x material usage per item.).
Note one way of updating is to have a latest change field - update this and then have a formula that calculates updated inventory and have a further automation that copied that calculation into the current inventory.
I know this is a little high level but hope you find it useful.