3 Replies Latest reply on Apr 2, 2010 3:07 PM by philmodjunk

    conditional counts using GTRR problem

    Dand

      Title

      conditional counts using GTRR problem

      Post

      Hi everyone,

       

      I’m relatively new to Filemaker and databasing in general and I’m having some problems getting a script to work.  I’m using Filemaker Pro 9 on Windows XP SP3 and the database I’m working on is only meant to be used locally on a single machine at this point.  The database tracks students in terms of enrolment (program, full time, part time, citizenship, gender,  etc).  We’d like to be able to produce enrolment counts that represent each possible combination of categories (i.e.  we’d want to know how many male International students are enrolled full time in program X during term Y).  The difficulty I’ve been having with this is that the various fields that need to be checked exist across a few different tables.   The Tables I’m using look something like this:

       

      Student                                                                               

      • Student ID    (number)
      • Citizenship
      • Gender
      • Term 1
      • Term 2
      • ...

      Semester

      • Student ID
      • Term
      • Program
      • Attendance     ----  (full time or part time)

      Count

      • Program
      • Term
      • Several count fields (ie LMFT – the count field for Local Male Full Time students)

       

      The relationships graph looks like this:

       

      Student ---------------- = ------------------ Semester
                      Student ID          Student ID
                      Term 1                  Term
                    ---------------- = ------------------  Semester 2
                      Student ID          Student ID
                      Term 2                  Term
                   ---------------- = ------------------ Semester 3
                      Student ID          Student ID
                      Term 3                  Term

       

       

      Count ---------------- = ------------------Semester 4 ---------------- = ------------------ Student 2
                     
      Program         Program                                    Student ID          Student ID
                      Term               Term
                  ---------------- = ------------------ Count 2
                     
      Term                Term         

      Where the Student TO is connected to the Semester TOs 1, 2, and 3 for data entry and display purposes.  While the Count TO is connected to Semester 4 which is in turn connected to Student 2 for the purposes of the script.  The Count TO is also connected to the Count 2 TO in order to display portal records of different programs during the same term.

      The script is called from the Count Layout (which is based on the Count TO).   So far the script goes like this:

       

      Go To Related Record [Show only related records; From table: “Semester 4”; Using layout: “Gen Sem” (Semester)]   (with the ‘match current record only’ option selected)
      Set Variable[$LMFTc; Value: 0]
      Set Variable[$LFFTc; Value: 0]
      Set Variable[$IMFTc; Value: 0]
      Set Variable[$IFFTc; Value: 0]
      Set Variable[$LMPTc; Value: 0]
      Set Variable[$LFPTc; Value: 0]
      Set Variable[$IMPTc; Value: 0]
      Set Variable[$IFPTc; Value: 0]
      Loop
                      If [Semester:: Attendance = “FT”]
                                      Go To Related Record[From table: “Student 2”; Using layout: “Students” (Student)]
                                      If[Student::Citizenship=”Local”]
                                                      If[Student::Gender=”M”]
                                                                      Set Variable[$LMFTc; Value: $LMFTc + 1]
                                                      Else If[Student::Gender=”F”]
                                                                      Set Variable[$LFFTc; Value: $LFFTc + 1]
                                                      End If
                                      Else If[Student::Citizenship=”International”]
                                                      If[Student::Gender=”M”]
                                                                      Set Variable[$IMFTc; Value: $IMFTc + 1]
                                                      Else If[Student::Gender=”F”]
                                                                      Set Variable[$IFFTc; Value: $IFFTc + 1]
                                                      End If
                                      End If
                      Else If [Semester:: Attendance = “PT”]
                                      Go To Related Record[From table: “Student 2”; Using layout: “Students” (Student)]
                                      If[Student::Citizenship=”Local”]
                                                      If[Student::Gender=”M”]
                                                                      Set Variable[$LMPTc; Value: $LMPTc + 1]
                                                      Else If[Student::Gender=”F”]
                                                                      Set Variable[$LFPTc; Value: $LFPTc + 1]
                                                      End If
                                      Else If[Student::Citizenship=”International”]
                                                      If[Student::Gender=”M”]
                                                                      Set Variable[$IMPTc; Value: $IMPTc + 1]
                                                      Else If[Student::Gender=”F”]
                                                                      Set Variable[$IFPTc; Value: $IFPTc + 1]
                                                      End If
                                      End If
                      End If
                      Go to Record/Request/Page[Next; Exit after last]
      End Loop
      .....
      after this point the script will return to the Count record that called it and will set the appropriate fields to the tabulated count values.

       

      The issue I’m having appears to be at the lines marked in red.  When the script executes, it will get to this point and return an error message:

       

      “This operation could not be completed because the target is not part of a related table.”

       

      The test sets (records) that I’ve set up all have a corresponding Semester-Student match record, so as far as i understand the records should be accessible through the relationship but I’m not able to access them using GTRR and I don’t understand why.

       

      The line marked in green is a line of code that i think there may be an issue with, but since the red lines never execute properly I’ve been unable to check.  I think it might be ok because I’m assuming that GTR/R/P will increment the found set index as opposed to the record index, which in the script should change which Semester record is being looked at as opposed to moving to the next sequential, non-related Student record (since a student record is current when that line is called).

       

      Anyways, thanks a lot for any help with this, I’ve been looking all over and i can’t seem to figure out what I’m doing wrong. 























        • 1. Re: conditional counts using GTRR problem
          philmodjunk

          You'll get that error message because your starting table occurrence at the time those GTRR's execute is "Semester" and it is not related to Student 2. That's your starting TO because your first GTRR specifies a layout based on Semester.

           

          If your first GTRR specified a layout based on Semester 4 instead of Semester, it should work.

          • 2. Re: conditional counts using GTRR problem
            Dand

            Awsome.  I just tried it out by making a hidden layout for the Semester 4 TO and it works perfectly.  I also ended up adding a line to return to the semester 4 TO after the if statements and before the GTR/R/P line so it wold increment the found set and not the student record index (i'm glad the found set wasn't lost by switching TOs).  Anyways, thanks again eh.  I really appreciate it.

            • 3. Re: conditional counts using GTRR problem
              philmodjunk

              Now that you have that error fixed, you should add some error trapping for cases when there might not be any related records for GTRR to go to. If there are no related records, GTRR stays on the same layout and that can result in chaos when the subsequent scripts steps execute on a completely different table and/or set of records than you intended.

               

              You can add a get ( LastError ) step after each GTRR. If the function returns 0, GTRR executed correctly.

               

              For those who want to dig deeper:

               

              Go To Related Records is a very useful tool, but can be hazardous if you don't properly trap for errors. To learn more about GTRR, click the following link:

              The Complete Go To Related Record