Auto update Inventory base with activity from another base

[Peter_Martens]

Very new to Airtable and so far quite impressed with ease of use

I am trying to set up a system for tracking product movement in a food processing environment. We currently have a “Master Log” which defines every lot that comes in with details on when, from who, where it was stored on receipt, if it was moved prior to processing when and to where, etc.

I would like to be able to track the history of each storage silo for audit trail.

What I am thinking is to set up a base for each silo and then somehow link the master log to those individual bases so that when something is received and marked as entering “Silo S1” it shows up in the Silo S1 base with quantity and date, when it is later moved from “Silo S1” to “Hopper P1” that shows as a debit from “Silo S1” in that log and a credit in the “Hopper P1” log. When it is processed it shows as a debit from “Hopper P1” and a credit to “Warehouse”

I think this would have some things in common with an inventory management system but having the records updated by actions in the master is what im havign trouble figuring out

Thank you for any and all advice

[Kamille_Parks]

Do you need a separate base for each Silo? How many Silos do you have, and how often do things move between Silos?

Based on first reading it sounds like you could do this with one base, two tables, and one View in the “Master Log” table per “Silo”. Can you post a copy of your “Master Log” base?

[Peter_Martens]

After thinking this over longer I think that you are correct, a single base will do the job with 1 table per silo or hopper. We have 4 storage silos, 2 process hopper bins, and a substantial amount of warehouse space in 2 general locations.

Materials move between storage silos and process hoppers daily so there is a fair amount of movement.

Our master log currently has some customer specific records in it but I will try to duplicate it as a blank with example data and then post that.

We currently are maintaining a paper job sheet that defines customer data, job#, lot#, work to be completed, all pertinent data about arrival, storage, movements, machines used, packaging, and ship date.

The resulting data is stored in several google sheets and blank forms and I have long wanted a unified linked system to make audit reviews more transparent and straightforward so that is my goal with this project