First, I recommend these tables and relationships to manage your invoices:
A portal to LineItems on your Invoices layout is used to list individual items sold. This separate related table is crucial to setting up a system for managing sales and inventory that is flexible and easy to manage both as a user and as a developer.
You may want to examine this demo file created by Comment: http://fmforums.com/forum/showpost.php?post/309136/
Phil, I took a screenshot of my relationship, which I believe the issue lies can you assist. Thanks.
You may find this article helpful: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/
It can help you get a better handle on the "spider web" you have here....
Should items be instantly removed from inventory when they are listed on an invoice?
Should they be immediately added to inventory when listed in a "receiving" record?
The answers depend on your business practices.
Assuming the answer is yes, I'd use a single table for both the Line Items and Receiving Line Items tables. In place of one Quantity field, you define two: QtyIn for items received and any other transactions that increase your inventory. and QtyOut for items sold and any other transactions that reduce inventory.
Then define this calculation field:cBal as
QtyIn - QtyOut
and a summary field: sBalance as the total of cBal.
If you set up such a summary field as a running total, with the restart totals option, you can pull up all your line items records in a list or table view layout, sort them by product and see not only the current inventory for each item, but how they change over time--which can help you fine tune your re-order points.
To see the current inventory counts on your invoice and receiving layouts, there are several ways to do that with this setup. I suggest you add a number field to Products named CurrentInventory.
Use this script to update this field every time a new record is added/changed or deleted to/from the line items table:
Go To Related Record [Show only related records; From table: Related Products; Using layout: "Products" (Related Products)]
Set Field [Related Products::CurrentInventory ; Line Items::sBalance]
Go to Layout [original layout]
Then you can add the CurrentInventory field from Related Products to the portal rows where you list Line Item records.
(You can also define a calculation field in Related Products that computes this total, but this can lead to slow screen updates once you have a very large number of line item records in your table.)