The invoicing starter solution has a number of significant flaws when it comes to managing inventory levels. There's code there that's supposed to update the inventory but it's very vulnerable to user errors messing up the inventory counts.
Another approach is to use your LineItems table as an "inventory log" where sales reduce your inventory totals and shipments recieved increase them. This approach can also be used to handle inventory shipments and customer returns that return product to inventory.
What you do, is add some fields to LineItems that turns it into a "ledger" where one field is used to remove items from inventory and different field is used to add them. A running total of the difference of these two fields tells you your current inventory for each item in stock.
Your table would need at least these fields to function as an inventory log (Keep the current fields so that it still works for logging item sales):
TransDate : Date (Can look up a date from Invoices for sales entries)
InvoiceID : Number (blank for non sales entries)
ProductID : Number
TransType : text ( sales, shipment, return, shrinkage, etc.)
QtyIn : Number
QtyOut : Number (this is the item quantity field already used in your portal on the invoices layout)
cBal : QtyIn - QtyOut
sTotal : Summary, Total of cBal, Restart totals when grouped by ProductID
With this structure, you can create a list layout based on LineItems. If your sort your records by ProductID, then TransDate, you'll not only see the inventory levels for each Product, you'll be able to see how they've changed over time. You'd log non sales inventory changes directly on this layout such as logging in the receipt of a new shipment of products.
Ok i am very very new to file maker pro. You solution sounds great but unfortunately i fully understand how to go about doing this. Do you have a screenshot by chance. Sry for bein such a noob
Sorry, but I don't have a screen shot, nor a demo file of this particular method...