AnsweredAssumed Answered

Multi level relationships

Question asked by garyshell on Feb 2, 2012
Latest reply on Feb 3, 2012 by beverly

I am new to FileMaker but not to databases. I have been developing in Access, Oracle, SQL etc for MANY years.


I have a simple three level relationship to represent.


  1. Client
    1. Project
      1. Building


In the Client table I have a ClientID field. In the Project table I have a ProjectID field and a foreign key for ClientID. In the Building table I have a BuildingID field and a foreign key for ProjectID. I have established the relationship between Client and Project and also between Project and Building.


I was able to successfully create a report layout that would show Clients and Projects within each client and another report showing Projects and Buildings within each Project. I used the SubSummary part for these. But try as I might, I cant seem to be able to create a Client/Project/Building report with multiple SubSumary parts. It appears that the implied relationship between Building and Client records is not being understood by the report generator.


It was suggested that I de-normalize my data by including the ClientID in each Building record, but that seems like a kludge. Plus I actually have two more levels of info below Building, and the thought of de-normalizing the data all the way down those next two levels just seems wrong to me. I realize that many things are done differently in Filemaker, but...


Thanks in advance for any light you might be able to shed on this.