2 Replies Latest reply on Jan 8, 2015 9:57 AM by jwshelton22

    Calculation to show test scores per Student

    jwshelton22

      Title

      Calculation to show test scores per Student

      Post

      We are in FM13Adv on Windows.

      There is a Student table with 10 Students and a Tests table. Each test is made up on 30 to 200 questions. There are 15 tests in all. There are 1200 answers in all.

      I need to show by Student the grade per test. I would like to display the scores along side each other horizontally on the each Student record in the Student table.

      I can get it to work for each student but only for the first test.
      Relationship is
      Student ID pk  x  Student ID fk
      Test ID = Test ID

      I can do this with a script, but seems like a calculation would be cleaner.

      I am not sure how to go from here.

      Thanks,

      Jim

        • 1. Re: Calculation to show test scores per Student
          philmodjunk

          Option 1:

          use this relationship:

          Students::Student ID pk  =  Tests::Student ID fk

          Put  a row of 15 one row filtered portals to Tests on your Students layout. Set up a portal filter expression for each portal that selects for a specific test.

          Option 2:

          Create a series of 15 occurrences of the tests table in your relationship graph. The relationships for the first two occurrences would look like this:

          Students::Student ID pk  =  Tests::Student ID fk AND
          Students::ConstTest1ID = Test::Test ID fk

          Students::Student ID pk  =  Tests::Student ID fk AND
          Students::ConstTest2ID = Test::Test ID fk

          The ConstTest1ID, ConstTest2ID fields would be either global or calculation fields where you specify the same test ID value for all your student records. You then put your test score field from Each of these table occurrences in a row on your layout.

          Option 3: Set up 15 calculation fields that use ExecuteSQL to access the correct record in Tests.

          • 2. Re: Calculation to show test scores per Student
            jwshelton22

            Thanks, I am going for option 3. Need more experience with ExecuteSQL.