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.

 

Gary

Outcomes