Rank/position number of sorted records

[enquirerfm]

Based on a large DB I have created a summary report (I'm not interested in the detail) which extracts products according to certain criteria and groups them under sub-summaries. The product at the top represents the most sales, and the product at the bottom - the least. I wanted to obtain a ranking no eg No1 at the top and had thought to define a field which would accept this number by inputting the rank using Replace Field Contents with a serial number. However, it inserts incremental numbers through the whole file rather than just for the sub-summary. For example: say, I have a file of 2000 different hats in 50 different colours. A red hat is the most expensive at $100, the black hat the least - $50, say. But I sell only 10 of the red hat and 1000 of the black hats. So, in my summary at the top I have Black hats $50,000 (at my proposed No.1) and Red Hats below it at only $5,000 (proposed No. 2). eg

Black hats $50,000
Red hats $5,000

How can I create and populate a field which I can add to this report which will indicate the following:

Black hats $50,000 Rank 1
Red hats $5,000 Rank 2
 

?

[Wim Decorte]

Dump the results in a reporting scratch table.  That would give you a true "summary" record.  And would make it safe.  As it is now your approach needs to touch every exsisting record and change it which will:

- potentially not work because of locked records by users

- screw up your auto-entry modification fields if you have them.

 

The report as you have it now shows summaries but those are not their own records.  It shows data from the first record that belongs to that "group".

[comment]

1 hour ago, enquirerfm said:

it inserts incremental numbers through the whole file rather than just for the sub-summary.

In order to number sorted groups incrementally, you will need three fields:

1. sCount, Summary = Count of any field that cannot be empty - e.g. the serial ID field;
2. cInvCountByProduct, Calculation = 1 / GetSummary ( sCount ; ProductID )
3. sGroupNum, Summary = Total of cInvCountByProduct (running)

Note that this just numbers the groups incrementally, it does not truly rank them. If two or more groups are tied, they will not be assigned the same rank. To truly rank the groups would be more complicated - although I would not go as far as to require a summary table.

See also: http://fmforums.com/topic/93917-subsummary-count-of-related-parents/?do=findComment&comment=429470

Edited November 26, 2015 by comment

[enquirerfm]

Thanks. But this won't work where the rank is based on a value?

[comment]

2 hours ago, enquirerfm said:

But this won't work where the rank is based on a value?

No, it works on the position of the group in the sort order. But in practical terms, you would reorder the groups based on the summary value anyway, wouldn't you?

[enquirerfm]

Works like a charm... thank you.