Need Help With Inventory Count and Management
i work for an antiques dealer. three years ago, i set up an Inventory file, a Contacts file and a Line Item file, which are related. we do not create invoices with filemaker (or on a computer at all) -- our actual customer invoices are hand-written. (please don't laugh or ask why!) so we actually enter the information for each item sold into the Line Item file instead of an Invoice file. the Line Item file is named "Invoices" (just to make this more confusing for you) and uses lookups to fill in information from the Inventory file and the Contacts file.
the Inventory file is our main resource, and is used to track lots of information (how many pieces of an item were purchased ("Amount Purchased"), where and when something was purchased, whether it is at another location, additional costs such as restoration, etc). this file basically tracks information with simple calculations. so far, so good.
75-80% of our items are one-of-a-kind (one in/one out) but 20-25% are multiples, so there could be 50 of one item under one item ID, which means that theoretically, 50 people could purchase one each of the same item. also, people return things sometimes, and we keep a record of that also. (one in/one out/one back in) sale information (who purchased, invoice number, date purchased, price, discounts, shipping cost, number of pieces sold and/or returned, etc.) is entered in the Line Item/Invoices file.
i have a portal set up on each Inventory record that shows who purchased an item, when it was purchased, how many were purchased and the invoice number - information from the Line Item/Invoices file. this is just fine.
BUT i need the Inventory file to keep track of the current inventory of each item and i am having trouble with this.
in the Line Item/Invoices file, i created a sub-summary part (when sorted by the item ID) to get the total number of that particular item sold (this also takes into account any returns). this summary field is named "Net Quantity Sold" and i put a portal in the Inventory file that shows this number. but i want to deduct "Net Quantity Sold" from the "Inventory:Amount Purchased" number for each item in the Inventory file whenever we sell something. i can't get it to work. i created a new field in the Inventory file called "Current Inventory" i set the calculation for that field: "Amount Purchased - Invoices::Net Quantity Sold". even after i sort the Line Item/Invoices file and the sub-summary calculations are correct, the field "Current Inventory" in the Inventory file remains blank. i also tried putting that same calculation field in the Line Items/Invoices file (Inventory::Amount Purchased - Net Quantity Sold) and if there was only one of an item sold, the calculation worked just fine, but if there were multiples of the item sold and/or returned, the result was really screwy (-312 of one item)
(note that i also have another sub-summary part in the Line Item/Invoices file that summarizes each invoice. and there is a training grand summary part also)
what am i doing wrong? any help? i am really a novice, so keep it simple if possible!!!
also, I am using FM pro Advanced 8.0.
THANK YOU anyone in advance!