Combine Tables in Sub-Summary

[cwest91]

Hi there,

I am a teacher who is new to Filemaker and working on a gradebook solution for my practice. Here's what I am trying to do:

I have 2 tables:

1. Assignments Table (Keeps track of Assignments dates, names, subject, etc)

2. Students Table (Name, email, student number, etc.)

I would like to be able to add the name of an assignment from the assignments table as a new field in the students table so that I can give each student a grade for an assignment... I thought of doing an "Add new Assignment" script which will take me to the assignments layout and allow me to enter the assignment information- BUT I cannot figure out how to create a new field in the students layout with the name of the assignment?

Right now I have been trying to solve this using separate tables, but I'm wondering if this would not be easier to just have all the data on 1 table??

Any help or suggestions would be greatly appreciated...

Thanks

[comment]

You should use three tables: Students, Assignments and a join table of Grades:

Students -< Grades >- Assignments

The Grades table needs fields for:

• StudentID (used to link to the Students table)

• AssignmentID (used to link to the Assignments table)

• Grade

Use a portal to the Grades table to view and create student's grades.

[cwest91]

Hi,

Thank you very much for your response. I have set this up in my database, but I am still having a few problems...

Here are my TO's including the join table

I have created a Portal that looks at the "Join Table" but nothing shows up in there even though I have 2 Assignments already input.

I also can't seem to add anything to the portal (and I checked that "allow creation of records" was checked..

I will continue to muck around, but does anybody have any thoughts??

Thanks again in advance.

[bruceR]

What fields are in the portal? Try a screen shot while in layout mode; or consider uploading the file or a clone of the file.

[comment]

I have created a Portal that looks at the "Join Table" but nothing shows up in there even though I have 2 Assignments already input.

You have 2 Assignments in which table? The portal will show related records from the join table. You can create these directly in the portal, by selecting an assignment and giving it a grade (of course, the relationship between Students and the join table must allow creation of new join records).

[cwest91]

Thanks again for the help. I have been mucking around and now I am able to enter a record into the Join Table (I renamed it grades) and if I put in an assignment_ID and a student_ID then I am able to get the records to display in the portal and I can give the students a grade. I still have 2 problems:

1. I still cannot enter anything into the portal directly except for in the Mark field. I get no response when I click on the other fields.

2. I want the assignments to populate to all the students.. Right now when I put in a student_ID then the assignment only appears for that student. Ideally I would like that assignment to appear for all students and then I can run down my list and give them all a grade.

Here is what I've done so far...

http://dl.dropbox.com/u/1687280/Gradebook.com.fp7

(For some reason it said that I can't upload the above file onto this forum??)

[comment]

I still cannot enter anything into the portal directly except for in the Mark field.

The only thing you should enter into the portal is the AssignmentID and the Mark (both from the Grades table). You don't have the AssignmentID field in your portal.

Ideally I would like that assignment to appear for all students and then I can run down my list and give them all a grade.

That too is possible, but requires a little extra work. Here's an example of one way to do it:

http://fmforums.com/forum/topic/31495-portal-pre-population-with-records/page__p__142456#entry142456

[cwest91]

Thank you so much. I will give that a try and let you know how I make out.

Thanks again..

[cwest91]

Thanks to your help, I now have the portal working properly - I mean I can enter data into it and I can give each student a different grade for their assignment. I'm still struggling with the second part of the solution.

Ideally I would like the contents from "Assignment Name" to be converted to a field on the students layout- this would make it very easy for me to enter grades for assignments- I would just look at it in table view and I would find the correct row for the students paper I had in my hand. (That would be easier than searching for a students record either using spotlight or navigating through the records until I find the correct student)

My question is: Can I do this with a portal?? and if I can't is there a huge downside (besides the fact that I don't get to keep any of my assignment data) to just creating a field for every assignment in the students table? I know I will end up with one huge table , but will there be any other problems??

Thanks again in advance,

Chad

[comment]

is there a huge downside (besides the fact that I don't get to keep any of my assignment data) to just creating a field for every assignment in the students table?

Yes. You will also make it very difficult to produce any kind of stats, e.g. student's average grade, the average grade of an assignment, etc.

The problem you are facing is a problem of user interface, not data structure. It can be solved in several ways, with different levels of complexity. The two simplest solutions (IMHO) are:

1. Add a global gAssignmentID to the Students table, and define a relationship to a new occurrence of the Grades table as:

Students::StudentID = Grades 2::StudentID

AND

Students::gAssignmentID = Grades 2::AssignmentID

Allow this relationship to create new records in Grades 2.

In a list/table layout of Students, place the gAssignmentID in the header, and Grades 2::Grade in the body. Select the assignment in the global field to view/add grades for that assignment.

This is the method used in the file I have linked to earlier.

2. The other method is scripting the creation of new assignments, with the script pre-creating a new record in Grades with the new assignment's ID for all existing students.

[cwest91]

Wow! Thanks for the solution! I am almost there. I have only discovered one thing. When I change the gAssignmentID in the header I can edit 1 record and give it a grade and then the gAssignmentID header disappears and I have to select the assignment again before I can edit the mark for the next student. And it seems that the last mark I enter for any given student is the default when I click into that cell again.

is there any way to set the gAssignment ID to stay the same while I enter all of the students data..

Thanks again- I can almost see the light at the end of the tunnel and once I get this done I can start marking assignments and entering grades..

Chad

http://dl.dropbox.com/u/1687280/Gradebook.fp7

[comment]

I am almost there.

Yes, you are. The only problem is that gAssignmentID is NOT a global field. In Manage Database, select the field, then Options… > Storage > Global Storage.

BTW, you can attach the file here, if you zip it first.

[cwest91]

Thanks again for all of your help. (I actually posted twice before but for some reason it still hasn't shown up). I have come up with one more problem that I can't seem to get around.

In my grades table I have a mark and I have added a field for a report card comment

I want the report card comment to use the mark entered to lookup the specific comment for the assignment (I have 4 comment fields in the assignment table)

eg. If they get an "A" then filemaker should look at the related assignment and pull in the Report card comment for A.

Here's what I've done so far:

I have set the "Report card Comment" field on the grades layout to a calculation...

I have set the calculation using the Case function to display the values from the appropriate Comment bank in the assignments section...

The problem:

As you can see from the image... I have all 4 report card comments displayed correctly (from the assignments Table) but the "Report Card Comments" field is for some reason pulling it's results from another assignment record (BEDMas instead of Integers)

But, strangely when I view the information in the portal the portal displays the correct comment for each assignment...

I have tried to set this calculation up as an auto-enter (text type) as field but when I do that the "Report Card Comments" field is blank..

Any thoughts or suggestions would be greatly appreciated..

Thanks

[comment]

As with anything that is 'one' to 'many', you should have a table of Comments, related to Assignments by AssignmentID. Show the correct comment through another relationship matching:

Grades::AssignmentID = Comments 2::AssignmentID

AND

Grades::Mark = Comments 2::Mark

[cwest91]

Thanks that worked like a charm. I have encountered one more problem in that in my Mark Entry section I am using the gAssignment field to choose which assignment to grade. I choose the assignment from the list but then all reference to the name disappears and all I can see in that field is the assignment ID.

I wanted some visual feedback - just to be sure that I am working on the right assignment so I thought I would just add the assignment name field, but that doesn't seem to update when I pick a new assignment from the gAssignment_ID list.

I figure there has to be something simple... I thought of doing a calculation ( get gAssignmentID) Lookup Assignment Name from the assignments and the result to a new field (Assignment Name - Mark Entry). But I haven't had any luck...

See image

As with anything that is 'one' to 'many', you should have a table of Comments, related to Assignments by AssignmentID. Show the correct comment through another relationship matching:

Grades::AssignmentID = Comments 2::AssignmentID

AND

Grades::Mark = Comments 2::Mark

[comment]

A simple - if a bit clunky - solution would be to format the field as a pop-up instead of drop-down.

Otherwise you'd need to link another TO of Assignments to the global.

[cwest91]

Thanks again.

I totally don't understand why adding another TO made a difference in this case- But it works as I was hoping.

Does that mean if I add multiple TO's that I can grade multiple assignments at once?? That would be very handy as sometimes I break down a test into three different assignments based on what I want to report on but it would be nice to enter all those marks at once (while I have the students test in my hand)

I will do some experimenting with this...

Thanks again for everything,

Chad

[cwest91]

Hi there,

I have had a quick look around the forums and can't seem to find anybody with my problem. I have set up a gradebook for my class that tracks Students, Assignments, Assignment Grades, Comments and Report Card Grades. I am trying make a report that matches my districts report card but I have not been able to.

My report needs to look like this:

I have managed to get two reports that are close, but not what I need.

Report Comments:

This report is a sub-summary of the "Grades" table which is sorted by Subject and based on the mark it pulls the appropriate comments from the comments table into the "Report Card Comment" field. The problem with the report below is that I can't seem to get the "Letter Grades and Effort Marks" from the "ReportCardGrades" table to appear next to the subject (in the sub-summary by subject).

Report Letter Grades:

This report is a Sub-summary of the ReportCardGrades table and it is sorted by subject and pulls the letter grades down for each subject. The problem is that I can't figure out how to get the appropriate "report card comments" from the grades table to appear underneath each Subject.

I have been tinkering around and creating TO's and creating relationships and ID fields for 2 straight days now and I have not been able to find away to accomplish my goal. Any help would be greatly appreciated.

Gradebook Copy.zip

Thanks alot,

Chad

[comment]

I thought this was already solved here:

http://fmforums.com/forum/topic/76081-re-create-a-new-field/page__p__359058#entry359058

[Lee Smith]

I merged your two topics.

Please do not multiple post your questions.

[cwest91]

I thought this was already solved here:

http://fmforums.com/forum/topic/76081-re-create-a-new-field/page__p__359058#entry359058

I'm not sure where that link was supposed to lead- I think because my topics were merged (I'm very sorry about that) that the link was somehow invalidated but I'm guessing it pointed back to one of my previous posts. Since that post I have made a couple of changes.

1. I created a table called ReportCardGrades which stores a "letter Grade" and an "Effort" mark for each student for each term (1,2,3 and F). (The records in this table are created using a script that creates a record for each student for each subject for each term)

The solution I think that you pointed me too solved the first part- it allowed me to do a sub-summary report using the "Grades" table which pulls out the appropriate comments for each Subject.

What I need to do now is to somehow add the "ReportCardGrades" for Term 2 to that same report (Although ideally I'd like to export it to as a csv file for a mail merge with pages because I think it would be easier to do my final edits in an actual word processor).

Thanks again for all your help in advance and I apologize for the multiple posts.

Chad

[Lee Smith]

Read your Private Messages!

Also, note that you are the second page of this thread.

[cwest91]

Read your Private Messages!

Also, note that you are the second page of this thread.

Hi Lee,

How do I read my private messages?? I didn't even know that there was such a thing. I looked under my account and there is something called messenger but there is nothing new there (Actually there is nothing at all)

Thanks

Chad

PS: I tried to send this to you as a private message but it said that "the user Lee Smith cannot receive any more private messages" or something like that..

[Lee Smith]

Not sure what happened, you should have received an Email, and a 1 should have appeared in the upper right of the browers where you see your icon. I'll notify Ocean West about this because you should have had no problem sending me a PM.

Lee

[comment]

I'll notify Ocean West about this because you should have had no problem sending me a PM.

Please include me too:

http://fmforums.com/forum/topic/75442-design-questions/page__st__20__p__360855#entry360855

[cwest91]

I'm still struggling with how to get the "Letter Grades" and "Effort" marks from the ReportCardGrades table and combine them with the comments for each subject.

I'm wondering if this will require a script to combine these two tables into one?

Thanks again for any advice that points me in the right direction