Sorting by summary field

[enquirerfm]

I have created a summary field which collects the number of records (sales) attached to a particular product. e.g. Shirts, blue, size L - summary field adds up all the quantities of each shirt. I want create a report which lists the products by the largest no. of quantity. I can produce a report, using a self-join relationship, which automatically fills in a field with the quantities I need so I can see for each product sorted by product name or attribute the quantities for each. But, how do I produce a report sorted by the quantity in the summary field? If I try and do this the summary field is always greyed out?

And.. I would like to have a field which shows which number each of these is listed at e.g. no. #1 (most sales), #99, # 10023 etc - a bit like Amazon's listing of books

Many thx.

[comment]

a summary field which collects the number of records attached to a particular product

I am not sure I follow this fully. Is there a table of products and another table of styles or quantities?

[Matthew F]

I think what you mean is that you have a summary field that set to the count of 'product'.

I don't know why this summary field is grayed out when you do a sort. What if you do the following:

1. Create a calculated field 'productCount' in the original table which is equal to the summary field in the related (self join) table.

2. Add a sub-summary part onto your layout which displays values when sorted by product.

3. Sort your list by: 1) 'table2::calcF' and 2) 'product'.

[enquirerfm]

Seems to work - thank you.

What about my question:

And.. I would like to have a field which shows which number each of these is listed at e.g. no. #1 (most sales), #99, # 10023 etc - a bit like Amazon's listing of books. So I have a product which has 28 sales and should be no. 165 in my list. Another product which has sales of 256 which is no. 65 in my list etc.

[Matthew F]

I think what you are looking for is a ranking according to product sales. Filemaker has no rank function. Nightwing previously described a method for doing this with relationships. Check out his entry entitled Field Ranking and Sorting. The demo file called 'AutoRanking' does the best job at handling ties.

[enquirerfm]

Thanks for this - yes, complicated stuff. Very handy to have this link. Thank you.