2 Replies Latest reply on Jul 20, 2011 1:05 PM by philmodjunk

    mulitple Join tables for multiple many to many relationships, how can I get report



      mulitple Join tables for multiple many to many relationships, how can I get report


      Houston I am having a major problem! I can use Tabs on my layout for entering in the information for my MANY, many to many relationship join tables but I want to be able to to do a one page report that will print where it will give all the information. HEre are my tables:


      Containers: ContainerID

      Triggers: TriggerID

      Alarm: AlarmID

      Specialty: SpecialtyID

      Each of these tables are joined to the Device table with a middle table so for example

      Containers table is joined to Device table with a table called Device_Contianer and in this container I have DeviceID and ContainerID

      and that is how all the other join tables are setup. It works great when I can put the information in tabs in portals but I need a report of all containers, triggers, alarms etc that are in the one device and I cannot figure out how to get a report like this.

      For some reason I think I have my database setup horribly wrong or have made it too complicated. I would really appreciate some help, guidance, tutoring..... ANYTHING.... I have to get this thing together and deployed using filemaker go (which is a whole other bag of worms). I just need to get this part together so my guys can start using it and then I can figure out how to go about doing an inventory qty in and qty out..... I am just so frustrated and lost. I have done the lynda.com training online and I have the missing manual book (but have not had the time to read it yet).


        • 1. Re: mulitple Join tables for multiple many to many relationships, how can I get report

          I can think of two options both require some thought and effort to make them work for your report.

          1) While we usually try very hard to avoid using portals in reports, sometimes it's the only practical way to get this to work. Base your report on Device and include portals for each of the Join Tables. Make these portals many rows taller than the largest possible number of related records (This is why we try to avoid this approach) and set them to slide up | Resize enclosing part.

          Key facts about sliding layout objects:

          1. It's only visible in preview mode and when you print/save as PDF...
          2. All layout objects below and in the same layout part as the slide/resize field need to also be set to slide up and resize.
          3. Objects in headers and footers will not slide.
          4. Portals will shrink/slide to fit the number of rows of records, but fields within the portal row will not shrink/slide.
          5. Consistent side borders are difficult to achieve with sliding fields.


          2) Make all your join tables different occurrences of the same table and base your report on this table. This avoids portals, but can make design of your report layout very tricky as you will need to include fields from all of the other tables. Sometimes you end up placing fields from different related tables on top of each other. Since only one table other than Device is linked to any one join table, all the "layered" fields save one will be empty for any given record. In other cases, you can place the fields above/below each other but use slide/resize to eliminate the unused space for fields that appear empty because there is no related record from the specified field for that record in the join table.

          Handling the label text for these related fields is an extra challenge. You can use conditional formatting to hide layout text that does not apply to a given join record if you layer the label text just like the fields or you can use calculation fields in the related tables to provide the label text and then they can slide up just like the other fields.

          • 2. Re: mulitple Join tables for multiple many to many relationships, how can I get report

            Note: a combined join table would use separate foreign key (ID) fields for each of the tables to keep separate the links to each table.