7 Replies Latest reply on Jul 19, 2013 7:22 AM by philmodjunk

    Providing a count on the number of records



      Providing a count on the number of records



           I have a student database which is collecting information from staff regarding student homework which has not been done and student work variations (which are items such as forgetting a text book etc).

           I have managed to create one table with the student records. I have another table which has the term dates. These tables are linked now so I am able to collect the term number by comparing the date the record was made, with the date range that the term goes for.

           What I now need to do is to record a running count against each student each term per homework or work variation recorded.

           I think the best way to achieve this is to add another field to student homework table which is a calculation (?). What I need to do is this:

           For each record in the table:

           1. Look at the STUDENT ID, YEAR and the TERM and check if they have been seen before.
           2. If these items have not yet been seen, record the value "1" in the field TERMCOUNT
           3. If the item has been seen before, collect the value of TERMCOUNT and increment it by 1 and add it to the TERMCOUNT field.
           4. Go to the next record and start again at step 1.

           The reason I think a calculation would be best is it allows the system to be flexible enough to rework out all the values on the fly, and will not require someone to sit down and change everything (which would become confusing ;->)

           The other reason is that I want to use these numbers in a layout is so it shows the items in order they were added per student, and it should allow me to set off a nightly script which checks the TERMCOUNT field and emails someone when the count is over 5, then again at 10 then again at 15. Each time it does this it would change an emailed field to YES.

           So any ideas on how I might achieve this?

        • 1. Re: Providing a count on the number of records

               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?

          • 2. Re: Providing a count on the number of records

                 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 ;->) 

            • 3. Re: Providing a count on the number of records

                   Here is the second image.

              • 4. Re: Providing a count on the number of records

                     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

                • 5. Re: Providing a count on the number of records

                       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.


                  • 6. Re: Providing a count on the number of records

                         An image of the field I am talking about.

                    • 7. Re: Providing a count on the number of records

                           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.