Jul 28, 2022 03:00 PM
Can anyone guide me, in how to overcome this obstacle I keep banging my head against?
Let me make a very simple example: The Tailor Shop
Table 1 - Incoming orders
Table 2 - Inventory
Table 3 - Material usage
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 hope this makes sense, sorry for the long post!
Jul 28, 2022 04:09 PM
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.
Jul 28, 2022 08:17 PM
Hmm, what if you had the following two fields in
[Quantity Snapshot value] - [Amount of Fabric used so far in Table 1]
Then you can just copy the value from
Quantity and paste it into
Quantity Snapshot, and then delete everything from
Table 1 but still have an accurate inventory count