Hi,

The problem that I am trying to solve is the following, if you have products with category codes then I want to sum up the number of products in each category and post it against each product. For example

Product Code

Product1 001

Product2 002

Product3 002

Product4 004

This should show up as

Product Code Concentration

Product1 001 1

Product2 002 2

Product3 002 2

Product4 004 1

I can think of how to do this using SQL but how can it be done using functions? Ideally I would like to have this calculation triggered through a button.

Your help is appreciated.

Sunil

This should work for you but there may be better ways -

Create self-join between ProductCode to ProductCode (the full Product1 001) ...

Create calculation (number) with Count(selfjoin::ProductCode)

I believe I misinterpreted your bold column heading Product Code.

If you simply want to count how many products appear in each Code, join Code to Code instead. And then Count(selfjoin::Product). Joining Product1 001 would give you the count of each product within each code. Not sure if this is what you want. And I assume list file/table is like a lineitems? In that the same product can be listed multiple times? If not (and you have a quantity field), you would need to use Sum(selfjoin::Quantity).

To get the display just as you've indicated, you would then need a concatenated calculation (text) with: Product & " " & Code & " " & cCountcalc

You can also create a columnar/list report. Put Code in leading sub-summary and Summary = Count(Product). Be sure to sort by Code. But you have to view the report in Preview and that doesn't sound like what you want.

Howdy! Try Moonshadow's last tip first (or make "count of" summary fields for each product if there's not many). You can make a button to switch layout/view if you want.

--ST