automatic deduction from inventory

[jfernand]

I have a file maker file which handles my inventory and one file maker file which makes the invoices of the merchandise I sell. The description fields of the invoice file are linked to the inventory file and this way i don't have to type the description of everything i sell, but just select it from a pull down menu. I also created a relationship so that the price was automatically "pulled out" of my inventory file and entered as well.

What I want to do is that everytime I complete an invoice, file maker automatically deducts the quantities of the items I sold from my inventory file. Right now what I have to do is open the inventory file and change the quantities manually. Is there any way to do this?

Thanks.

[markpro]

Create a relationship in your inventory file with the invoice item file.

Relationship Name is "Sold"

Item No=Invoice Items::Item No

In the inventory file, create a "Qty Sold" field, calculated:

sum(Sold:Qty)

[mcstar]

Along the lines of what markpro wrote about the sum(sold::quantity) and the Product Inventory business template, you could keep a running count even when you restock.

1) In the Inventory file create a number field for stock quantity and a date field for last restock date (both of which you update whenever you add stock)

2) In the Invoice file (or Line Items file if you want a more flexible solution) create a calc field for quantity to deduct from stock = if( invoice date last restock date, quantity, 0).

3) Back in the Inventory file create a calc field for quantity remaining = initial quantity - sum(sold::quantity to deduct from stock).

Now whenever you enter a quantity onto an invoice, the inventory count is reduced. And whenever you restock, you get an accurate count going forward.

-- Mark