Calculating stock available / sold

[RogerA]

Hi. I have created a simple invoicing system: Invoices - Line items - Parts (Products). I need to calculate how many items of stock for each product are sold / stil available via Invoices. Could someone please help me with the necessary calculation? Best wishes and many thanks. Roger

[Ender]

Hi Roger,

Below is an ER diagram showing a simple invoicing/purchasing system. Getting counts of products sold and products purchased is not too difficult, a simple calc field in the Product file can get these:

Quantity Available = Sum(PO Line Items::Quantity) - Sum(Invoice Line Items::Quantity) + Sum (Adjustments::Quantity)

Unfortunately, as the system grows, this calculation will get slower and slower.

There is another problem with this simple count: If items are lost, stolen, etc; the counts become incorrect. There needs to be a method for correcting inventory.

A good way handle both issues is to use a number field (instead of a calc) to hold the Quantity Available. Now the challenge is to make sure the Quantity Available gets updated regularly. This can be done every time an Invoice is finished and every time a PO is ordered by looping through the line items and updating the corresponding products' counts. There will also need to be some method to reverse these counts if an invoice or PO is cancelled.

In my ER diagram, I have an optional table for Adjustments. This could be used to do manual adjustments to the Quantity Available (instead of users just changing the number field). It lets you track changes a little better.

I'm sure there are other ways to handle this type of system, but that's what I've come up with. Let me know if it makes sense.

[RogerA]

Hi. Thank you so much for your very clear instructions re the stock levels. have now put that into practice.

Could I ask a supplementary question? My invoice are in file INVOICES. I would like to automatically enter the Cusomer ID No. from CUSTOMERS when I access the Invoices file / screen. How do I bring this info across? At the moment I am re-typing it in to the Customer ID field on Invoices reklated to Customer ID Customers.

Many thanks. Roger

[Ender]

You can start the process in the Customer file and use a script to create a new Invoice. The script would go to the Invoice file, and set the Customer ID. There are a couple ways to do this.

One example:

The script in Customer:

...

Set Field [ "gCurrentCustomer", "Customer ID" ]

Perform Script [ Sub scripts, external (Invoice: Create new record from Customer) ]

The script "Create new record from Customer" in Invoice:

Enter Browse Mode []

New Record/Request/Page

Set Field [ "Customer ID" , "Customer::gCurrentCustomer ID" ]

...

You should make sure you have some way to verify the user's access level when launching this type of script. If you had a user with edit permissions but no permission to Create records, the above script would modify an existing invoice instead of creating a new one.

[RogerA]

Hi there. Thanks for the reply. I have set up the scripts as you suggest(one for the Customer file and one for the Invoices file, but I only get the Invoices file producing a new record. The Customer ID field is not populated. Many thanks for your help. Kind regards, Roger

[Ender]

Sorry, my example above assumes you have created a relationship to Customer in the Invoice file based on Customer ID. Even though the relationship is not valid without the right side key, you can still pull globals across.

[RogerA]

Thanks, Mike. I suddenly realised that I hadn't set up a global field (gcustomerID) !Sorry. That's a novice for you!. Now works well. Thought you might like to see where your help is going: www.rspcanorwich.org.uk .Many thanks from all the cats and dogs in our care. Roger

[timber35150]

Hi,

Brand new to FileMaker and can't see the wood for the trees!

I would like my "qty on hand" field in my PARTS file to auto reduce by the qty a user enters, when used in the PARTS ORDER file.

Anyone help?

E.G.

The user finds the part he/she wishes to use, then clicks "add part to order" which does exactly that. This creates a new record in the PARTS ORDER file, where the user can enter how many parts he uses. In this file i have a "jobno." field which is related to the "SERVICE RECORD" file, jobno::jobno

This file (SERVICE RECORD) displays a portal from the PARTS ORDER file, which has the unique jobno. identifier.

I assume a calculation is required?

[Robert Kidd]

Just a quick word of warning regarding using a number field to hold the Qty. You will need to be carefull of record locking issues, two users trying to update the quantity of the same stock item simultaneously will give problems. I prefer the sum of transactions method, you can , if necessary "roll up" say last years transactions into a single transaction to reduce the number of records. The transactions method gives you a simple way of making stock adjustments for lost or damaged goods and gives a very good audit trail for checking levels.

[mike foy]

I think this is the best thread i can ask this question in....

I used the invoice template and linked it to the stock template with lookups then a created (effectively anyway) a customer db just to hold the address, so I wouldn't have to keep typing them in again, I link the customer db to the invoice with the email address and when I enter the email into the invoice it looks to see if there is anyone in the customer db and fills it all in. So I have really only used lookups. Now it would be nice (hence me being in this thread) to be able to remove items in the invoice (am I stating the obvious here?) Now I have tried (wrongly so it seems) to say that the quantity in the stock db = quantity in stock db - quantity in invoice (repeated field,is that a problem) but of course it moans that this is a circular equation. so it seems I can't do it as a simple calculated field. so next I tried to have a script (i don't even mind if it works on a button)

not knowing too much about the scripting (I am a programmer, but...) my thoughts ran along this way, get first product id (of 8 possible) from invoice db

open up the stock db

repeat

find stock record that matches product id

quantity=quantity- invoice quantity

end repeat (based on numbre of products in invoice)

that's it done.

but how does this get translated into a script?

I started by creating a script in the stock db

enter browse mode[]

go to field["control stock:: product quantity"] this is a relationship that matches the product id in the invoice to the product id in the stock

insert calculated result[select,"quantity",:quantity-control stock::prodcut quantity"]

but this doesn't seem to work* (also do I need line 2? since it seems to be reproduced the the following line)

* it took one item away when I had sold 3 and in another case it took 3 away from the first item and didn't remove any from the second.

hheeeellllpppp please

mike

[freshwaterf]

HELP ME!

DOESE ANYONE HAVE A WORKING VERSION OF TH$E FILES FOR THIS INVENTORY SCHEMA. I'm an idiot, and I need to see an example..please!!

Thanks

[freshwaterf]

Is there a working model(files) for a simple inventory? Simple...I just want to be able to update inventory from invoice using line items...Simple..Please help ME!!!