Calculating total quantity based on status in related table

[Pinki]

Hi,

I am an FMP Novice, I'm struggling with what should be a fairly simple calculation.

I have a master table (Inventory) containing a list of chemicals and a related table (Item) listing each container of each chemical (uniquely identified by a serial number), the quantity in each container and the status of the container (i.e Stock, In use or empty). I want to be able to record on my master table the total volume of each chemical we have.

I have been trying to perform the following calculation in the stock volume field;

if(Product Name = Item::Item and Item::Status ≠ "Empty";Sum(Item::Container Volume))

The result i get is a total calculation including the empties - which i don't want included.

I'm sure it's something obvious that i am missing, but i can't seem to see what it is. I hope someone can help.

Thanks

Pinki

[bruceR]

First of all, there is no native conditional "sumif" function and you cannot write calculations the way you are attempting to write them. There are some custom functions that can do this.

However, in your case, there does not appear to be any need. If the container is empty; then it's empty! So all you need is Sum(Item::Container Volume). But I may be making the assumption that you're recording the actual stored volume in addition to the container capacity; and maybe you're not doing that. That is, you might have a 5 gallon container that is holding 2 gallons.

So another approach would be to have a calculated volume field, case( status="empty"; 0; capacity) and use sum( Item::CalculatedVolume )

Edited June 7, 2010 by Guest

[Pinki]

Hi Thank you - this worked a treat.