6 Replies Latest reply on Aug 2, 2011 11:31 AM by philmodjunk

    Printing related data

    ChristopherGray

      Title

      Printing related data

      Post

      I have created a database with separated tables for Pupils and Courses. Each Pupil table contains the usual fields for name, etc and the table for Courses lists each course numbered C01, C02 etc. and gives the name. In the Pupil table there is a field for Chosen Courses, that field is in tabular form and each Course (C01 etc) is separated by a hard return. The Courses Chosen field is Related to the number of the course in the Courses table. I want to print out each pupil and show a list of the courses chosen and the name of each course. The layout is based on the Pupil table and shows the pupil name and courses chosen but when adding the related Course Name field, only the first name, from the list of chosen courses names is shown. How can I get round this. I am using FM Pro 10.

        • 1. Re: Printing related data
          philmodjunk

          Return separated lists can be useful at times, but here, the way they work in a relationship is making thing more difficult for you. It would be best to change the structure of your database so that you no longer have this list of course ID's in a single field.

          Pupils---<Pupil_Course>------Courses

          Pupil::PupilID =Pupil_Course::PupilID
          Courses::CourseID = Pupil_Course::CourseID

          With this structure, a portal to Pupil_Course on the Pupil layout will list all the courses for that Pupil. You can add the course name from courses to the portal to display the name. A similar approach can be used to place a portal to Pupil_Course on the Courses layout to list all pupils enrolled in that course.

          For you report, I'd set up a summary report based on the Pupil_Course table with Pupil and Course Names from the other two tables added to this layout to provide the needed information.

          • 2. Re: Printing related data
            ChristopherGray

            Being fairly new to Filemaker I am having some difficulty getting my head round your reply. How many different relationships are you showing or is #1 mine and the rest yours?

            With about 500 Pupils and 70+ courses I don't want a complete rebuild unless absolutely necessary. With your suggestion, are chosen courses added to the portal on the pupil table or how does the data entry work.

            The database as designed, if not the best solution, works well apart from the problem outlined.

            Suggestions please in idiot-level English! :-)

            • 3. Re: Printing related data
              philmodjunk

              I think you'll need to modify the design of your database in order to get this to work for you.

              I don't think you'll need to start over so you aren't looking redoing all your current work, but as I can't see the total design of your database from here, that's "informed speculation" on my part.

              There are two relationships in my last post one from Pupils to Pupil_Course and one from Courses to Pupil_Course. Pupils and Courses should be tables that already exist in your system. Pupil_Course is called a "join" table and is necessary to link your data in a format that makes the report you want possible. It can also make for a much simpler system for managing the enrollment of students in each course. Since you have this data already stored in a text field, a script could even be written to update the new join table with the appropriate information so that you don't have to go through the records for all 500 pupils an manually link them to the courses table by adding records to Pupil_Course.

              Here's a demo file you may want to look it. It matches "Contracts" to "Companies", but if you were to rename those two tables as "Pupils" and "Courses", you'd have an example file of what I am recommending to you.

              http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

              • 4. Re: Printing related data
                ChristopherGray

                Many thanks for your help

                • 5. Re: Printing related data
                  EmmettOliver

                  Hi Phil,

                  Well, I have the tables, layouts and relationships working as you suggested.  I have the following: 

                  Report---<Family>-----Child

                   With this structure, a portal to Child on the Family layout I can use the following script:

                   If [ Not IsEmpty ( Child::_kf_FamilyID )

                      Go To Related Record [Show only related records; From table: Child; Using layout: "Pat. Reg." (Child)]

                      Print [Restore ; no dialog]  // Specify the records being browsed option here

                      Go to Layout [original layout]

                  End If

                  It's awesome, but now I want to be able to select only one of or say two of three related records and only print those.  How can I set us a check box for each row, then say print only those with a check box selected?

                  Thanks again for all of your guidance,

                  Emmett

                   

                  • 6. Re: Printing related data
                    philmodjunk

                    After your Go to related records step, add this code:

                    enter Find mode [] //clear the pause check box
                    Set Field [Child::SelectionField ; "*"]
                    Set Error Capture [on]
                    Constrain Found Set []

                    This will elminate the related records that were not selected.

                    Note that this approach works best in single user systems. If you are sharing the database over the network, the selections you make and the selections another user makes could interfere with the results should both of you being working from the same Report record.

                    PS: if _kf_FamilyID is a field of type number, you can simplify your If step to be:

                    If [Child::_kf_FamilyID]