I have a Line Items file. Each record is a product. Each product is in a single category. There are 6 categories, 900 products, and 60,000 records.

I'm looking to create a report of a found set, sorted by category, showing the number of products in each category, AND sorted by the number of products in each category (in descending order)

The report should look something like this:

CATEGORY A

Product1 1200

Product3 1150

Product2 1100

...

CATEGORY B

Product500 1500

Product150 1000

Product200 890

...

CATEGORY C

Product750 2000

Product10 1500

Product151 1000

...

Note the sorting by NumberOfProducts (descending)

I have tried using a self-relationship to get NumberOfProducts in a found set using a GetSummary calculation. I can sort on the GetSummary field, but this takes a looong time. Is there a better way?

Thanks,

ft.

You don't need a GetSummary calculation to do that, just use the "Reorder based on summary field" option in the Sort dialog.

This should be rather simple, unless I'm missing something: sort your records by CATEGORY (ascending) first, then by Product (descending, reorder based on summary field: ).

Thank you both very much.

I had not used/noticed that 'Reorder based on summary field...' option before.

I have created a summary field, sCount, to count the number of each product and am trying to add this to the sort.

But I am not seeing what I expect.

If I sort on' Category, ascending' then 'Product, Ascending' I do see categories and products in alphabetical order, and the total number of each product in the found set is shown.

However, if I use 'Category, Ascending', then 'Product, Descending, Reorder based on SummaryField, sCount', the total number of each product is blank. The products are sorted in reverse alphabetical order.

Thanks,

ft.

...After a little more research...

It looks like I'm experiencing the bug described in detail here:

http://www.listsearch.com/FileMaker/Thread/index.lasso?47590#162460

The 'Reorder based on summary field' only works correctly if used in the original file containing the original table for the summary field.

My report was not in the original file that contained the original table and fields. My report was in an interface file, which had a TO referencing the original file.

If I create the report in the original file, all works as expected.

At least I know and can work around the problem.

Thanks,

ft.

Thanks for sharing that thread, really good info from Corn Walker in there.

I experienced that bug myself a while ago and worked around it the same way you did.