14 Replies Latest reply on Dec 20, 2013 8:48 AM by Keith224

    Sorting Question - Newbie

    Keith224

      Title

      Sorting Question - Newbie

      Post

           I have a sort question.  I have a table with a many records for each student.  There are 4 scores per year for each student.  I need to be able to sort those records in to the latest 16 records for each student by a field name [Year].  Then re-sort the previous sort into 10 records based on students [Score] from lowest to highest out of the previous sort of the 16 records.  Then from those 10 records have a calculation of the Average of those 10 scores.

           I am a relative newbie to Filemaker.  Formerly an Access user now converting to Filemaker.

           Thanks for any help!

        • 1. Re: Sorting Question - Newbie
          philmodjunk

               "Sort" doesn't mean the same thing for every person that posts here. I think you mean "find" not "sort" as a "sort" arranges records in a particular order where a "find" pulls up a specific set of record as your found set on a layout, such as for a report like you describe. But what you describe will require a combination of the two.

               What Data in this table allows you to tell which records for a given student are the "latest records"? Is there a date, time stamp or auto-entered serial number that can be used to determine that?

               When you say: 

               

                    I need to be able to sort those records in to the latest 16 records for each student by a field name [Year].

               Does that mean that you want to find, as an example, the most recent 16 records recorded for each student in 2013 or some other year specified by the user or a script?

          • 2. Re: Sorting Question - Newbie
            Keith224

                 Fist, sorry, i did not mean to click on Best Answer!  I don't know how to remove that.

                 Find is probably a better definition.  Records have field for year, Record ID.  But the complication is that not every student has scores every year.  We need to still be able to find the latest 16 records and out of those records choose the lowest 10 scores.  Then have the average calc.

            • 3. Re: Sorting Question - Newbie
              philmodjunk

                   So these are annual tests--such as standardized testing or exit exams?. I wondered if that would be the case. Otherwise a different "score" for each year isn't reasonable.

                   To recap what I am reading, You want the best ten of the latest 16 scores? And this is to compute an average of those best 10?

                   Do you need to seen the individual scores plus the average or do you just need to compute the average?

              • 4. Re: Sorting Question - Newbie
                Keith224

                     Compute the average but wanted to display the 10 scores on the layout.

                • 5. Re: Sorting Question - Newbie
                  Keith224

                       Yes, you are correct in that i want the best ten of the latest 16 scores.  Then compute the average of the best 10.  Yes, would like to see the 10 scores on the layout along with the average.

                       i appreciate all you assistance!

                  • 6. Re: Sorting Question - Newbie
                    philmodjunk

                         This one is tricky. I can think of several ways to do it and none of them are particularly simple. One could show all records sorted by student and year. Then a script could loop through and omit all records from record 17 to the next student's set of records. Then you could sort again to get the best 10 and do another round of looping and omitting to get it down to the best 10. Slow and cumbersome, but at that point, a summary field could compute the average for each student based on the remaining group of 10 records.

                         I'm thinking that a pair of self join relationships and a portal might make for a simpler approach.

                         Is this all in one table or do you have one table for students and a related table for the yearly scores?

                    • 7. Re: Sorting Question - Newbie
                      Keith224

                           Students in one table, Scores in another table.  I already have them in a Portal by Student.  I have a tab with all scores by student another tab for the Average and the 10 scores that make that average, i just don't know how to get it to the 16 records and then the 10 and the average of the 10.  

                           I just need the help to find the 16 records and then the 10 lowest records of the 16 and then the Average.  I have been banging my head over this one.  I am wanting to learn how to do these in Filemaker.  

                           Any help would be greatly appreciated!

                      • 8. Re: Sorting Question - Newbie
                        philmodjunk

                             A Portal to Scores Sorted by Year (the sort can be specified in Portal Setup or in the underlying relationship) can list the most recent 16 scores just by being defined to have 16 portal rows. The trick is to then show only the best 10 and compute an average.

                             This can be done with two Tutorial: What are Table Occurrences? to scores. The first is used to get the most recent 16 and the second just get's an average of the top 10.

                             You'll need a primary key field in scores that uniquely identifies each record to pull this off. I'll call that field: Scores::__pkScoreID.

                             Assuming that your current relationship is:

                             Students::__pkStudentID = Scores::_fkStudentID

                             We can use that relationship to get the most recent 16 if we double click the relationship line and specify a sort order for Scores that sorts them by the Year field in descending order. If that affects other parts of your database, you may need to duplicate the Scores table occurrence to set up an additional relationship where you can specify the sort order without it affecting other areas where the order would be an issue.

                             Now define a calculation field, cRecent16 as:

                             LeftValues ( List ( Scores::__pkScoreID ) ; 16 )

                             Use another table occurrence of Scores and link to Students like this:

                             Students::__pkStudentID = Scores|T16::_fkStudentID AND
                             Students::cRecent16 = Scores|T16::__pkScoreID

                             Select a sort order for this relationship that sorts records in Scores|T16 in descending order by Score.

                             A 10 row portal to Scores|T16 will list the top 10 scores from the most recent 16.

                             And this calculation field will compute the average of these top 10:

                             Let ( ScoreList = LeftValues ( List ( Scores|T16::Score ) ; 10 ) ;
                                     Evaluate ( "( " & Substitute ( ScoreList ; ¶ ; " + " ) & 0 & ") / " & Count ( ScoreList ) )
                                   ) // Let

                             Notes:

                               
                        1.           We could just divide by 10 instead of Count ( ScoreList ), but if a student doesn't have at least 10 scores on record, using count will make this an average of all the scores that you do have instead of computing an artificially low average.
                        2.      
                        3.           The Substitute function turns the list of values into this text: "Fieldvalue1 + FieldValue2 + ....", but the leftValues function sticks an extra return after the 10th value so we end up with an extra + operator on the end. Including & 0 is a simpler way to keep the syntax correct for evaluate than adding extra stuff to strip off the extra return or extra + operator.
                        • 9. Re: Sorting Question - Newbie
                          Keith224

                               I have all of this working with the exception of the last calculation.  It keeps returning ?.  I can't seem to get it to work properly.  Is it suppose to reside somewhere special, such as in a particular table?  Or am i just not understanding?

                               I appreciate the help!

                          • 10. Re: Sorting Question - Newbie
                            philmodjunk

                                 It should be defined as a calculation field in Students. If you click in the field, do you still get the "?" result?

                                 If you still get "?", make a copy of this field and edit out the Evaluate function to get:

                                 Let ( ScoreList = LeftValues ( List ( Scores|T16::Score ) ; 10 ) ;
                                         "( " & Substitute ( ScoreList ; ¶ ; " + " ) & 0 & ") / " & Count ( ScoreList )
                                       ) // Let

                                 What does that produce for you?

                            • 11. Re: Sorting Question - Newbie
                              Keith224

                                   ok, tried the later.  Here is a copy of the result and the basis behind it.

                                   Returned=  -8.3839e+20

                                   Basis behind he return:( 83 + 83 + 86 + 86 + 88 + 89 + 90 + 91 + 91 + 92 + 0) / ?

                              • 12. Re: Sorting Question - Newbie
                                Keith224

                                     When i Click on it i still get a ?.  I copied the field and edited the Evaluate function.

                                     Results returned: -8.3839e+20

                                     Clicked on it and got:( 83 + 83 + 86 + 86 + 88 + 89 + 90 + 91 + 91 + 92 + 0) / ?

                                • 13. Re: Sorting Question - Newbie
                                  philmodjunk

                                       I see my mistake: It should be:

                                       Let ( ScoreList = LeftValues ( List ( Scores|T16::Score ) ; 10 ) ;
                                               Evaluate ( "( " & Substitute ( ScoreList ; ¶ ; " + " ) & 0 & ") / " & ValueCount ( ScoreList ) )
                                             ) // Let

                                  • 14. Re: Sorting Question - Newbie
                                    Keith224

                                         That worked!  Thanks you!  I am trying my best to learn how to do these type of calculations.