caluclating Totals

[johnrh]

I have two files related via a unique ref field in each giving a simple oneto many relationship. The first file has a layout with a portal showing related records from the "many" file - again very simple!.

The files deal with departments (main file) and sales of particular products by that department. Also there will only ever be a maximum of six different products sold altogether across all the departments.

My problem is that I simply need to show the sales of each product for each department. My plan is to create six new calculated fields to store these values. The problem I have is how to total the sales of each product from within the portal. Something similar to the Excel "Sumif" function would be nice.

Any help or suggestions or perhaps a differnt direction to go ASAP would really be appreciated.

Thanks

John

[CobaltSky]

SumIf about sums it up ...except that you need to put the if part into the related file, and the sum into the main file.

What I suggest you do is to create six separate 'flag' fields in your related field which reference the product field, with formulae along the lines of:

Case(Product = "FirstProduct", 1)

Then in your main file, the six corresponding fields will use the sum function along the lines of:

Sum(RelatedFile::ProductOneFlag)

These latter six fields will be unstored, and will pick up the respective sub-totals of a particular product sold by a particular department in the main file.

[johnrh]

Just a quick reply to say thanks a million. I just got your reply and tried it and it works like a charm. Simple when you know how. Thanks again

John