Help

Re: Raw material inventory tracking

785 0
cancel
Showing results for 
Search instead for 
Did you mean: 
lars_b
4 - Data Explorer
4 - Data Explorer

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:
Skærmbillede_072822_114221_PM

In Table 2, we have our material inventory
Skærmbillede_072822_114515_PM

In Table 3, we are determining how much material each line item needs
Skærmbillede_072822_114749_PM

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
Skærmbillede_072822_115451_PM

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

2 Replies 2
Russell_Findlay
8 - Airtable Astronomer
8 - Airtable Astronomer

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.

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