AnsweredAssumed Answered

Report combining different tables

Question asked by pandemic on Nov 19, 2014
Latest reply on Nov 19, 2014 by philmodjunk


Report combining different tables


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.