Adjusting Inventory for Physical Inventory Counts

We are building out an inventory control system through Airtable. I’ve found examples and been able to build out the functionality to have the inventory adjust up and down based on receiving, production runs, and shipping. However, I cannot figure out how to incorporate physical inventory counts into the current inventory number.

Example of what we would like to do:

THIS PART WE HAVE BUILT:
May 31 Starting Physical Inventory Count of Item A = 100
Throughout June, 30 Item A units were received
Throughout June, 40 Item A units were shipped
June 30 calculated inventory of Item A = 90

PART WE NEED HELP ON:
June 30 physical inventory count of Item A = 85 (5 were lost to waste, etc.)

What is the best way to enter this new physical inventory so that items received / shipped before June 30th are not adjusted from it and ONLY items shipped / received after June 30th until the next physical inventory count are deducted from the June 30th physical inventory count?

Has anyone built an inventory tracking module that has this capability for manufacturing?

Thanks!

Welcome to the community, @Joshua_Parker! :smiley: I haven’t worked with inventory tracking before, but I’ve got some ideas to toss out anyway if you don’t mind. Before doing that, though, it would greatly help to know more about the design of your base (without giving away any proprietary info, of course) to know if these ideas even work with your current structure. Can you share more about the number and names of the tables you’re using, how the fields in those tables are set up, etc?

Hello Community!

It looks like Joshua doesn’t need the tips any more, but I’m exactly at that point.

@Justin_Barrett I would love to hear those ideas you have in mind. Don’t worry about my current design as I built it just for testing, I will rebuild from scratch once I figure this issue out, so I will use the optimum design.

Thank you and all the best for 2021!!

Here’s the rough outline. The core tracking part pretty much boils down to three tables: one to record all items that are available (I’ll call this table [Items]), one to record line items that are a part of sales transactions ([Sales Line Items]), and one to record line items for incoming stock ([Receiving Line Items] - I don’t deal with this stuff on a daily basis, so forgive my sloppy terminology).

The [Items] table is only used to record the items themselves: names, prices, descriptions, etc. When more of a given item comes in, a record is added to [Receiving Line Items] that links to the appropriate item in [Items] and indicates how many of that item were received. Similarly, when an item is sold, a record is added to [Sales Line Items] linking to the item in [Items] and indicating how many were sold. Rollup fields in the “Items” table collect and tally the quantities from the other two tables based on those links, and a formula field subtracts the number sold from the number received to indicate how many are in stock.