AnsweredAssumed Answered

conditional counts using GTRR problem

Question asked by Dand on Apr 2, 2010
Latest reply on Apr 2, 2010 by philmodjunk

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. 























Outcomes