4 Replies Latest reply on Aug 18, 2011 8:10 AM by LGC

    Calculation fields not all being evaluated in a relational DB

    LGC

      Title

      Calculation fields not all being evaluated in a relational DB

      Post

      There are 3 related tables:

      1) Student: PK-StudentID

      2) StudentSpecialProgram: PK-StudentID (I only add the StudentIDs to this table that are associated with a special program)

      3) SpecialProgramEnrollment: FK - StudentID

      Relationships:

      Student to StudentSpecialProgram : 1:1; StudentSpecialProgram to SpecialProgramEnrollment : 1:M

      Calculations:

      There are over 20 special programs, any (but not all) students could take part in 1 or more special programs.  In the StudentSpecialProgram table I have a field for each program possible to indicate if the student takes place in that program.  Many students take part in many programs however it appears that only ONE program is flagged per student (possibly the first field calculation to be = true).  Since each calculation has it's own field, I don't understand why each one is not being evaluated.  The calculation is listed below:

      If (SpecialProgramEnrollments::SpecProgID = 954 and SpecialProgramEnrollments::ProgramEntryDate ≥ Date ( 7; 1; 2010);1;0)

      The logic appears to be correct because it is populating one of the program fields for each student, but is not evaluating each field (which has the same logic except with it's respective SpecProgID #) individually.

      I don't want to add the calculation fields to the Student table, because only 4000 of 22000 student listings would contain indicators for special program enrollment, and I need to generate data for any student with any special program enrollment - the "find" gets ridiculous with all the "or" statements necessary to give me all students with any special program enrollment. 

      Any ideas would be appreciated!

        • 1. Re: Calculation fields not all being evaluated in a relational DB
          philmodjunk

          I see a number of basic design issues with the structure of your database, but first to explore the specific issue:

          Are these fields of type calculation or are they number or text fields with an auto-entered caculation? Auto-entered calculations that refer to fields in other records or tables will not automatically update when the data in that referenced field is modified, nor do they assign values to existing records when newly added to your table. (You have to use something like Replace Field Contents with the same calculation expression to update your existing records.) Use fields of type calculation with these expressions instead.

          Now the design issues.

          In the StudentSpecialProgram table I have a field for each program possible to indicate if the student takes place in that program.

          That sounds like a really challenging way to set this up. Here's an alternative that can be a much simpler and much more flexible:

          Use this combination of tables and relationships to document the enrollment of your students in your special programs:

          Students----<SpecialProgram_Enrollment>----SpecialPrograms

          Students::StudentID = SpecialProgram_Enrollment::StudentID
          SpecialPrograms::ProgramID = SpecialProgram_Enrollment::ProgramID

          SpecialPrograms is where you document each existing program. In your case this will be "over 20" such records. The SepcialProgram_Enrollment table is a "join" table that makes the many to many relationships need so that one student can be enrolled in more than one program and each program can enroll more than one student.

          On a Students layout, a portal to SpecialProgram_Enrollment can be used to list and assign SpecialPrograms to that student. On a SpecialPrograms layout, a portal to SpecialProgram_Enrollment can be used to list and assign students to that Special Program.

          This reduces your many different programs in dedicated fields to a flexible set of join table records so that adding a new program is a simple as adding one new record in SpecialPrograms, then using the join table to list the enrolled students.

          • 2. Re: Calculation fields not all being evaluated in a relational DB
            LGC

            Thank you, this is a lot cleaner set up and my 1-1 relationship must be throwing this off?  I'm unclear about one thing though (which I didn't include detail about originally) - I need to be able to indicate if a student was in the special program during a specific time frame (in the SpecialProgramEnrollment table I have ProgramEntryDate and ProgramExitDate).  For example, I may need to compare many other data sets (within the same DB) like absences, GPA, suspensions, etc for a specific time frame that the student was in a program, and I may need to go back historically as well which is why I had created calculation fields to 'flag' the student for X SpecialProgram during the 2010/2011 school year.  I planned to continue to add calculation fields each year to flag for the respective year.  I am using a calculation field type for these fields.  With the suggestion above, how do I link the SpecialProgram table with SpecialProgramEnrollment when their SpecialProgram is determined by a calculation? 

            Thanks so much for your help, greatly appreciated.

            • 3. Re: Calculation fields not all being evaluated in a relational DB
              philmodjunk

              You may need to script that. It's difficult to make detailed description of the proccess without knowing more about the way this data is sctructured, but if you can perform a find to pull up all students that meet the criteria for a given program, a script can then loop through that list and enroll them if not already enrolled in the program. A calculation field, filtered portal and/or filtered relationship may also still be a possible to find students eligible for a given program, but I can't say for sure.

               I planned to continue to add calculation fields each year to flag for the respective year.

              You might want to think about what your database will be like 5 years from now...

              • 4. Re: Calculation fields not all being evaluated in a relational DB
                LGC

                 Thank you for your help and ideas, I was worried I was making it harder than it needed to be - but it sounds like I need to think of a smarter way to handle it.