3 Replies Latest reply on Sep 13, 2013 8:07 AM by philmodjunk

    Create a report from multiple related tables

    maddy

      Title

      Create a report from multiple related tables

      Post

           I have a database which contains Patient details. It has mainly three related tables:

           1.       Slide Image which contains patient information with MR no. unique to each patient.

           2.       SurgPath table which has many to one relation with Slide Image table. One patient can have multiple surgical path.

           3.       Image Path table which has many to one relation with SurgPath table. One SurgPath can contain multiple imagepaths. For eg.,

         SlideImage                                    SurgPath                              ImagePath

                                  A1                                AA12                                         path//abc.jpg

                                                                                                                         Path//aaa.jpg

                                                                       AA13                                        path//123.jpg

                                                                       AA14

           I would like to create a report showing the data from these tables. Currently I created a layout on SurgPath table, with related fields from SlideImage and ImagePath tables. In this case, I can see only the first one imagepath entry from ImagePath table. I can use portal in this case to see all imagepaths of a particular SurgPath entry. But, It doesn’t work in my case as  I need to set the number of lines in portal and sometimes a SurgPath doesn’t have any imagepath, so it occupies unnecessary space in the report, which looks weird.

           ·         Is there a way I can show a report looks like a report generated from a single table? I mean, multiple imagepaths related with same MR no. and Surgpath contains duplicate entries from SlideImage and SurgPath tables, which is not possible in case of portal.

           ·         Do I need to create a temporary table which merges all the data from related tables? How to do that?

           ·         If I need to use the portal, how I can show the report without showing the boxes/lines of portals?

           I saw many posts regarding this issue, but I couldn’t understand the solutions.  Please help me in this. Thanks in advance! 

      table_relation.png

        • 1. Re: Create a report from multiple related tables
          philmodjunk

               There are two possible approaches.

               You could base your report on Image_Path instead of Surg_Path. But you've indicated that there isn't always an Image_Path record for a given Surg_Path record and this would automatically omit the Surg_Path records that aren't linked to an Image_Path record. We may, however, have to return to that option if the second approach doesn't work for you.

               Take your portal. Edit your layout so that it has many more rows than you expect to ever need to list all the related Image_Path records. Then set it to "Slide up" and to "resize enclosing part" in the Inspector's sliding and visibility section. This method has it's limitations, but if you can work within them, it's probably your best option for this report.

               Key facts about sliding layout objects:

                 
          1.           It's only visible in preview mode and when you print/save as PDF...
          2.      
          3.           Sliding fields will shrink but not expand.
          4.      
          5.           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.
          6.      
          7.           Objects in headers and footers will not slide.
          8.      
          9.           Portals will shrink/slide to fit the number of rows of records, but fields within the portal row will not shrink/slide.
          10.      
          11.           Fields will slide up only if Top alignment is specified for it and will slide left only if Left alignment is specified.
          12.      
          13.           Consistent side borders are difficult to achieve with sliding fields.
          • 2. Re: Create a report from multiple related tables
            maddy

                 I set the portal to "Slide up" and "resize enclosing part", but it is still visible in Browse mode when the imagepath is not there. As I understood, you mentioned that "Slide up" would be only effective in Preview mode. So in this case, I don't have an option to shrink my portal rows, if there is no rows?

                  

            • 3. Re: Create a report from multiple related tables
              philmodjunk

                   Correct.

                   Which is why I mentioned the first option as that method produces identical results in browse mode. The catch there is that if there is no Image_Path record, you won't see a Surg_Path record. One solution is to generate one blank Image_Path record for every Surg_Path record that does not have at least one related Image_Path record.

                   Needless to say, that complicates your solution, but it can be made to work.