It's a little unclear how the tables in your database are confirgured. Could you explain a little bit more?
You should have (at least): a Students table, a Tests table, and a Student Test Join table. Adding a test means adding a record to the Tests table and a record for each student in the Student Test Join table. You shouldn't be adding new fields to account for new tests.
Hey DJ, thanks so much for the reply. Sorry about not being clear. This is my first attempt at Filemaker (and databases).
I do have a student table, test table and a student to test join table. (This took me a while to get my head around.)
My first problem: When I create a new test for my students I need to create 60 odd individual records for each student sitting that test in the join table. This I have fixed for now with a very unpleasant looking script button that creates the records on a button click.
My current problem: Each time I create an assessment (above) I want it to appear on a "marksbook" layout so that I can enter results for students in a particular class(es) and compare them with all their previous results.
Student1 Test1result Test2result etc (Test3result field should "pop" up when Test3 has been created)
Student2 Test1result Test2result etc (Test3result field should "pop" up when Test3 has been created)
**edit** I think you were right DJ, I think there is a problem with my database structure design...
1) There are ways of getting around the ugly button issue (are you using FMP Advanced?), but the simplest would be to make it a pretty button. Have it set to run a script that creates the Join records when creating a new Test record.
2) Is Marksbook based on Students? You could have a portal on that layout that shows the related records from the Join table. New records would should automatically in the portal.
1) Hehe will have to work on the pretty button. Yes to Advanced. I have the script and it does the job.
2) My join table between tests and students (result_records) will be a gigantic table with an indivigual record for every test paper completed by students.
How I get that into a standard Marksbook type layout which updates as more tests are added has me stumped.
With Advanced, you can use a Custom Menu so when users select a "New Record" in the Test table, it runs a script that makes the new record and all the records for the Join table. No button needed then.
I'm not sure what a Marksbook looks like. Is it a spreadsheet view? If it is, you'll need to do a little FileMaker trickery. How much trickery depends on whether you'll be entering data in the Marksbook view or just looking at it.
You can use a series of one row portals to organize your list of test scores from the related record into columns so that you have a list of students in column 1 and a columns of student scores in column 2. It's even possible to use the same table to record student scores on all types of assignments.
This technize is sometimes called a "horizontal portal".
If you are using FileMaker 11, you can do some nice things with these portals by using portal filter expressions to control which score appears in which column. You can also set this up so that the related "Student Mark" records is created only as needed when you click on the portal row to add a score for the specific, test, quiz, assignment, or project specified for that column on your layout.
Please note that some fairly exensive work is needed to set up all the details. (I'm putting the finishing touches on a DB to support a Church AWANA (Children's Ministry) program where I use this same basic interface approach to record a child's attendance (Each column is a different date) and their progress through their hand books (each column is a different "section" in their handbooks to record the date when their adult leader signs it off as complete).
Wow, thanks so much guys.
David that is a great idea to clean up my "nasty" button, thanks heaps. Are the horizontal portals the trickery you were talking about?
Phil cheers for putting me on the right track, fingers crossed I can nut it out.
A question regarding these individual portals for each column: Do you have to have a certain number of these portals set up to begin with. Or can they be automatically added when a new test (or in your case week) is added. For example I don't want to have 10 empty portal columns sitting there when I only have 1 test. I.e. with your Children's Mininsty DB do you have a bunch of empty columns for weeks (dates) that are still future?
There's several ways to skin a cat. I'm more of a calculation guy than a layout guy, so I would go a different route than portals.
I would use a repeating field in table view and script triggers to capture and pass data from the repeating field to the related field.
With portals, (and this is also true for a repeating field), you have a fixed number of columns on your layout. Whether the portals are visible or not depends on your layout design. You can also set things up so that the record displayed in each column is selectable. In my DB's attendance screen, I select a club meeting date in column 1 and each of the columns to the right of it display attendance data for subsequent meeting dates as specified on a club calendar. I click arrow keys next to this date field with pop up calendar and I can "scroll" my columns right or left one meeting date per mouse click. Whichever column displays the current/most recent meeting date is highlighted in yellow courtesy of a conditional format.
Thus, your most recent student score might be displayed in column1 with past scores for other assignments might be displayed in columns to the right of it. Click a button for a new assignment/test/Quiz and everything shifts one column to the right with the left most column open for data entry... (That would reverse the traditional left to right chronological order for assignments in a student mark book, but it's an option if you like that idea...)
How I did all that takes a lot of typing to describe both the structure and the trigger controlled scripts that make it happen (and I had to work around a bug I recently uncovered to do so), but I describe this to let you know that it is possible to do in FileMaker. If you're interested in how I made this work, let me know and maybe we can break it down into fairly manageble chunks that focus on the parts of the design that most interest you.