7 Replies Latest reply on May 14, 2014 10:40 AM by evanscl

    Create a Single Report with Lists of Records from Multiple Unrelated Tables

    evanscl

      Title

      Create a Single Report with Lists of Records from Multiple Unrelated Tables

      Post

           How do you create a single report that list records from multiple unrelated tables?

           I want to display records from four unrelated tables: Bird, Veterinary, Human, and Vector. 

           I want to display four lists on the report where [Diagnosis is not equal to "Negative"], and each list is sorted by Diagnosis, County, City, and DateOnset:

             
      1.           Bird: CaseIDN, Diagnosis, CaseStatus, County, City, ZIPcode, Species, DateOnset
      2.      
      3.           Veterinary: CaseIDN, Diagnosis, CaseStatus, County, City, ZIPcode, Breed, DateOnset
      4.      
      5.           Human: CaseIDN, Diagnosis, CaseStatus, ClinicalSyndrome, BloodDonor, County, City, ZIPcode, DateOnset
      6.      
      7.           Vector: CaseIDN, Diagnosis, CaseStatus, County, City, ZIPcode, Species, DateOnset

            

           I also want to display a fifth list from the Human Table, which is sorted by Diagnosis, CaseStatus, and ClinicalSyndrome:

             
      1.           Human: CaseIDN, Diagnosis, CaseStatus, ClinicalSyndrome, BloodDonor, ImportedFrom, CountryOfOrigin, County, City, ZIPcode, DateOnset

            

      ---Begin Report---

            
      Arboviral Activity in Birds, Animals, People, and Mosquitoes
      (Listed by Type, Diagnosis, County, City, and DateOnset)
                                                                                                                                                                             
                
                Animals
                          CaseIDN                     Diagnosis                     CaseStatus                     County                     City                     ZIPcode                     Breed                     DateOnset
                                                                                                                                                                                                                                                                                                                                   
                
                Birds
                          CaseIDN                     Diagnosis                     CaseStatus                     County                     City                     ZIPcode                     Species                     DateOnset
                                                                                                                                                                                     
                                                                                                                                                                                                                                                                                                                                                                   
                People
                          CaseIDN                     Diagnosis                     CaseStatus                     ClinicalSyndrome                     BloodDonor                     County                     City                     ZIPcode                     DateOnset
                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                                                                                   
                Mosquitoes
                          CaseIDN                     Diagnosis                     CaseStatus                     County                     City                     ZIPcode                     Species                     DateOnset
                                                                                                                                                                                     
            
      Arboviral Activity in People
      (Listed by Type, Diagnosis, Case Status, and Clinical Syndrome)
                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                         
                          CaseIDN                     Diagnosis                     CaseStatus                     ClinicalSyndrome                     BloodDonor                     ImportedFrom                     CountryOfOrigin                     County                     ZIPcode                     DateOfOnset                      
                                                                                                                                                                                                                                                       

      --End Report--




            

        • 1. Re: Create a Single Report with Lists of Records from Multiple Unrelated Tables
          philmodjunk

               If you want to combine data from different unrelated tables in a single report layout, then they shouldn't be unrelated. You are using a relational database, after all and it is designed to use relationships between tables to get the job done.

               One possible solution is to set up 5 report layouts, one for each of the tables with an extra layout for people. You can use a script to print from each layout in turn or use a script with Save As PDF to generate a PDF where each layout's pages are appended to the end of the same PDF file.

               Another possibility is to add a report table that links in a relationship to each of these tables--including two different table occurrences of your People table. The relationship can exclude records where Diagnosis is "negative" and can also specify other criteria--such as a date or range of dates if such is needed to get just the records you need for your report.

               On such a layout, a portal can be placed to show the related records. These portals can be sized to be many rows tall, but set to "slide up" and to "Resize enclosing part" to adjust the results to better fit the actual number of related records when you print, preview or Save As PDF from this layout.

               And I would suggest that you consider using one table for Birds and Veterinary data, with an added field to distinguish between Birds and non-bird records as these two tables appear to use exactly the same fields.

          • 2. Re: Create a Single Report with Lists of Records from Multiple Unrelated Tables
            evanscl

                 The data are much more complicated than what I present above. The data are unrelated because they can't be related. A person with an arboviral disease case is much different than a bird case, for example, and one requires much more detail than the other.

                 Given that I can't relate these tables, is there another solution besides a multi-paged PDF report that may show only one record on each page (that is, until seasonal activity begins here soon)?

                 I saw the idea of a "dashboard layout", which would be ideal. However, I can't find a good tutorial to even know if a dashboard would work for me.

            • 3. Re: Create a Single Report with Lists of Records from Multiple Unrelated Tables
              TKnTexas

                   You can relate tables by adding a common field that has the only purpose of linking.  It was something I learned in pre-FP7 days.  I had a field called LinkField on each table, that was otherwise not linked.  The field was Text with AUTO-ENTER Data "1".  Click on PROHIBIT MODIFY.  Then connect the tables in the RELATIONSHIPS.

              • 4. Re: Create a Single Report with Lists of Records from Multiple Unrelated Tables
                evanscl

                     I created a new table called ReportTable with the fields that I need for the report (e.g., CaseIDN, Diagnosis, Case Status, etc). For the ReportTable and for each of the four tables (Bird, Human, Vector, and Veterinary) that I wanted to relate to it, I created a field called LinkField, with the auto-calculation of "1".

                     How do I assign the fields in the ReportTable with the corresponding values in the four tables? For example, how do I assign the Diagnosis field in the ReportTable with the Diagnosis field in the Bird, Human, Vector, and Veterinary tables?

                • 5. Re: Create a Single Report with Lists of Records from Multiple Unrelated Tables
                  evanscl

                       So far, I figured out how to assign the fields in the ReportTable with those in the Bird table.

                         
                  1.           In the ReportTable, I set all of the fields to auto-enter as the number one (1)           
                                     
                    1.                     For example, CaseIDN = 1
                    2.           
                         
                  2.      
                  3.           In the Relationships, I set the fields as in the following example:           
                                     
                    1.                     ReportTable::CaseIDN x Bird_TO1::StateUID. That is, if Bird_TO1 StateUID = "AV001-2014", then (1 X "AV001-2014") = "AV001-2014".
                    2.           
                         

                       So, I've at least gotten all of the values to list as I want them. How do I set up the relationship to exclude records where Diagnosis = "Negative"?

                  • 6. Re: Create a Single Report with Lists of Records from Multiple Unrelated Tables
                    philmodjunk

                         When one links tables in relationships, generally they are very different tables. That's why we added different tables in the first place.

                         But the fact that you want to list this data in the same report suggests that there is some data common to all that determines which records from each table are to be included in this report. Perhaps you need all records for each table that fall into a specific date range?

                         Given that you are using a value of 1 in your match fields, I don't see how this will work to show data from multiple records in multiple tables. Is your plan to use a portal to display the records, for example, from your Bird table?

                         If so, a portal filter expression could be added to exclude the related records where the diagnosis is "negative".

                         And don't forget the alternative:

                         Use a different layout for each table to produce a combined report. In that case, you can perform a find on each layout to omit the "negative" diaognises or any other records that should not be part of the report. (and you can still number the pages correctly as those this report were from a single table and single print job.)

                    • 7. Re: Create a Single Report with Lists of Records from Multiple Unrelated Tables
                      evanscl

                           From your earlier advice in this discussion thread, I am using different portals on the report layout. Adding a portal filter expression is a good idea to exclude the related records where the diagnosis is "negative".

                           The following is a more detailed explanation of my report needs and why I am having a difficult time in relating four different tables. I hope you can provide some more insight on how you would relate the tables.

                           I perform mosquito-borne disease surveillance. I monitor for mosquito-borne disease in birds, people, veterinary animals such as horses and donkeys (equids), and mosquitoes.

                           The purpose of the report is to list all positive cases of mosquito-borne viruses that occur during the calendar year in each of the four surveillance categories previously listed. Mosquito-borne viruses can be West Nile virus, eastern equine encephalitis virus, St. Louis encephalitis virus, dengue virus, chikungunya virus, etc.

                           I can understand the relationship of a HumanPatient table to a HumanTesting table. A patient can have one or more sample types taken on different dates that are tested for several viruses by one of many test types. So, it is necessary to relate the patient to his/her own testing. However, I don't know how to relate human, bird, mosquito, or equid testing as they share nothing in common other than the fact that they are all being tested for mosquito-borne viruses. In this sense, they will have in common such things as a diagnosis, case status, clinical syndrome, location, onset date, or species/breed (the technical terms for these vary with each surveillance type). These common fields are what I want to show on a report.

                           So, given this information, how would you relate the four different surveillance types so that they could be reported on the same report?