Show the maximum score of a student from related table

[Sam0316]

Hello everyone, i am new to filemaker. i am now working on student database.

The following is the problem i face.

The relationship of my database is shown as follows

 

student >> Class >> Class Line Item(portal) , student>>test>>test history(portal)

 

so in the class layout user could see details of a class. In the test layout, user could find out the test history of a student.

My client now would like to have a report based on the class line item showing the highest score of the student.

 

But my original idea is to separate the table clearly as shown above. i try to make a portal based on class line item table, but there is no relationship between class line item and test history table so i could not show any related data of the student. For the highest score, i think i will create a summary field to calculate the maximum value of the score.

 

my question is should i write a script to take the highest score of a student from the test history table to class line item table or modify the relationship between these tables?

[webko]

Add a new relationship? Depending on if the test history also has some idea of the class it was taken for....

 

[Agnes Riley]

A student database is a large bite, even for an experienced developer myself. It will probe to be much more difficult than you expect. So I recommend doing some reading, not just for learning FileMaker but how school systems and enrollment works becsuse I made the mistake of simplifying things two years ago and last year I had to redevelop a lot of it.

Whatever you can separate into logical buckets, you should. So e.g. Score cards should be based on student and enrollment year.

That being said, take a look at the Max function.


Sent from my iPhone using Tapatalk

[Sam0316]

22 minutes ago, Agnes Riley said:

A student database is a large bite, even for an experienced developer myself. It will probe to be much more difficult than you expect. So I recommend doing some reading, not just for learning FileMaker but how school systems and enrollment works becsuse I made the mistake of simplifying things two years ago and last year I had to redevelop a lot of it.

Whatever you can separate into logical buckets, you should. So e.g. Score cards should be based on student and enrollment year.

That being said, take a look at the Max function.


Sent from my iPhone using Tapatalk

Thank you. so you mean i should reading some books? what books you will suggest?

34 minutes ago, webko said:

Add a new relationship? Depending on if the test history also has some idea of the class it was taken for....

 

i should create a new relationship of test history and class item table?

[Agnes Riley]

FileMaker Training Series to begin with and generally Google, forums such as this, white papers, videos. But if you need a book the bibles are a good start.


Sent from my iPhone using Tapatalk

[Sam0316]

2 hours ago, Agnes Riley said:

FileMaker Training Series to begin with and generally Google, forums such as this, white papers, videos. But if you need a book the bibles are a good start.


Sent from my iPhone using Tapatalk

Thank you. i have read the filemaker training series(beginner and advanced), several forums and videoes. anyway, thank you for your advice.

Is it a good idea to directly connect student to test history table?

Edited December 13, 2016 by Sam0316

[Agnes Riley]

Ok. Then you are in the right track. There is always more to learn, I find. :-)

So, at least you need a student, student enrollment, and an enrollment test joint table and you want to connect these with the year, as well as the student ID. And that's your history table. You don't need a separate history table.


Sent from my iPhone using Tapatalk

[Sam0316]

2 minutes ago, Agnes Riley said:

Ok. Then you are in the right track. There is always more to learn, I find. :-)

So, at least you need a student, student enrollment, and an enrollment test joint table and you want to connect these with the year, as well as the student ID. And that's your history table. You don't need a separate history table.


Sent from my iPhone using Tapatalk

Thank you. so i need to delete the class and test table?

[Agnes Riley]

No, you need those, too. A class is something a student gets enrolled in in a year. A test is something a student takes several times or once a year.


Sent from my iPhone using Tapatalk

Try to draw a diagram. There are some school diagrams online.


Sent from my iPhone using Tapatalk

[comment]

3 hours ago, Sam0316 said:

The relationship of my database is shown as follows

 

student >> Class >> Class Line Item(portal) , student>>test>>test history(portal)

I am afraid this is not at all clear. We don't know what these tables represent in real life, or even how they are related ( the symbol >> can be read in many ways).

Why don't you start by describing the things that your solution needs to track. Will a student only ever be in one class? If not, you will need an Enrollments join table between Students and Classes. Similarly, does a student only take one test? And how may times can a student take the same test (I am assuming more than once, because otherwise you would not need a "history" table). Also, does a test belong to a specific class?These are the things that will dictate the database structure, which needs to be figured out before you can ask about maximum scores.

[Sam0316]

18 minutes ago, comment said:

I am afraid this is not at all clear. We don't know what these tables represent in real life, or even how they are related ( the symbol >> can be read in many ways).

Why don't you start by describing the things that your solution needs to track. Will a student only ever be in one class? If not, you will need an Enrollments join table between Students and Classes. Similarly, does a student only take one test? And how may times can a student take the same test (I am assuming more than once, because otherwise you would not need a "history" table). Also, does a test belong to a specific class?These are the things that will dictate the database structure, which needs to be figured out before you can ask about maximum scores.

Thank you. Actually, my client would like to have a report like

Class A

Student Name      Age      Date of Birth        Maths Score     English Score

Peter                     12          1992/4/6                    60                    70

Jenny                    13          1991/3/8                    70                   50

And the score is the maximum score that the student get of all the test the student attended.

A student could get a test for many times, so i use a portal to present all the test detail of a student.

Is it better to have a summary report to present this data?

i should find the record with same class rather than present them in portal?

[comment]

You haven't answered any of my questions.

[Sam0316]

A student could enroll in different classes

test is not specific to any class

students could take for more than once for same test

students could take test as they wish

Edited December 13, 2016 by Sam0316

[comment]

11 minutes ago, Sam0316 said:

A student could enroll in different classes

Suppose Peter is enrolled in Class A and in Class B. And Peter has a max score of 60 in Math (which you say, is not related to any class). In your report, should Peter's score  be listed twice - once underClass A and once under Class B?

[Sam0316]

5 minutes ago, comment said:

Suppose Peter is enrolled in Class A and in Class B. And Peter has a max score of 60 in Math (which you say, is not related to any class). In your report, should Peter's score  be listed twice - once underClass A and once under Class B?

Yes. but the user would like to have a report with class A or Class B, not both

[comment]

5 hours ago, Sam0316 said:

Yes. but the user would like to have a report with class A or Class B, not both

Do you mean each report will only include students enrolled in a single class?

[Sam0316]

15 hours ago, comment said:

Do you mean each report will only include students enrolled in a single class?

Yes.You are right so i need to create a join table of class line item to test history table?

[comment]

I have no idea what "class line item" means. Based no your description, you should have the following structure:

Now, in order to produce a report similar* to the one shown earlier, you need to:

1.  Select a class for the report and find the scores of the students in the selected class. You did not tell us how you want the user to select the class; for example, the user could click a button in the Classes layout, and your script would then find the related scores by doing Go to Related Record[]. Or you could just perform a find in the Scores table.

2. Sort the found scores by StudentID, TestID and by Score (descending) and show them in a report layout (based on the Scores table) having sub-sumary parts for StudentID and TestID and no body part. The Score field would be in the sub-summary by TestID part.

(*) Note that Filemaker's native reports are vertical, not horizontal. Taking your earlier example, the described report would look something like:

Class A
Student Name      Age      Date of Birth        
Peter                      12       1992/4/6                    
• Math: 60                    
• English: 70

Jenny                     13       1991/3/8                    
• Math: 70                   
• English: 50

[Sam0316]

so i need to delete the existing relationship of the database, right?

Edited December 14, 2016 by Lee Smith
Removed the quote of the graphic

[Sam0316]

On 14/12/2016 at 3:32 PM, comment said:

I have no idea what "class line item" means. Based no your description, you should have the following structure:

Now, in order to produce a report similar* to the one shown earlier, you need to:

1.  Select a class for the report and find the scores of the students in the selected class. You did not tell us how you want the user to select the class; for example, the user could click a button in the Classes layout, and your script would then find the related scores by doing Go to Related Record[]. Or you could just perform a find in the Scores table.

2. Sort the found scores by StudentID, TestID and by Score (descending) and show them in a report layout (based on the Scores table) having sub-sumary parts for StudentID and TestID and no body part. The Score field would be in the sub-summary by TestID part.

(*) Note that Filemaker's native reports are vertical, not horizontal. Taking your earlier example, the described report would look something like:

Class A
Student Name      Age      Date of Birth        
Peter                      12       1992/4/6                    
• Math: 60                    
• English: 70

Jenny                     13       1991/3/8                    
• Math: 70                   
• English: 50

 

If i would like to use class table to show the maximum score and the related test date, what should i do?

[comment]

8 hours ago, Sam0316 said:

i would like to use class table to show the maximum score and the related test date

I don't see how that's possible using Filemaker's relationships. It may be possible using ExecuteSQL(), but I am not going there. I recommend you learn to use the native reporting methods before expanding to SQL.

 

Edited December 16, 2016 by comment