Help with invoicing and inventory automation.
Still ironing out some tweaks to this system I'm building.
I have an Inventory table containing details about the items. This includes a stock level and a reorder level.
My invoice table contains a portal to my invoicelineitem table as the standard.
I would like to start an invoice and add an item with its QTY. then i would like that QTY to automatically deduct from the inventory stock. As simple as this sounds i cannot figure out a way because when we buy in more of the stock i would like to add it on to the stock as well.
Whats the best way to do this??