9 Replies Latest reply on Jan 19, 2012 12:49 PM by RayCologon

    weird problem with script


      I need to calculate the number of student absences for a specific range of dates.

      I have two tables. Students and Attendance, joined by student number. Here is the script.


      • Go to Layout [ “Students” (Students) ]
      • Go to Record/Request/Page [ First ]
      • Loop
      • Set Variable [ $Num; Value:Students::STUDENT_NUMBER ]
      • Go to Layout [ “Hcts” (Hcts) ]
      • Set Error Capture [ On ]
      • Enter Find Mode [ ]
      • Set Field [ Hcts::STUDENT_NUMBER; $Num ]
      • Set Field [ Hcts::ATT_DATE; "8/16/2009...7/1/2010" ]
      • Perform Find [ ]
      • If [ Get(FoundCount)=0 ]
      • Set Field [ Students::Count 9_10; "" ]
      • Go to Layout [ “Students” (Students) ]
      • Go to Record/Request/Page [ Next; Exit after last ]
      • Else
      • Set Field [ Students::Count 9_10; Get(FoundCount) ]
      • Go to Layout [ “Students” (Students) ]
      • Go to Record/Request/Page [ Next; Exit after last ]
      • End If
      • End Loop


      It looks that the script calculates accurately for some students but not for others. I.e. a student has 11 absences, but the found count stores 280. However, if I run the script for this specific student only, it calculates the correct number. What could be the issue and how can I fix it?



        • 1. Re: weird problem with script

          Hi, andretru2 (Andre?).


          Looking at your script, presumably, you have two tables, Students and Hcts. I don't know how these two are joined, but you may have an error based on your relational structure.


          Look at this section of your script:


               If [ Get ( FoundCount ) = 0 ]

                    Set Field [ Students::Count 9_10 ; "" ]


          You have a Set Field step here that addresses a field in the Students table. However, if you have no records in the found set, then FileMaker might not know what record in the Students table to address. I don't know how these are joined, again, but this may be the problem.


          If I were to write this, I would probably do it more in this fashion:





          Perform Find [ ]

          Set Variable [ $count ; Get ( FoundCount ) ]

          Go to Layout [ "Students" (Students) ]

          Set Field [ Students::Count 9_10 ; $count ]

          Go to Record / Request / Page [ Next ; Exit after last ]


          That will at least isolate down whether the problem is coming from a relationship issue.


          The other thing that looks a little suspicious is the date range search. What is "ATT_DATE"? Is that the attendance date? If so, you might be running into an issue where you're finding all the student's dates of attendance rather than his absentee dates. I don't know your table structure or data entry, but if you say you're sometimes getting lots of records instead of the correct count, that may be the issue.





          • 2. Re: weird problem with script

            Hello andretru,

            I agree with Mike about the Set field and the relationship issue.

            I would also like to caution about Set Error Capture. Turn Set Error Capture to Off right after you have done your find.  There are a lot things that can happen when it is just left On.  That would still NoT allow a message to pop up if nothing is found, and continue on with the script.  Just something to keep in mind for future scripts as well.


            That might help detect what Mike was saying that it could not find a related record in the Students table if none is found to set the Count 9_10 field.

            • 3. Re: weird problem with script

              Let me suggest a different approach altogether: go to the Attendance table, find all absences in the given range and summarize the results by StudentID, using a summary field in the Attendance table.


              Producing a report showing existing data is a non-event in terms of your data. You should not be modifying the Student records for this purpose. Not to mention that if two users wish to produce two different reports, they will clash with each other.

              • 4. Re: weird problem with script

                You could always do this by calculation + summary and filter the records by the relationship. using 2 global fields in the students table (begin date and end date) related to corresponding date fields in the attendance table using the ">" and "<" operators for the filter. A simple calculation or summary will give you the data you are looking for without having to write teh script at all as well as being constantly live data.

                • 5. Re: weird problem with script
                  Stephen Huston

                  I agree with what's been said before, but would add:


                  Perform your Find (Enter Find Mode, Set Date Range, Perform Find) while already on a reporting layout based on the table in which the Find is performed. This removes the issue of trying to capture data and set it elsewhere for the report. You will need a summary field for counting the records found.


                  Try to avoid having a report  modify the data records it is reporting. A report is supposed to find and report what's there, not change the records being reported, at least in most cases. If you need to capture the results into another field, set a global field with the value for the duration of the report. That won't cause modification dates/accounts to change.


                  The first rule for simplifying a report is to do your reporting from the table occurance with the finest data detail you need to report, not from a related table.


                  Best of luck

                  Stephen Huston

                  • 6. Re: weird problem with script

                    While I do agree with Stephen, I have found that the larger the data set becomes, the slower that option becomes as well. While the related records method allows you to view the information on the student's layout as well as in the report.


                    Either way will work and get you the detail you want. FileMaker is good (and bad) about giving you a thousand ways to skin a cat.

                    • 7. Re: weird problem with script

                      Excellent advices. Thanks everyone!

                      • 8. Re: weird problem with script

                        I can't say what the problem is. It's not obvious from the script.


                        One comment: I can't see what advantage there is in


                        Set Field [ Students::Count 9_10; "" ]

                        Go to Layout

                        Go to Record/Request/Page



                        It doesn't seem to be required. if the found count is zero the number field can be formatted to not display zeros if that is the desired aesthetic result. Less code for the same result.


                        A different way to do this is to find all students by the date range, then sort by students and use sub-summary reports to generate the numbers. If it is necessary to store the results then do the find and the sort and walk the records, if you want to search for it, it's called the fast summary technique by Mikhail Endoshin.




                        • 9. Re: weird problem with script

                          I tend to agree with Stephen ... and Aryden.   


                          Stephen is correct that you shouldn't, in general, be modifying the records you're reporting on. (And comment is right that you should be cautious about record lock collisions in a multi-user environment.) But at the same time, summarizing on related data sets is a performance hit that can become prohibitive if the data set size starts to climb.


                          Solution? One way around it is to consider a separate Reports table, where you count up the records (using Count or Sum), then store the results for later retrieval and reporting. This creates the performance advantages of stored data, while simultaneously avoiding modifying the records you're reporting on and the risk of a record lock collision.


                          Of course, all of this is tailored to your need. Separating felines and dermis is multivariant ...     



                          • 10. Re: weird problem with script

                            Hi andretru2,


                            It appears that what you actually require is an AttendanceReport layout based on the Hcts table occurence that has no body part, but a sub-summary part set to activate when the data is sorted by Hcts::STUDENT_NUMBER. In the Hcts table you need a summary field defined to return a Count of the STUDENT_NUMBER field, and you need to place this field in the sub-summary part, along with a name field(s) from the Students table and any other Students fields you want to see on the report.


                            Then, your script need only have four lines, as follows:


                            • Go to Layout [“AttendanceReport” (Hcts)]
                            • Perform Find [Hcts::ATT_DATE; "8/16/2009...7/1/2010"]
                            • Sort Records [Restore, No dialog]
                            • Enter Preview Mode [ ]


                            ...with the sort at line three being on the Hcts::STUDENT_NUMBER field.


                            This approach is not difficult to implement, should not take long to run unless you have tens of thousands of records (and in any case, it should be a good deal quicker than the method you're currently using) and will dynamically produce a summary for any period without modifying data or causing potential conflicts.





                            R J Cologon, Ph.D.

                            FileMaker Certified Developer

                            Author, FileMaker Pro 10 Bible

                            NightWing Enterprises, Melbourne, Australia