I'm not sure exactly what you are counting.
What do you mean by "seen before"?
A student presumably may attend for more than one year for more than one term. And is "year" the accademic year or is it based on the calendar date?
Hi Phil and thank you for the reply.
We have four terms each year. During each term, if a student hasn't completed their homework - they recieve a "homework support" record. These records are used during lunch time, where the student attends the homework room and they are marked as having attended.
Students may also receive a "work variation", which is simply a record which shows that they were either late for class, or forgot a text book for example. The student does not need to do any more (besides correcting it so it doesn't occur again ;->). Both of these items go into the same table
These records are collected every term. What I need to do is to count for each student, the number of homework and work variations they have recieved for that term (which needs to be from the same year). As an example:
It is currently Term 3. Jenny comes to school and forgets her text book. The teachers records this in the database as a work variation. Jenny (identified in the database by a student ID) needs to have a 1 added to the TERMCOUNT field (as the database checks to see if Jenny's TERMCOUNT record has been seen before - which it hasn't)
Jenny comes to school the next day (still term 3) and hasn't completed her homework. The teacher records this in the database against Jenny's student ID. The database check to see if any other records have been submitted for term 3 and it finds one. The last number entered was 1. The TERMCOUNT field for this record only now needs to be 2. The TERMCOUNT field for the original record is still 1.
And so on until Term 4 arrives and Jenny forgets to do her homework again. The TERMCOUNT resets back to 1 and goes on again as before.
I would like this to be a calcuation if possible, as the term dates could be changed which would throw out the acurracy of all the records. It needs to be based upon the year and the term.
The year is a value such as 2012, 2013 etc.... not their grade. It is based upon the calendar.
Yes. A student may attend the College for thirteen years from Prep (-1) to year 12 (12).
I am attaching a screenshot of some of the fields in the homework table so you can see. I will attach another view in the next post, which shows a report I have created. This report really needs to have the TERMCOUNT show for each student and the largest TERMCOUNT value show at the top for each student (I am sure I can work this part out once I have the TERMCOUNT working correctly ;->)
Here is the second image.
You can construct a list view, summary report based on teh homework support table. You can sort these records first by student ID, then by the type field. A summary field defined to count the Student ID field (any field that is never empty will work), can then be placed in a sub summary layout part "when sorted by Type", to show the total records for that student for that type. This report need not list the indivdual homework support entries, you can remove the body layout part and just have the sub summary part(s).
To limit the results to given term, you can either perform a find for only records for a given term or you can sort your records first by term, then by student ID, then by type.
Here's a tutorial on summary reports that may be helpful if this type of report is not familiar to you: Creating Filemaker Pro summary reports--Tutorial
Thanks Phil. What I didn't realise is how the option to "Restart summary for each sorted group" ,"When Sorted by" worked. I now have a much better understanding of it and have got it working.
What I now need to figure out is how to access the value <<SummaryOfRecordNumber>> per student such that when it reaches 5, it sends en email and marks a field that it has done so. The same will occur when it reaches 10 and 15.
Can I access these values in a script which I would like to run a specific time each night? The script will look to see if any records have reached 5 or more, then check to see if it has emailed this record before, then if it hasn't, it will send an email (still working on who it needs to go to).
I am thinking if I had a script first, that checked the current date, to see if it was in a term, and if it was in a term it then got the term number and year (from the same row that the date range is in) and performed a related record search.
It then needs to be able to look for any <<SummaryOfRecordNumber>> which is higher than 5 and if it is, send an email and mark a field that it was done. If it isn't it would go to the next record.
I'd do some nesting if it was greater than 10 and 15 the same way.
An image of the field I am talking about.
To access a sub total computed by a summary field requires one of two methods:
1) Find and sort your records just like you would for your report, then the GetSummary function can be used to extract the sub total. Specify the same field as the "break field" parameter in this function as you specified for the "When sorted by" field in the set up for the sub summary layout part.
2) refer to the summary field from the context of a related table where the relationship matches to only the records for which you need to compute this total. If you can set up such a relationship, your script may be simpler as you may be able to loop though a table of student records instead of groups of "Homework support" records that you'd end up doing with 1).
The trick for making 2) work usually requires a relationship that uses more match fields than just the StudentID. You can define some global fields in that same table--such as a TermID field, and then, you can set the global field to a specified value once and all the student records match to only related records where studentID matches AND the TermID matches the ID in the global termID field.