12 Replies Latest reply on Jan 22, 2014 12:56 PM by evanscl

    Create a Report from Related Tables

    evanscl

      Title

      Create a Report from Related Tables

      Post

           I have data in three related tables: Parent, Child, and Grandchild. I want to create a report in Table View that shows certain fields from each of the three related tables.

           I want the report to group records based on a field in the Parent Table and subtotal/total records from a field in the Grandchild Record. How do I create such a report?

        • 1. Re: Create a Report from Related Tables
          philmodjunk

               From the names, I am assuming these relationships:

               Parent----<Child----<GrandChild (---< means "one to many" )

               You can Base your table view layout on GrandChild and still include fields from Parent and Child. And there are table view options for the grouping and sub totals. But I'd use a List View layout with sub summary layout parts instead as I get more and better options for how to set up the sub totals and some "group headers" by using a sub summary layout part.

               With either list or table view, a summary field can be used for your sub totals.

          • 2. Re: Create a Report from Related Tables
            evanscl

                 I created a new table and added fields from each of the three related tables (Parent, Child, Grandchild). I related the newly created table to the Grandchild table via a Specimen ID Number. When I exit the layout and view it in Browse Mode, no data appear in the fields.

                 In the screenshot below:

                   
            •           Parent = VectorCollectionSite
            •      
            •           Child = VectorTrapInfo
            •      
            •           Grandchild = VectorTestInfo

                 The newly created table, on which I wish to build a report, is titled, "Vector_lay_TestResults"

                 Is there something wrong with the relationship that is causing the data not to appear in the layout, "Vector_lay_TestResults"?

            • 3. Re: Create a Report from Related Tables
              philmodjunk

                   That is not what I suggested. Don't use a new table. Just select VectorTestInfo in Show Records From for your layout.

              • 4. Re: Create a Report from Related Tables
                evanscl

                     OK. I followed your suggestion, and the data from the related tables are appearing in the fields on the new layout. However, the fields on which the newly created table is based (Show Records From: VectorTestInfo), are not appearing.

                • 5. Re: Create a Report from Related Tables
                  philmodjunk
                       

                            However, the fields on which the newly created table is based (Show Records From: VectorTestInfo), are not appearing.

                       Do you mean "newly created layout"? (Show Records From: VectorTestInfo) refers to a setting for a layout.)

                       You can add any fields from the Layout's underlying table by entering layout mode and dragging from the Field Tool in the status bar to your layout. In FileMaker 13, you can also open a "field picker" to do this. If you are using Table view, you can stay in browse mode and use the Modify button to add them.

                  • 6. Re: Create a Report from Related Tables
                    evanscl

                         "newly created layout" -- I didn't mean to refer to a setting for a layout.

                         I used the field tool to drag the SpecimenIDNumber field from the Vector_lay_TestResults onto the layout. Still, the field is empty in Browse Mode. Could it be because the SpecimenIDNumber is a calculated field? Is something wrong with the relationship you think?

                    • 7. Re: Create a Report from Related Tables
                      evanscl

                           When I click on "New Layout/Report", do I click "Computer"? Then do I click "Table" or "Report"?

                      • 8. Re: Create a Report from Related Tables
                        philmodjunk

                             What you have set up is still not clear.

                             Check Layout setup|Show Records From for your report layout. Does it specify "VectorTestInfo" or some other name?

                             If the layout does specify VectorTestInfo, then, if you add SpecimenIDNumber to the layout from VectorTestInfo and it is blank when viewed in browse mode, this means that the field is empty for the current record on your layout.

                             But note that "From VectorTestInfo" qualifier. When you click this field while in layout mode and check "Display Data From" in the inspector's data tab, you should see this exact text: VectorTestInfo::SpecimenIDNumber. If you see some other name to the left of the ::, then you have added this field from a different Tutorial: What are Table Occurrences? and the relationship between VectorTestInfo and this other table occurrence could keep the field blank due to their being no related record for your current VectorTestInfo record.

                        • 9. Re: Create a Report from Related Tables
                          philmodjunk
                               

                                    When I click on "New Layout/Report", do I click "Computer"? Then do I click "Table" or "Report"?

                               Report could be made to work. But it may be simpler for this specific task to select "Form". This gives you a blank layout and you can add the needed layout parts and fields your self a bit at a time and test them while you do so.

                          • 10. Re: Create a Report from Related Tables
                            evanscl

                                 Everything is working now except for the SpecimenIDNumber. Its value is calculated in the Grandchild Record. Does the calculation prevent it from showing up in the new layout?

                            • 11. Re: Create a Report from Related Tables
                              philmodjunk

                                   It does not.

                                   But I can see from your relationship map that it either has global storage specified or it is an unstored calculation. It should not have global storage specified so I would guess that it is a calculation that refers to either global fields or a field from a related table in the relationship. Problems with your relationships might keep that field from calculating the value you expect it to return.

                              • 12. Re: Create a Report from Related Tables
                                evanscl

                                     Got it to work. Internal problems with a calculation that disappeared.