What structure have you set up in your inventory file? One record for each product?
Instead, you can set up your inventory file like an accounting register with a field for adding items to inventory and a field for removing them. Each record in this table represents a change in inventory (items received, sold, lost, destroyed, etc.). Summary fields can then compute the amount on hand for you.
WIth this structure you can use the inventory table as your line items table that your invoice links to and uses a portal to edit. Then completing an invoice automatically deducts the items sold from your inventory.
Search this forum for threads with "inventory log" and you'll find several that discus this approach.
The inventory file is one record = one product. I just used the inventory template that comes with FMP and edited the layout accordingly.
I'm a bit confused about what the inventory log table will look like, what fields to include etc...
Can I still use the inventory db I have created, or do I need to make a new one from scratch?
Thanks for your help! Sorry for being pretty clueless, I'm learning as I go.
I find the inventory template that comes with filemaker way too limiting. WIth an inventory log table, you can not only see what you have on hand, but also how inventory levels are changing over time, when was the last time you recieved a new supply, etc.
An inventory Log table works like a check register where new product recieved represents a "deposit" and items removed from inventory (Sold, lost, shrinkage, etc.) are "checks". A summary field that computes a running balance is used to show items on hand.
Typical fields in such a table:
cBalance (Qty Recvd - Qty Removed )
sBalance (Summary, running total of cBalance, restart totals when grouped by ITemID)
If you use this with an invoice that uses a portal to link directly to this table, you'd add at least these fields to this table:
Cost (Unit Price * Qty Removed )
Other fields may be needed depending on what you need to see in your invoice.