In the second list, John is still listed first because he has the top score. Seems like your top list should show some other value than A for that one top scoring record...
You can use custom value lists to specify different arbitrary orders for sorting, but you'd need that first record to have some other value than "A".
Thus you could have a set of custom value value lists--one for each possible order and a script can select the correct Sort Records step, that references one of these value lists, to get the desired record order.
Thanks for the quick reply. Yes. I have explored custom value lists but my main road block is the top 10%. This is why John is at the beginning of the list. So, if I understand you correctly, I need a new field that labels the top 10% of people. Just as a point of reference, there is a total of around 150 employees. The top 10% ends up being around 15 people.
So "Field X", lets call it "Order". How would I get something like:
Name Grade Bucket Order
John 100 A TOP 10%
Karen 90 A A
Bob 98 A A
James 99 A A
Jim 70 B B
Jay 80 B B
Liz 99 B B
Kay 98 C C
Brian 50 C C
Jane 99 C C
I'm thinking something along the lines of Order being a calulation field but I can't quite figure the equation.
Given the "top 10%" requirement, you probably cannot make that field a calculation. A script can sort your records by Grade, then Replace Field Contents can assign a value to the top 10% of your found set.
It could use this calculation:
If ( Get ( RecordNumber ) < get ( FoundCount ) / 10 ; "X" ; Bucket ) // "I'm using "X" where you are using "Top 10%"
You can't use this in a calculation field because changing the sort order will change the value returned by this calculation.
I also would like to know a way of getting a "Rank" regardless of bucket. I currently have a running count that labels the rank but this changes when the list is sorted. If I had a rank instead of this running count, I could do an equation like:
Good thinking with the script. I think my understanding of when to use a calculation vs script is lacking. So... I could do the same thing with the "Rank" field.
It looks like we posted at the same time, but I would also like a "Rank" that would be independent on sort order. Would I just assign the current record number to a field and it would stay that way until I rerun the script?
If this is the case, should I use a calculation field for the "Top 10%"... Oh wait, I don't think I can since again, it would require a reference to count of employees.
You can use replace field contents to assign the rank to a number field in each table after first sorting the records in correct order for ranking. You then have a rank value that will persist with any sort order or found set you might have.
Awesome! Two fields to be populated upon going to layout. Done and done. Thanks.