calculation formulas

[Sheridan Designs]

I have had a database created for me for my jewellery design company and I am trying to add another function to it. You will have to bare with me as I know very little of this program especially when it comes to writing calculations. I'm a designer not a wizz kid!!

Basically alot of our jewellery goes out on Sale or return basis, and is very important for us to keep an update of our different stockists stock levels.

Our filemaker pro database is set up as Line Items / product lines / invoices / delivery notes and customer database.

For any one stockist we send delivery notes and raise invoices on items they have sold at the end of that month.

In line items I have an existing stock field but at the moment it is a manual drop down menu. Which inevitably means alot of work by hand calculating the stock.

What I want my system to do is to take the unique product code from delivery notes and subtract the unique product code from invoices to give me a running total of any one item that the shop should have at any one time. Can someone one please tell how to write the calculation for that existing stock field. If you can you will make me very happy.

[zincb]

In your Invoices file:

(1) I assume you already have a Num_Sold field, to hold how many of an item was sold.

(2) Create a Self-Relationship, called Self_Join, where Invoices:Unique_Product_Code = Invoices:Unique_Product_Code.

(3) Create a new calculated field:

Total_Sold = Average( Self_Join:Num_Sold ) * Count( Self_Join:Num_Sold )

In your Delivery_Notes file:

(1) I assume you already have a Num_Delivered field, to hold how many of an item has been delivered.

(2) Create a Self-Relationship, called Self_Join, where Delivery_Notes:Unique_Product_Code = Delivery_Notes:Unique_Product_Code.

(3) Create a new calculated field:

Total_Delivered = Average( Self_Join:Num_Delivered ) * Count( Self_Join:Num_Delivered )

In your Line_Items file:

(1) I assume you already have Relationships with both the Delivery_Notes and with the Ivoices files, based on Unique_Product_Code. These relationships are named Delivery_Notes_Join and Invoice_Join, respectively, below.

(2) Change your Existing_Stock field calculation to:

Existing_Stock = Invoice_Join:Total_Sold - Delivery_Notes_Join:Total_Delivered

If you make a change to Num_Sold or Num_Delivered, the change may not immediately appear in the Line_Items file if it is concurrently open. It will appear if you click into a row with the same Unique_Product_Code, however.

[Sheridan Designs]

Many thanks for your reply, I will try what you have suggested. May I come back to you if I have any problems.

It's funny that you mention that information doesn't always appear in diifferent files even though there are relationships between those files. I do find this very irritating. Is there something wrong with my set up or is that an annoying querk with filemaker pro 4.1.

For example...I can raise a delivery note and the Delivery Note No and Delivery Note Date immediately appear into Line items. When I do the same in raising an invoice neither appear in the line items.The relationships and formulas/look ups are exaclty the same appart from using the file names. Is there an easy answer to this?

Could you possibly save my sanity twice?!!

Thankyou again and I will let you know whether I manage it. I never mentioned I use a mac..would this make a difference to anything that you have suggested.?

Regards Sarah

[zincb]

I find that fields in related files will refresh quickly if there are no calculations involved.

With the exception of importing data, lookup fields always require a conscious action on the part of the user to update their contents.

And Yes, using a Mac will make a big difference -- you will have more fun!

If you wish, you can e-mail me your (zipped or stuffed if they are large) files and I will gladly take a look at them. No charge, since I am not a consultant (but no guarantees of success, either ).

[Sheridan Designs]

Dear Carey,

Many thanks again for your help. I tell you what I would so appreciate you looking at my database. How would I go about emailing it to you?

Regards

Sarah

[zincb]

My address is [email protected]. If you click on the e-mail button above this reply, it will open a mail window, automatically.

If they are over 5Mb in size, please send them in pieces, as our mail server gets upset.