Looking from the invoicing perspective, an invoice is created when an item is 'sold' which adds items. If items are returned, a credit memo is issued which would minus the same items. You would duplicate your invoice and lineitems (on the return) and change the quantity to minus. This cannot be handled entirely by calculation because it would not account for damaged product or product written off because of non-return.
If your lineitems is (or will be) very large, you can use an Inventory table otherwise lineitems will suffice (but you would need to create 'inventory adjustment' invoices to record product loss/damage/adjustments. Your product table would sum the lineitems and always know the quantity 'in stock'.
Thanks for your suggestion, that would help for returning items, but i still need to figure out how to summarize "on hand " quantities and subtract from them when an invoice goes out. Any help w/ how to formulate those calculations would be great! thanks!
Your LineItems table would have a quantity field qty. When a product goes out, the qty would be 1 (or however many went out) . When the item is returned the qty would be -1. You would put a summary field in LineItems, which would be Total of Quantity (name the field s_TotalQty. If LineItems holds your ProductID and it is joined to your Products::ProductID then placing the LineItems summary field directly on your product layout will produce your existing 'on hand' quantity.
You must have a field in your Products table with the beginning inventory (or put that in the inventory table). So if you have beginning inventory of 10 for Widgets and you send 3 out (on an invoice) and only get two back, it would work like this:
Products::BeginInventory - s_TotalQty ... which translates to 10 - 3 + 2 = 9 on hand
If you instead use an Inventory table, you would write 10 as beginning inventory on the product. Then each night you would add any 'out' products and also write any 'in' products - all kept in the lineitems table as lines on an 'invoice.'
ok , got it ! the only thing i'm still wondering about is how to get totals for individual line items. I tried "total quantities" but that added up all of the line items w/o differentiation between different types of parts ( each part has its own ID). I have been able to do a sub summary that gives me the totals i want, but im not sure how i can use this summary when i want to subtract quantities. Thanks again for all your help.
b34n3 wrote: i'm still wondering about is how to get totals for individual line items. ...im not sure how i can use this summary when i want to subtract quantities.
An individual LineItem can't be summarized; it holds only one value whether out (plus) or in (minus). You would sum by the Product. If you want to know how many of a product for a given month was rented (plus) and also how many of a product was returned (minus) then you need a Type field in your Invoice table designating whether it is rental or Return form.
Then from lineItems, report columnar, leading parts of ProductID and Type. Put your summary field into both leading parts. Then you will know how many were rented out and how many total were returned. Are you creating two forms? One when item is sent out and another when item is returned? Do you have Invoice table and LineItems table? How about Products? Are you using Inventory table? Do you have qty field in LineItems which holds plus qty if invoice and minus qty if return?
If we are still missing each other then I suggest that you post up a sample file showing the issue and explaining a bit more. And maybe others see something I'm missing. We'll get there. :smileyhappy:
Maybe i am making this more complicated than it needs to be. I have an inventory table that has a portal containing inventory line items. All of the stock that is currently "on hand" is here. I have subsummary report that shows me the totals of stock sorted by part id & description. I also have an invoices table that has separate portal containing invoice line items. When parts go out i would enter them here as negative quantities. I am still trying to figure out how to subtract invoice quantities from the inventory quantites.
I have tried doing "Sum" calculations ( like this: Sum( inventorylineitems: quantity) - Sum(invoicelineitems: quantity)
but this does not work.
"I am still trying to figure out how to subtract invoice quantities from the inventory quantites."
If you have an INventory table then ALL lineitems with a Product (in and out) should be written to the Inventory table. The Inventory table holds:
1) Products which have moved in and out (through your Invoices LineItems)
2) Your beginning inventory on every product (as a line)
3) Your re-take of physical inventory so you enter adjustments directly here (as write-offs)
4) Your purchases of product so you increase your stock on hand.
5) Stock which is given away or trashed (most businesses require these too run through the Invoicing process invoiced to their own customer record.
These are business rules you will need to find out about. All lines in your LineItems table (if they have a Product ID and are not discounts, payments etc) should be written to the Inventory table. Nightly or on the fly with script triggers, find all product lines (which aren't in inventory yet) and add them as records into Inventory.
"When parts go out i would enter them here as negative quantities."
Standard procedure is to enter them as positive numbers. This is because an invoice lineitem has a price and price times qty equals amount owing. Be careful not to get yourself twisted in this thinking ... if someone pays $10 towards an invoice, how then would you apply their payment (as a minus or a plus)? In your world it would be as a plus but payments should deduct from an invoice/charge total and everyone thinks (or knows) that a payment or credit is a minus. So when you write all the product lines to Inventory - you reverse them. But they should remain positive for outgoing and negative for incoming (credits, returns and such) in the LineItems table.
So items out the door are in lineitems as plus. Items returned are lineitems as minus. Write all to Inventory. Now some businesses drop the inventory table entirely and treat everything as an Invoice - even their products purchased. But I don't think it wise to go down that path right now. Then the lineitems become their inventory but it can get ugly as it grows; FileMaker is not good at aggregating data.