7 Replies Latest reply on May 2, 2013 7:42 AM by malijames

    Rank with ordinal suffix

    malijames

      Title

      Rank with ordinal suffix

      Post

       please how do I rank a calculated field called "Total". The total is based on the sum of different subjects. I want to create a new field called "Rank" to display the ranks of each student 1. I want the rank to be updated as the totals change (if possible) 2.  The rank be updated without sorting the layout (table view) if possible 3. The ranking should appear with ordinal suffix like " st,nd,rd, th I.e 1st,2nd..........) 4. If I'm on a form view or portal , can the rank be updating as I change scores ? 5. The rank should take note of ties 6. My portal is based on the subject table Hope I'm not asking too much I have 2 tables STUDENT and SUBJECT table related by pk_STid and fk_STid.                           Please help me out Thanks in advance

        • 1. Re: Rank with ordinal suffix
          philmodjunk

               If you have all these records in a single found set, sorted in descending order by Total, then this unstored calculation named "Rank" can report the rank:

               Case ( get ( RecordNumber ) = 1 ; 1 ;
                           Total = GetNthRecord ( Total ; Get ( RecordNumber ) - 1 ) ) ; GetNthRecord ( Rank ; Get ( RecordNumber ) - 1 ) ) ;
                            Get ( RecordNumber )
                        ) //

               You have to get sneaky to define this field.
               Add a new number or text field named Rank and click Create. That adds "Rank" to the field list so that the calculation can refer to itself.

               Then change the field type to Calculation and add in the above expression. Be sure to click storage options and specify the "do not store..." option.

               This produces the rank number but not the ordinal suffix. Get the above calculation working for you first, then add this calculation field to properly format the Rank:

               cFormattedRank: Rank & If ( rank < 4 : Choose ( Rank ; "" ; "st" ; "nd" ; "rd" ) ; "th" )

          • 2. Re: Rank with ordinal suffix
            malijames
             i created a new field called" Rank" and change the field type to calculation ,then in the specify calculation dialogue box i copied and paste the above expression exactly as it appears but when I click Ok  it gives me an error saying " An operator (e.g. +,-,*,....) is expected here then returns back to the specify calculation box.   Is the first expression above a custom function? I may be doing something wrong If possible help me with a sample file
            • 3. Re: Rank with ordinal suffix
              malijames
               i'm still struggling with it but can't get it work
              • 4. Re: Rank with ordinal suffix
                philmodjunk

                     There is no custom function in this calculation, but I could have included a typo in my sample calculation. How is the expression highlighted when you get that error message?

                • 5. Re: Rank with ordinal suffix
                  malijames
                  This part get highlighted in the expression you gave me above                                                                                             ; GetNthRecord ( Rank ; Get ( RecordNumber ) - 1 ) ) ; Get ( RecordNumber ) ) //                                                                               From this post.                                                         http://forums.filemaker.com/posts/2b39b2625a                                                    I got this but it only works when the records are sorted in descending order by Total.                 If ( Get ( GetNthRecord ) = 1 ; 1 ; Let ( [PrevRec = Get (GetNthRecord )) - 1 ; PrevTotal = GetNthRecord ( AMOUNT ; PrevRec ) ; PrevRank = GetNthRecord ( RANK ; PrevRec ) ] ; If ( PrevTotal = AMOUNT ; PrevRank ; Get (GetNthRecord )) ) ) )                                                                                                                                                                  But when I unsort it back the rank doesn't follow each total I could have love it to work without sorting all the time                                                                                  Don't get tired pls.   I'm just a beginner
                  • 6. Re: Rank with ordinal suffix
                    philmodjunk
                         

                              but it only works when the records are sorted in descending order by Total

                         That is correct. I stated that limitation in my original post.

                         To get the ranks to "stick", you can find/sort your records in the needed order and use Replace field Contents to assign these ranks to a simple data field, but then you will lose the ability to see them change when you edit data that produces a new total for that individual. After each edit of a value, you'd have to run a script or manually do the steps to repeat the update process.

                    • 7. Re: Rank with ordinal suffix
                      malijames
                       thanks phil I will do it just as yoou said Thanks again