7 Replies Latest reply on Mar 16, 2009 12:23 PM by TSGal

    How to sort and determine the position

    applezpiez

      Title

      How to sort and determine the position

      Post

      Hi,

       

      I am now creating a points system, such as every week the teacher will enter the points the students earned.

       

      Example (my student point database):

      Jan - Week 1 - Peter - 1 pts

      Jan - Week 1 - Mary - 2 pts

      Jan - Week 2 - Peter - 3 pts

      Jan - Week 2 - Mary - 4 pts

      Jan - Week 3 - Peter - 5 pts

      Jan - Week 3 - Mary - 6 pts

      Jan - Week 4 - Peter - 7 pts

      Jan - Week 4 - Mary - 8 pts

       

      Then i will use a summary field to get the total points earned for one month,

      hence i will get:

      Peter - 16 pts

      Mary - 20 pts

       

      Hope you still understand so far..

       

      Next, i will need to determine the position of the students based on the points they earned in each month. In this case, Mary got 1st, Peter got 2nd and etc (for the rest of the students)

       

      I need to prepare the report as shown below:

      Month - 1st    - 2nd    and etc...

      Jan     - Mary - Peter  and etc....

      Feb    - .................................

       

      This is where i am lost... Not sure how to sort and determine the position. Please advise.

       

      Thanks in advance!

       

       

        • 1. Re: How to sort and determine the position
          applezpiez
            

          Please advise if this is feasible... if this cannot be done, please let me know :smileyindifferent:

          • 2. Re: How to sort and determine the position
            TSGal

            applepiez:

             

            Thank you for your post.

             

            Yes, this can be done, but it would be easier to put the summarized data into another table that can be printed.

             

            Create a second table, REPORT, with the fields:

             

            Month (Text)

            Field1 (Text)

            Field2 (Text)

            Field3 (Text)

            ...

            Fieldn

             

            where "n" is the number of students.

             

            Each time a month changes, a new record is added.

             

            Next, create a script (under the Scripts menu "Manage Scripts..." ) that performs this operation automatically for you.  Here is one way of doing this:

             

            # turn on error capture 

            Set Error Capture [On]

             

            # go to the REPORT layout and remove all existing records.  Start fresh each time this report is run. 

            Go to Layout [ "REPORT" (REPORT) ]

            Show All Records

            Delete All Records [ No dialog ]

             

            # return to your points detail layout 

            Go to Layout [ <your layout with points detail> ]

             

            # sort the records by Month (custom order?), and Name (Descending based on Summary field) 

            Sort Records [ Restore ; no dialog ]

             

            # the sort may not leave you at the first record, which is where we want to start processing 

            Go to Record/Request/Page [ First ]

             

            # initialize a variable to keep track of the Month 

            Set Variable [ $mon ; Value: "" ]

             

            # enter the loop and process records 

            Loop

               # check to see if the Month is still the same.  If not... 

               If [ <original table> :: Month ≠ $mon ]

                  # ... initialize some variables 

                  Set Variable [ $mon ; Value: <original table> :: Month ]

                  Set Variable [ $name ; Value <original table> :: Name ]

                  Set Variable [ $field ; Value: "1" ]

             

                  # go to the REPORT Layout

                  Go to Layout [ "REPORT" (REPORT) ]

             

                  # add a new month record 

                  New Record/Request

             

                  # ... and set the appropriate fields. 

                  Set Field [ REPORT::Month ; $mon ]

                  Set Field [ REPORT::Field1 ; $name ]

               Else

                  # set some variables

                  #    Grab the Name field 

                  Set Variable [ $name ; Value: <original table> :: Name ]

                  #    increment the $field variable 

                  Set Variable [ $field ; Value: $field + 1 ]

             

                  # switch to the REPORT layout 

                  Go to Layout [ "REPORT" (REPORT) ]

                  # go to the next field in order and replace the result with the name

                  Set Field By Name [ "REPORT::Field" & $field ; $name ]

               End If

             

               # return to the original points detail layout 

               Go to Layout [ <layout with points detail> ]

             

               # loop through the records until we find the next person or month

               Loop

                  Go to Record/Request/Page [ Next ; Exit after last ]

                  Exit Loop If [ <original table> :: Month ≠ $mon or <original table> :: Name ≠ $name ]

               End Loop

             

               # if the last record is reached, error code 101 is set.  Exit the loop. 

               Exit Loop If [ Get (LastError) = 101 ]

            End Loop

             

            Go to Layout [ "REPORT" (REPORT) ]

             

            -----

             

            At this point, you can go to Browse Mode and view, or print.

             

            If you need clarification for any of the above steps, please let me know.

             

            TSGal

            FileMaker, Inc. 

             

            • 3. Re: How to sort and determine the position
              comment_1
                

              Hi TSGal,

               

              I'm afraid there's no good way to say this, so I'll just say it: this is an extremely bad idea. What will happen if, for example, you add or a delete a student?

               

              I don't see why a simple report, sub-summarized by month and student wouldn't work here. The records need to be sorted by month, then by student (re-ordered by total of points). The ranking could be taken care of by another summary field that counts the records (with restart for each month).

               

              Admittedly, this will not produce the ideal result in case of a tie. If that's an issue, then one should really consider a temp reporting table, where each month/student combination would be a RECORD. The ranking can then be computed by defining a self-join of month=month AND points<points, and counting the related records.

              • 4. Re: How to sort and determine the position
                TSGal

                comment:

                 

                I totally agree with you that my solution isn't ideal.  And yes, I didn't take into account adding and deleting students.

                 

                This user wanted to know if this type of cross-tab report was possible.  You and I both know it is possible, but I wanted to know why the customer asked this question.  I have to determine the level of FileMaker expertise for this user.  This becomes a balancing act on my end.  If I am too technical, the user becomes confused or overwhelmed, and if I am too basic, then I can upset the customer.  From my judgement (flawed or otherwise), I could not assume that he had experience with self-joins.  So, I decided to show a method that would teach the user about using a second table for reports and use a commented script to show what I was doing.

                 

                Please don't get me wrong!  Please continue to comment (no pun intended) on any of my posts.  I appreciate your feedback, and I have learned a lot from your expertise.  Your contribution to this forum has been outstanding, and I hope you continue to contribute.

                 

                TSGal

                FileMaker, Inc.

                • 6. Re: How to sort and determine the position
                  applezpiez
                    

                  Thanks TSGal and comment for all the advise. Really appreciate it

                   

                  I am jus a beginner in FM, that's y all these seems quite complicated for me :smileysad:.

                  I will try to understand it and test it out. Hope it can work on my end!

                   

                  Can I still post if there is any issue :smileytongue:

                  • 7. Re: How to sort and determine the position
                    TSGal

                    applepiez:

                     

                    Yes, please feel free to post again if you run into an problem with either solution.

                     

                    TSGal

                    FileMaker, Inc.