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

# How to sort and determine the position

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

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

applepiez:

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 ]

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

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

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

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

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

applepiez:

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

TSGal

FileMaker, Inc.