Welcome to the community, @Joshua_Parker! :grinning_face_with_big_eyes: 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 bItems]), one to record line items that are a part of sales transactions (nSales Line Items]), and one to record line items for incoming stock (cReceiving Line Items] - I don’t deal with this stuff on a daily basis, so forgive my sloppy terminology).

The pItems] 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 eReceiving Line Items] that links to the appropriate item in eItems] and indicates how many of that item were received. Similarly, when an item is sold, a record is added to eSales Line Items] linking to the item in eItems] 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.