Can values be 'ranked?' 1

[Lemur21]

Assuming that I have a record with a portal. In the portal are values from 5 users -- their names and their scores (which means, 5 record in the join table).

Is there a way to have the values RANKED - i.e., I'd like to have another field; calculation, I assume, that would 'rank' the values in descending order. I know I can pull out a MAX and a MIN value, but I'm looking for something similar to the "LARGE" function in Excel (where I can 'rank' the values from 1-5, with 1 being the largest and 5 being the smallest).

Is there a way to do this without scripting (or, with a fairly easy, quick-running script -- I guess I wouldn't be opposed to entering the data and then hitting a button to 'rank' the values)

Thanks!

Chris

 

[JeanW]

Depends how you want your ranking.

As a simple row, sort on value:

Rank Value
1 500
2 400
3 300
4 200
5 100

or the complex way:

Rank Value
1 500
2 400
3 400
4 200
5 100

What are then the requirements for rank 2 and 3 (same value).

Basically it can be done with a few self-join relationships, 2 scripts.
But you need a more than basic understanding of relationships.
The scripts are nothing difficult, just sort on serial and do a replace by calculation.
I don't think you can accomplish this without scripting.
Hitting a button to trigger the script is not the end of the world.
Advantage can be that you can make an 'in between' ranking, f.i. when not all the scores are available....

 

[Lemur21]

In your example, I'd want it to do:

Rank Value
1 500
2 400
2 400
4 200
5 100

I'm okay with using a script - basically, there could be a button that would say "assign ranking."

The important thing is that, after that script is run, there HAS to be a # in the rank field -- assigned by the program.

Chris

 

[JeanW]

Well, you need a few fields and 2 scripts.
Let see if we can do this without a relationship....

Don't know how your structure is, so this is a dummy...
5 fields: score (number), rank (number), rankCalcStart_gn (global number), rankCheck_gn, setRankTo_gn.

Script 1 could be something along these lines (not tested just by heart)
You need a loop that sets the rank after the scores have been sorted.
Goto record/request/ page - first
Set field (rankCalcStart_gn; 0)
Sort Records (restore, no dialog) (sort manually score/descending)
Loop
Set field (rank;rankCalcStart_gn + 1)
Set field (rankCalcStart_gn;rank)
Go to record/request/page - next, exit after last
End loop

Script 2
You need to verify each score with the preceeding and re-rank the scores who are tied

Goto record/request/ page - first
Set field (rankCheck_gn;score)
Set field (setRankTo_gn; rank)
Go to record/request/page - next
Loop
If (getasnumber(score)= GetasNumber(rankCheck_gn)
Set field (rank; setRankTo_gn)
Else
Set field (rankCheck_gn;score)
Set field (setRankTo_gn;rank)
End if
Go to record next Exit after last
End loop

Or you can make from those two script just one.
View your data in list view.

HTH

 

[Lemur21]

Actually, I *was* able to do this with a calculation - albeit a fairly lengthy one.

In my related field, where I had "score", I then added three more fields - Score2, Score3 and Score4.

I created a field in the portal file, for Max_Score, which I could find easily, as well as new fields for Second_Score, Third_Score and Fourth_Score.

Then, in the related field, essentially, it said, if the player's score = Max_Score, make the score 0 in the Score2 field, otherwise, copy the original Score.

In the Portal file, Second_Score was a Max value of all the Score2 scores - since the true Max Score was now 0, the max Score2 score WOULD be the second-highest score.

And so on, for Score3 and Score4.

Thus ranking became a Case function - if score = Max_Score, make the rank 1, if score = Second_Score, make the rank 2, etc.

Takes care of ties as well, automatically!

Chris

 

[JeanW]

Just one question: what if ever in the future there will be a need for more than 5 users ?

Seems to me that you limit all your efforts to just 5, which doesn't make your solution dynamic....