5 Replies Latest reply on Nov 19, 2014 3:16 PM by philmodjunk

    Report combining different tables

    pandemic

      Title

      Report combining different tables

      Post

      I'm a relative novice to FileMaker and am currently using FileMaker Pro 12 to build a database. I'm trying to build a database that keeps track of projects, along with the various types of tasks involved in the project. I'm currently struggling with creating a report for combining journal logs and sorting them by projects. This is a simplified list of tables I have:

      - Project

      - Part (with foreign keys to project)

      - QCTests (with foreign key to Part)

      - MoldShopJobs (with foreign key to Part)

      - Users

      - Journal (related to QCTests and MoldShopJobs, foreign key to Users)

       

      The Journal table is used for different people to write down the daily tasks they performed for a particular QC Test or Mold Shop Job and how many hours they spent. I want to create a summary report for the combined journal entries, sorted by Project and User. I'm using the anchor buoy approach of relating the tables. This means my relationships look like this:

      Journal -> journal_Users

      Journal -> journal_QCTests -> journal_qctests_Parts -> journal_qctests_parts_Project

      Journal -> journal_MoldShopJobs -> journal_moldshobjobs_Parts -> journal_moldshobjobs_parts_Project

      As a result, I'm having difficulty trying to sort by Project because they are in two different table instances. The focus here is to have a sum of the hours per project per person within a certain time-frame. 

       

      I originally had a different Journal table for the QCTests and MoldShopJobs, since they had a few different fields and it was easier to create reports for each one individually, but I found it a challenge to combine them together when I needed an overall Project report since FileMaker doesn't seem to have anything like the SQL Union command. 

        • 1. Re: Report combining different tables
          philmodjunk

          Conceptually, your relationships are:

          Project------<Part-----QCTests-----<Journal>-----User
                                 |                                     v
                            MoldShopJobs----------------|

          From what I can tell, i don't think that it matter which occurrence of projects that you refer to when sorting your Journal records as each "relationship path" should match to the same Project record for any single entry in Journal.

          • 2. Re: Report combining different tables
            pandemic

            That is the expected and desired outcome. However, the actual outcome when I sort by User, Project, Date is the User subsummary being properly divided up, but only one project showing up per person for whichever table instance wasn't used for the sort. For instance, if I sort by journal_Users, journal_qctests_parts_Project, all the projects related to QCTests will show up under each user, but only the first project will show up under things for MoldShop. For example, sorting by journal_qctests_parts_Project might give:

            Adam (QC)

            - Project A, 10 hours

            - Project B, 5 hours

            Amy (Mold Shop)

            - Project A, 12 hours

            Alex (Mold Shop)

            - Projact A, 10 hours

            whereas sorting by journal_moldshobjobs_parts_Project gives:

            Adam (QC)

            - Project A, 10 hours

            Amy (Mold Shop)

            - Project A, 12 hours

            - Project B, 1 hours

            Alex (Mold Shop)

            - Projact A, 10 hours

            - Project B, 5 hours

            The only way to get all the projects to show up is to have a subsummary for QC journal_qctests_parts_Project and  journal_moldshobjobs_parts_Project as well as to sort by both. However, this will cause the headings to show up multiple times on some projects. For example:

            Adam (QC)

            - Project A, 10 hours

            - Project A, 10 hours

            - Project B, 5 hours

            - Project B, 5 hours

            Amy (Mold Shop)

            - Project A, 12 hours

            - Project A, 12 hours

            - Project B, 1 hours

            Alex (Mold Shop)

            - Projact A, 10 hours

            - Project B, 5 hours

            - Project B, 5 hours

            • 3. Re: Report combining different tables
              philmodjunk

              I would have thought that you wanted to sort by project first rather than by user.

              But I don't actually comprehend what is wrong with your example as you do not identify which rows are for MoldShopJobs and which for QCTests.

              • 4. Re: Report combining different tables
                pandemic

                Thanks for the reply, PhilModJunk. 

                For this application, which isn't a project summary but rather more of a work summary, we have each person record their hours per project.

                I apologize for the lack of clarity of my example. What I was trying to show for the first set of examples was that in one report, the projects under QC showed all projects whereas the projects under Mold Shop only showed one project each. When you change the sort, QC only shows one project whereas Mold Shop shows all projects. For the second/final example, I was trying to show that the items would be repeated. For instance, there are two reports of "Project A, 10 hours" under "Adam (QC)" where there should only be one per project per person. 

                • 5. Re: Report combining different tables
                  philmodjunk

                  I think I now see the problem. You want to sort records by a single project field that sorts all records by the same project table value. I hadn't considered that for one sub set of journal records, the reference to fields in Projects is that of an empty or missing field as a MoldShopJob entry doesn't have a link to Projects via the foreign key for a QCTest record and vice versa.

                  I suggest creating an unstored calculation field such as:

                  If ( IsEmpty ( Project1::ProjectID ) ; Project2::projectID ; Project1::ProjectID ) )

                  as a way to get the needed sort value in a single field for both types of journal records.