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?