Raw material inventory tracking

[lars_b]

Hello everyone,

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!

Thanks!

–
Lars

[Russell_Findlay]

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.

So

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.

[TheTimeSavingCo]

Hmm, what if you had the following two fields in Table 2:

1. Quantity Snapshot
   • Number field
2. Quantity
   • Formula field, where the formula is similar to the original one you created, but we use the value from Quantity Snapshot instead: [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