Jump to content

Rank/position number of sorted records


This topic is 3018 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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
 

?

Link to comment
Share on other sites

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".

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

This topic is 3018 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.