picking the 3 best grades from a course linkup table

[jax]

Dear experts,

 

I've got a student table linked via a linkup table to 3 or more courses in a 3rd table (see screenshot). The linkup also contains the grades.

 

I've been chewing on the following calculation: calculate an average of the 3 best course grades, e.g. discarding the worst if a student took 4 courses. So, I have to collect the grades via the student ID and then discard the worst if more than 3.

 

I've already burned 2h hours on this since I'm not very familiar with FM. I hope somebody can help me out.

 

All the best,

[comment]

One way to do this is by sorting the related records from the join table by grade, descending (you do this in the relationship setup). Then you can use the GetNthRecord() function in the Students table to get the first three related records and calculate their average.

[eos]

I remember a similar requirement for sporting events, but wasn't able to locate it. See if this example helps you …

 

EDIT: Saw comments post after posting myself; his method and description should be all that's necessary.

[jax]

Hi comment, hi eos,

 

Thanks for answering. Sorted the relationship - check.

Retrieved the grades in order using GetNthRecord() - check. I did this In separate fields to test. The command below correctly retrieved 1.0 as the best grade in a 1st, 2nd, 3rd system out of the following series: 1.0, 1.2, 1.4, 2.0.

GetNthRecord ( module allocation::module grade ; 1

Then, I attempted the following computation of the average of the 3 best grades:

Average ( GetNthRecord ( module allocation::module grade ; 1 ) ; GetNthRecord ( module allocation::module grade ; 2 ) ; GetNthRecord ( module allocation::module grade ; 3 ) )

But the average came out wrong and I have not the foggiest why. For 1.0, 1.2, 1.4, 2.0 it came out as 1.0 although after discarding 2.0 as the worst grade it should have been 1.2.

[comment]

Sorry, I should have remembered to mention there's a bug when using GetNthRecord() inside aggregate functions. Here's one way to work around it =

Average (
GetAsNumber ( GetNthRecord ( module allocation:::module grade ; 1 ) ) ;
GetAsNumber ( GetNthRecord ( module allocation:::module grade ; 2 ) ) ;
GetAsNumber ( GetNthRecord ( module allocation:::module grade ; 3 ) ) 
)

Or just do a simple =

( GetNthRecord ( module allocation::module grade ; 1 ) + GetNthRecord ( module allocation::module grade ; 2 ) + GetNthRecord ( module allocation::module grade ; 3 ) )  / 3

Also make sure that the calculation is unstored; Filemaker will not force this when using GetNthRecord().

[jax]

Hi comment,

 

What a weird bug. One would think that such a basic function/calculation would be properly implemented in FileMaker.

 

In any case, I tested it and it worked like a charm. Thanks a bunch.

 

Regards