8 Replies Latest reply on Jun 26, 2012 6:14 AM by philmodjunk

    RANKING OF CALCULATED FIELD

    OlalekanOnitiju

      Title

      RANKING OF CALCULATED FIELD

      Post

       Dear all,

      I have a layout (SummaryScores) that displays calculated fields from another layout (StudentScore). In (SummaryScores), I want to have another field that will rank a field display in (SummaryScores) from (StudentScore).

      Take for instance, field “StudentScore::total” that is on (SummaryScores) layout contains more than 100 records which some of them are the same, a field “position” is to be created that will contain the ranking.

      Note, the same score will attract the same rank.

      Help me out pls

      Thanks

        • 1. Re: RANKING OF CALCULATED FIELD
          philmodjunk

          "Layout" and "table" are two different things. Data is stored in tables and layouts are set up to provide an interface with those tables so that you can see and interact with that data. You can in fact, create multiple layouts all based on the same table but designed to use a different interface for viewing/editing the data in that table.

          You appear to have two tables, StudentScore and SummaryScores. I would guess that they are related like this:

          StudentScore::StudentID = SummaryScores::StudentID

          Is studentScore::Total a calculation field such as: Sum ( SummaryScores::Score ) or is it a summary field that computes a "total of" multiple records in teh StudentScore table?

          A "rank" calculation field can be produced that numbers your records by looking at the score of any record that immediately precedes it in the a found set of records sorted by total score, but I need to understand the exact structure of your data and relationships to make sure that what I have in mind will work with your database design.

          • 2. Re: RANKING OF CALCULATED FIELD
            OlalekanOnitiju

             

            Thanks for your response, maybe I should explain myself again, I don’t have two tables but one in a layout named “StudentScore” this table has many fields such as StudentID, Names, Sex as well as the students subjects scores such as MatFT, EngFT, BscFT, etc. the most important of these fields is “total” which is calculation field not summary field.

            The calculated field “total” will sum like this “EngFT + MatFT +BscFT +  BteFT + CedFT + IntrFT + LitFT + HomFT + BusFT + ComFT + FinFT + MusFT + AgrFT + CrsFT + FreFT + YorFT”.

            Furthermore, there is another layout in which some fields including “total” are displayed. Take for instance, fields like StudentID, Name, Sex and “total” are displayed from table “StudentScore” this layout is called “SummaryScores”. Now, the task is that I want to create a field called “rank” that will contain the position of these students based on their total scores “total”.

            I hope I made myself clear enough for you to help.

            Thanks very much.

            • 3. Re: RANKING OF CALCULATED FIELD
              philmodjunk

              What you have makes the ranking fairly easy, but I wouldn't use individual fields in the same record for recording student scores like this, I'd use a related table in order to get much greater flexibility in what can be done with that data.

              But for this task, teh key details are that you have one record for each student and a single field that contains the score.

              If you find the studentScore records that you want to rank and sort them by the total field in descending order, this calculation field should display a ranking:

              If ( Get (RecordNumber ) = 1 ; 1 ;
                       let ( [PrevRec = Get ( RecordNumber ) - 1 ;
                               PrevTotal = GetNthRecord ( Total ; PrevRec ) ;
                               PrevRank = GetNthRecord ( Rank ; PrevRec ) ] ;
                               If ( PrevTotal = Total ; PrevRank ; PrevRank + 1 )
                             )
                  )

              • 4. Re: RANKING OF CALCULATED FIELD
                OlalekanOnitiju

                Hello Phill,

                I am happy to inform you that it works but not the way I want it, for instance, it ranked it as the records are created but didn’t rank the highest number in the table with “1”.

                Also, I created four records so far and I expect to have ranked them “1 to 4” if there is no tie among the scores.

                The following is what I have:

                The first score=75, rank=1

                Second score=75, rank=1 (correct)

                Third score=73, rank=2 (incorrect, should be rank=3)

                Fourth score=94, rank=3 (incorrect, should be rank=1 while 1st and 2nd scores should change 2 and 3rd score to 4)

                Thanks

                • 5. Re: RANKING OF CALCULATED FIELD
                  philmodjunk

                  First key detail is that the records must be present in the found set and sorted by score in descending order. The field where this calculation is done should be an unstored field of type calculation. It should not be a number or text field with an auto-entered calculation or it will fail to update correctly.

                  Change the found set and the rankings change, though there is a way to "freeze" the results and store them so that this is no longer the case.

                  To get the third score in your example to rank as 3 instead of 2 requires a small change in the calculation:

                  If ( Get (RecordNumber ) = 1 ; 1 ;
                           let ( [PrevRec = Get ( RecordNumber ) - 1 ;
                                   PrevTotal = GetNthRecord ( Total ; PrevRec ) ;
                                   PrevRank = GetNthRecord ( Rank ; PrevRec ) ] ;
                                   If ( PrevTotal = Total ; PrevRank ; Get ( RecordNumber ) )
                                 )
                      )

                  • 6. Re: RANKING OF CALCULATED FIELD
                    OlalekanOnitiju

                    Hello Phill,

                    Thanks very much for your contribution, I tried all but couldn’t get the desire outcome, let me tell you that I want the position to auto correct itself as the entering of data is going on in the browse mode not after all the data have been entered.

                    I would be happy if you can still find the solution to this, if it may require scripts, I wouldn’t mind.

                    Once again a big thank to you

                    • 7. Re: RANKING OF CALCULATED FIELD
                      philmodjunk

                      The ranking cannot be done until the records are sorted. Have you tried doing this with the found set already sorted by this field? I would think that would work, but your records will "jump" on you when you enter a value that changes its position in the sort order.

                      I really think you'll have to wait until the data is entered to see the rankings.

                      • 8. Re: RANKING OF CALCULATED FIELD
                        philmodjunk

                        Hmmm, might be possible to add a sorted portal of student scores to the header/footer/grand summary part of your data entry layout and it might update as you enter data to show the rankings as each student's grade is edited.