3 Replies Latest reply on Nov 19, 2009 10:47 AM by philmodjunk

    Certain fields only appear once in a report?

    Peakoverload

      Title

      Certain fields only appear once in a report?

      Post

      I have a database that records any defects that occur on projects and which member of staff was responsible for it and it also records any training that was given to members of staff.

       

      I then have a list view layout where I can search for a member of staff and it then shows me a list of all the defects and training they've had. This all works well enough but I only need the fields from one particular table to appear once in the report whereas at the moment they repeat for each record found.

       

      What I mean is that I have a StaffDetails table which records their name, position, date they joined and their photo. I then have a defects table that records details of the defect and a training table that records details of any training given.

       

      In my report layout I have the fields from the StaffDetails table and then the relevant fields from the defects and training tables. When I search for a member of staff in this layout I want the report to appear like this:

       

      Staff Name

      Staff Position

      Staff Photo

      Staff Joined Date

      Date of defect

      Details of defect

      <these last two fields repeat for however number of records are found>

      Date of training

      Details of training
      <these last two fields repeat for however number of records are found>

       

      However what currently happens is this for every single record found

       

      Staff Name

      Staff Position

      Staff Photo

      Staff Joined Date

      Date of defect

      Details of defect

      Date of training

      Details of training

       

      So in other words for each record found all the above fields appear. Also if say a member of staff has 10 defect records and only 1 training record then the training record appears alongside each defect record.

       

      Is there a way to produce a layout where it will only show the staff details at the top of the list, then show any defect and training records in date order without repeating any records if there are not the same amount of training records as there are defect records?

       

      </newbie>

       

       

        • 1. Re: Certain fields only appear once in a report?
          philmodjunk
             Even though I recognize your project :smileywink:, please describe which table your report layout is based on and list each related table and their relationships. It looks to me like you are trying to list records from two separate tables and this can be a challenge in Filemaker.
          • 2. Re: Certain fields only appear once in a report?
            Peakoverload
              

            Hi again Phil

             

            Okay, I have 3 tables and 1 join table:

             

            Defects Table has the following fields:

            BookTitle

            Talkingbooknumber

            Date

            EmployeeID

            Defect

            RetrainingRequired

             

            Training table has the following fields:

            Training

            Date

            Training ID

             

            StaffDetails table has the following fields:

            Name

            Joined

            Left

            Notes

            Picture

            EmployeeID

            Job

            gJob

             

            StaffTraining table is the join table and has the following fields:

            EmployeeID
            TrainingID

             

            The relationships I have are:

             

            Defects:EmployeeID::StaffDetails:EmployeeID

            StaffDetails:EmployeeID::StaffTraining:EmployeeID

            Training:TrainingID::StaffTraining:TrainingID

             

            The report layout I have created is indeed based on more than one table, 3 actually <eek>!

             

            From StaffDetails I'm using

            Name

            Joined

            Picture

            Job

             

            From Defects I'm using

            BookTitle

            Talkingbooknumber

            Date

            Defect

            RetrainingRequired

             

            From Training I'm using

            Training

            Date

             

            It's not the end of the world if I can't get this working as I want because at least all the information is presented to me, it's just easier to read if so much of it isn't repeated throughout.

             

             

             

             

            • 3. Re: Certain fields only appear once in a report?
              philmodjunk
                

              Actually, you are basing your report on only one table--more precisely a Table Occurrence. This is the name you see listed in Layout Setup... You can then reference data from other tables via their relationships.

               

              I've color coded your tables and report fields to show where they come from if I am interpreting your post correctly:

              Tables:

              StaffDetails

              Defects

              Training

               

              Report:

              Staff Name

              Staff Position

              Staff Photo

              Staff Joined Date

              Date of defect

              Details of defect

              <these last two fields repeat for however number of records are found>

              Date of training

              Details of training
              <these last two fields repeat for however number of records are found>

               

              The problem here is that you have two tables providing "details". If you base your layout on Training, you can see the training details--but not the defect details. Base it on Defects and the reverse becomes true.

               

              There are two possibilities:

               

              Base your layout on either Details or Training, and display the other "Detail" records in a portal sized to be many rows tall. Set this portal and all objects below it in the same layout part to "slide up, also reduce size of enclosing part". This report will not show sliding until you either preview or print the report and if there are more "details" than will fit in the portal, the additional records will not be visible.

               

              Create a new table with sufficient fields that a given record can contain either defect or training data with the other fields blank. Create a script that imports records from both tables into this table and then sorts them so that the records are appropriately grouped.