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

    How to sort and determine the position



      How to sort and determine the position




      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

          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



            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)




            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 


               # 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 ]


                  # 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


                  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.



            FileMaker, Inc. 


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

              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



                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.



                FileMaker, Inc.

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

                  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



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



                    FileMaker, Inc.