1 Reply Latest reply on May 24, 2012 3:23 AM by GuyStevens

    Creating a calculation to search across related records

    JacquiAdler

      Title

      Creating a calculation to search across related records

      Post

      Hi all!

      I have a student database that contains 3 related tables.

      1-Students (student ID, name, etc)

      2-Course Requests (each student has 8-9 course requests, and this table just contains the student ID and course ID)

      3-Courses (all courses offered at the school including course ID, name, etc)

       

      I am working on developing a letter to be sent home that lists the students course requests.  Where I am getting stuck is that I want certain text to be included only if a student is requesting a particular class.  So, if a student is requesting course A, the letter would say XXXXX, but if the student is not in that course, that text wouldn't be shown.

       

      It seems simple enough, but I can't seem to get it to work for me?  Any ideas?

       

      Thanks!

        • 1. Re: Creating a calculation to search across related records
          GuyStevens

          You can make a report (letter) based on the Course requests table.

          In the body you can put the Course Name from the Courses table, and any other info you would like to add. Make the body only as big as it needs to be, for instance, if your courses are just one line, make it about 18 - 22 pixels high.

          In the header or the footer you could show fields from the Students table. You could make this header as big as you want.

          If you make that report a list view, you'll see all the courses

          There is no need to use calculations, because these fields are related. A Coursed record is available for the students table because of the relationship.

          - You can use merge fields if you want to put either courses information or student information inside a piece of text.
          - You need to perform a find for a certain student ID to see only this students information.
          - You can craete a script. That sets the studentId as a variable, goes to the report layout, performs a find to fint that student, and prints the report.

          If you neeed any help or examples, let us know.

          P.S. I think this file might be an example of this, but I don't have windows open right now, you might want to have a look.

          The name would suggest it might be helpfull  :)
          http://dl.dropbox.com/u/18099008/Demo_Files/ReportAsListFromChildTable.fp7