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

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.

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