Constraining and sorting

[enquirerfm]

I've partially created a script which reveals that I am missing something about how FM treats Find/Sort commands. I want to produce a list of the Top 100 from a database. This is what I have so far:

Show all records

Constrain Found set {items marked with a N}

Sort records [Restore] {sorted to produce the most expensive eg descending value}

Go to record/request/page [first]

Omit multiple records [100] {seems OK to here but then it adds back the constrained set omitted earlier}

Show Omitted only

What I then want to do is produce a list of the Top 100 (in, say, December 2009)

Any help much appreciated...

[LaRetta]

When you Show Omitted, the 'N' records will reappear in your found set. If I understand correctly, you want the Top 100 but skipping any 'N' records. This might do it:

Perform Find [ Restore ] ... this hunts for records with 'N'

Show Omitted Only ... it is this set you want to sort and grab first 100?

Sort [ Restore ; no dialog ; descending ]

Go to Record/Request/Page [ by calculation; no dialog ; 101 ]

Omit Multiple [ no dialog ; by calculation ; Get ( FoundCount ) ]

You should now have top 100 records without Item N sorted descending on value.

UPDATE: In omitting multiple, you could use calculation to be exact of: Get ( FoundCount ) - Get ( RecordNumber ) + 1 but I believe it is just as fast to omit more than needed. You could also hard-code the calc with 99999 but I envision (down the road when company grows) that you might outgrow that number and your script will begin breaking. Better to use Get (FoundCount), I believe.

If I missed the mark, let us know and we can re-work it. :smirk:

[enquirerfm]

Great! this worked well and I was able to add a couple of tweaks based on this script. Many thanks.