6 Replies Latest reply on Jun 21, 2013 3:25 PM by malijames

    stored Ranking

    malijames

      Title

      stored Ranking

      Post

      I have student table with the following fields Name , class,point1,point2,point.....  and  with a calculated  field called Total which is the sum of each student points then  another field called Rank wich calculate the rank of the total field now  how do I get my Rank field to store or replace the field content with  the actual  rank calculation used so that when sort by student name , the ranks will follow their respective total. At the moment I have to sort by Total field in descending order to get and accurate rank but when sorted back by names, the rank field don't get sorted because it is set not to store calculated result. * Can the rank calculation be limited to a found set? *  Can't I rank without sorting the Total? * If Sorting is necessary how do I write a script that  sort the found set by totals,rank them, store the ranks and sort again by names ( the found set)? * how do I achieve all these if the student table is used in a portal (that is ranking only found set in that portal) *the portal is used in a layout called "session" from session table linked to student table by fk_session_ID The rank formula used is If ( Get (RecordNumber ) = 1 ; 1 ; let ( [PrevRec = Get ( RecordNumber ) - 1 ; PrevTotal = GetNthRecord ( Total ; PrevRec ) ; PrevRank = GetNthRecord ( Rank ; PrevRec ) ] ; If ( PrevTotal = Total ; PrevRank ; Get ( RecordNumber ) ) ) ) Can this formula be converted to a script? Help me out please

        • 1. Re: stored Ranking
          philmodjunk

               Add a numer field to your table. sort the records like you do now to get the correct ranking. Then use Replace Field Contents with your rank calculation to load the number field with the rank value. Each time a student score changes, you will need to repeat this process so setting it up as a script would make it much easier to do.

          • 2. Re: stored Ranking
            malijames
             i created another field called rank2 next to the rank field after ranking by formula then run this script Replace Field Contents [No dialog; Students::rank2;Students::Rank] But the result is differnt from the rank field So how do I use this formula in a script If ( Get (RecordNumber ) = 1 ; 1 ; let ( [PrevRec = Get ( RecordNumber ) - 1 ; PrevTotal = GetNthRecord ( Total ; PrevRec ) ; PrevRank = GetNthRecord ( Rank ; PrevRec ) ] ; If ( PrevTotal = Total ; PrevRank ; Get ( RecordNumber ) ) ) )
            • 3. Re: stored Ranking
              philmodjunk

                   Were the correct valeus shown in the original rank field when you ran this script?

                   What you used should just copy the value of the original field--which should have number specified as the result type, into the new field.

                   Replace field contents also has a calculation option where you can specify the calculation instead of referring to the field.

              • 4. Re: stored Ranking
                malijames
                Thanks for the reply  the correct values shows in the original rank field after running the script but the rank2 field shows wrong values. How do I script the whole ranking process?
                • 5. Re: stored Ranking
                  philmodjunk

                       There must be some detail missing in what you are describing. The replace field contents step you described should simply copy over the calculated rank. Then you have the rank stored in a number field and you can sort the records into any order you want and use the new number field to display the rank.

                       Can you post an example illustrating the "wrong values" you are getting? There may be a clue there...

                  • 6. Re: stored Ranking
                    malijames
                     the replace field content now works perfectly I just don't know what went wrong the first time Thanks phil  you have saved me hours of trial and error