When you create an Order, the product number should be related to the product number in your inventory so that you can see what is left in your inventory. When you finalize the order (e.g., make it into an invoice and ship the units), then those quantities get removed from inventory. If you don't want to use a relationship with product back to the inventory table, you can alternatively use an ExecuteSQL function to verify the quantity of a product you are ordering to make sure you have it in inventory.
Ken told he is using FMP v11 that doesn't have SQL Function. This is brand new function of 12.
A side of this, taylorshare is right. Considering this few fields :
Invoice Item::Product ID
Invoice Item::Product Detail
Create a relationship between your Invoice Item table and Product table, based on both Product ID. Then, you can create a new calculation Field on your Products table, Named for instance Product::Left, that perform following formmula : Sum ( Invoice Item::Quantity ).
I hope this could help you...