1 2 Previous Next 16 Replies Latest reply on Dec 17, 2010 11:21 AM by DennisPrickett

# Ranking five fields

### Title

Ranking five fields

### Post

I have 5 fields, each of which carries a unique numerical value 1-100.  Each of the fields needs to be ranked on a 1 to 5 scale, where the field with the highest value is 1 and the field with the lowest value.  I can manually enter the ranking, but would rather have a calculation automatically perform the ranking as data is entered.

Suggestions on how to accomplish this?

• ###### 1. Re: Ranking five fields

It's easier to rank them if your 5 fields of data where in the same field of 5 related records, then you can sort your records and assign a rank number to each record.

I think I once saw a clever approach by Comment that used combinations of Max and Min but can't recall the details ( and he was working with fewer fields also, if I remember right and I might not be.)

If you insist on 5 fields in the same record, I think you'd need to use a script that sorts the values in these fields in order to rank them.

• ###### 2. Re: Ranking five fields

I need the 5 field in the same record, so I guess I'm looking for some help in writing the script that sorts the values in the fields and then enters the ranking date in the rank fields.

• ###### 3. Re: Ranking five fields

What ranks do you give if your five values are:

42     42     55     78     78     ?  Could this happen in your application?

• ###### 4. Re: Ranking five fields

No, the values are unique, so there are no duplicate values.

• ###### 5. Re: Ranking five fields

Just out of curiosity, why do they need to be in the same record? A table of related records might be pretty easy to work with.

Let's assume you have 5 fields named Field1, Field2, Field3... and 5 rank fields named Rank1, Rank2 ....

Set Variable [\$List ; value: List ( table::Field1 ; table::Field2 ; table::Field3 ; table::Field 4 ; table::Field 5 ) ]
Loop
Set Variable [\$Rank ; \$Rank + 1 ]
Set Variable [\$max ; value: Evaluate ( "max (" &  Substitute ( \$List ; ¶ ; "; " ) & " ) " ) ]
Loop
Set Variable [\$I ; Value: \$I + 1 ]
If [ GetValue ( \$List ; \$I ) = \$max ]
Set Field By Name [ "YourTable::Rank" & \$I ; \$Rank ]
Set Variable [\$list ; Value: List ( LeftValues (\$List ; \$I - 1 ) ; 0 ; RightValues ( \$List ; ValueCount ( \$List ) - \$I ) ) ]
Set Variable [\$List ; Value: Substitute ( \$List ; "¶¶" ; ¶  )
Set Variable [\$I ; value: 0 ] ]
End IF
Exit Loop If [ \$I = 0 ]
End Loop
Exit Loop If [ \$Rank = 5 ]
End Loop

There are many different sort algorithms that can be used. This is just one possibility.

• ###### 6. Re: Ranking five fields

I guess they could be from a table of related records, but that's not the way it's set up now.  Wouldn't be too difficult to change it up.  I do appreciate the script.  I thought that setting up some variables and then interting the list of values and somehow manipulating the list would be the solution.  But, I'm a Physical Therapist with little programming experience, so sometimes I need a little help.  I'll try this script.  Looking it over briefly it makes sense to me, but I'd have never come up with it on my own.

• ###### 7. Re: Ranking five fields

I tested the script before I posted it, but I left out one update that I used in the test file. Instead of replacing values with 0, I chose to replace them with -1000. Any value works as long as you are absolutely certain it will be less than the lowest value in any of the 5 fields. (Since blank can evaluate as zero, -1000 seems safer here).

Should you enter a value less than this substitute value, you'll get an infinite loop so it would be a good idea to include either a validation rule that enforces values larger than this or to include a test using the Minimum function to throw up an error dialog and exit the script if a too small value is entered.

• ###### 8. Re: Ranking five fields

I'm not sure what to do with the field name in the Set Field by Name script step.  I have five fields named A, B ,C D, and E, and five ranking fields which corespond to each of the field, A_Rank , B_Rank, C_Rank, D_Rank, and E_Rank.  I can follow what is happening up until this script step, bit now I'm lost.

• ###### 9. Re: Ranking five fields

The first parameter for Set Field By Name has to be a text calculation that evaluates to the name of the correct field. Simplest solution is to rename your fields to match my example. (You can edit the field names in your layout to any label you want, you don't have to use the field's internal names.) Changing a field's name won't break any references to it, unless you have other references such as those that use set field by name or get field to refer to it by name.

If you want to keep the file name unchanged, you'll need to modify the calculation. One possibility:

Set Field By Name [ Choose ( \$Rank ; "" ; "A" ; "B" ; "C" ; "D" ; "E" ) & "_Rank" ; \$Rank ]

( "" is needed as the 2nd parameter since Choose expects index values starting with 0 as the first possible value in \$Rank )

• ###### 10. Re: Ranking five fields

Just a slight correction - the calculation should be:

Set Field By Name [ Choose ( \$I ; "" ; "A" ; "B" ; "C" ; "D" ; "E" ) & "_Rank" ; \$Rank ]

"(Choose ( \$Rank;....)" just put the ranking  1 through 5 into the x_Rank fields in order A ->E.  Once I fixed that, it works PERFECTLY.  Thank you so much!

• ###### 11. Re: Ranking five fields

Your welcome! And the fact that you were able to diagnose and correct my mistake tells me that you are not just copying the solution, but understanding how it works--which is as important to me as the fact that you got it to work.

• ###### 12. Re: Ranking five fields

OK, now a little more of a challenge for me that I worked through but think there may be a different solution.  I found that if any of the values in the fields A through E are 0, then the ranking wouldn't work right because the loop kept running through until \$Rank = 5, so the rank of the last ranked field kept increasing to 5.  So, I added Exit Loop If [\$max = 0] after the Set Variable \$max line to provide a way out.  That part works fine.  Then, the next issue was if one of the fields was left blank.  Seems that the blank field is not evaluated as zero, as the \$List returned with less values, and then the ranking was all wrong.  I figured that I could just insert 0 into each of the empty fields with  If [IsEmpty (A))  ,  Set Field [A ; 0]    and then repeat that for each of the 5 fields.  I'm wondering if there's a more 'elegant' solution to this?  After having a cool looking script that I couldn't have written without help (much less understand), the column of If / Set Field / End If lines preceding the script is pretty ugly!

`If [ IsEmpty ( TMR::A ) ]`
`   Set Field [ TMR::A; 0 ]`
`End If`
`If [ IsEmpty ( TMR::B ) ]`
`   Set Field [ TMR::B; 0 ]`
`End If`
`If [ IsEmpty ( TMR::C ) ]`
`   Set Field [ TMR::C; 0 ]`
`End If`
`If [ IsEmpty ( TMR::D ) ]`
`   Set Field [ TMR::D; 0 ]`
`End If`
`If [ IsEmpty ( TMR::E ) ]`
`   Set Field [ TMR::E; 0 ]`
`End If`
`Set Variable [ \$List; Value:List ( TMR::A; TMR::B;TMR::C;TMR::D;TMR::E ) ]`
`Loop`
`   Set Variable [ \$Rank; Value:\$Rank + 1 ]`
`   Set Variable [ \$max; Value:Evaluate ( "max (" & Substitute (\$List; ¶;",") & ")") ]`
`   Exit Loop If [ \$max = 0 ]`
`   Loop`
`      Set Variable [ \$I; Value:\$I + 1 ]`
`      If [ GetValue (\$List; \$I) = \$max ]`
`      Set Field By Name [ Choose ( \$I; "";"TMR::A" ; "TMR::B";"TMR::C";"TMR::D";"TMR::E") & "_Rank"; \$Rank ]`
`      Set Variable [ \$List; Value:List (LeftValues (\$List; \$I -1); 0; RightValues (\$List; ValueCount (\$List) - \$I)) ]`
`      Set Variable [ \$List; Value:Substitute (\$List; "¶¶";¶) ]`
`      Set Variable [ \$I; Value:0 ]`
`      End If`
`   Exit Loop If [ \$I = 0 ]`
`   End Loop`
`   Exit Loop If [ \$Rank = 5 ]`
`End Loop`
• ###### 13. Re: Ranking five fields

If you read back several posts, you'll find a note by me to change this line:

Set Variable [\$list ; Value: List ( LeftValues (\$List ; \$I - 1 ) ; 0 ; RightValues ( \$List ; ValueCount ( \$List ) - \$I ) ) ]

To

Set Variable [\$list ; Value: List ( LeftValues (\$List ; \$I - 1 ) ; -1000 ; RightValues ( \$List ; ValueCount ( \$List ) - \$I ) ) ]

And a comment that that value should be one that you know is always smaller than the smallest possible value. (Use a validation rule to keep the user from entering smaller values than this.)

• ###### 14. Re: Ranking five fields

Seems that I turned myself around backward. The issue wasn't the value of 0. The -1000 solution works fine for that.  It's the issue of a field left empty.  I needed to insert 0 into the empty field to get the script to work properly.

1 2 Previous Next