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.
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.