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:
- Part (with foreign keys to project)
- QCTests (with foreign key to Part)
- MoldShopJobs (with foreign key to Part)
- 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.