12 Replies Latest reply on Feb 3, 2012 3:04 PM by beverly

    Multi level relationships

    garyshell

      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

        • 1. Re: Multi level relationships
          beverly

          Gary, do the report from the building table/perspective. Have a summary for Project and another for Client. then sort the report by both relationships (fields).

           

          In list  view, the fields for building go in the BODY. The related fields go in the respective SUBSUMMARY parts.

           

          If you have something to "total" those fields can be created in Buildings and placed in the summary parts.

           

          How you sort can produce different report from the same layout!

           

          Try it out and if you need more assistance, let us know.

           

          Remember *from* buildings, but use related fields in the report, too.

           

          -- sent from my iPhone4 --

          Beverly Voth

          --

          • 2. Re: Multi level relationships
            garyshell

            Beverly,

             

            That is precisely what I did.  I used the lowest level table, Buildings, as the basis for the report, added a subsummary section for Projects with ProjectName as a field and it works fine.  I sorted on ProjectName then BuildingName. 

             

             

            When I try to add the parent of Projects, namely Clients, as a subsummary with ClientName as a field above the Projects one, it does not work.  I added the sort for ClientName above ProjectName and yet the report doesn't function.

             

            In discussions with a contact who has extensive FileMaker experience, his opinion was that because the Building table had no explicit foreign key for Client my report won't work.  It seems hard for me to fathom that the implied foreign key derived from the relationship of the three tables would not be utilized by the report engine.  I just can't imagine the need to de-normalize the data to that extent, but then again as I said in my first post I am very new to FileMaker.

             

            Thanks for you help thus far.

             

            Gary

            • 3. Re: Multi level relationships
              beverly

              Gary, I guess a pic is worth 1K

              Define tables and fields and related them thusly:

              related_summary_graph.jpg

              Create the layout (use the report wizard if you want):

              related_summary_layout.jpg

              The summary parts are sort-by and this is also in the script:

              related_summary_script.jpg

               

              Oh, what the hay... I'll give you this sample. Run the script and see the results!

              1 of 1 people found this helpful
              • 4. Re: Multi level relationships
                Stephen Huston

                Hi Gary,

                 

                You do not need a direct fKey link between the first and third tables.

                 

                However, for clarification, are your records structured so that one Client has one or more Projects and one Project has one or more Buildings? Or can one building record be linked to more than one Project? (That second kind of linking can confound multi-level reports.)

                 

                As long as each level has its own child records, not shared with another record at the same parent level, the report should work IF...

                • all the Key fields for each relationship are indexed and match for data type (text, number)
                • the sorting is done in the correct order, usually level 1, then, 2, then 3 if reporting from 3
                • all fields in the subsum parts are positioned well within the height of the subsum part. Being 1 pixel to high, or sometimes even exactly ON the top pixel can make it fail to appear.

                If its still not working, maybe you can post your file for someone to troubleshoot directly.

                 

                Stephen Huston

                • 5. Re: Multi level relationships
                  garyshell

                  Thank you SO much for taking the time to build this sample. 

                   

                  It looks exactly like what I have, with ONE exception.  One of the things I left out in my initial explanation of the issue (mea culpa, yes I should NEVER leave out details) is that in addition to the ClientID being used to link to the Project table to the Client Table and ProjectID being used to link Project to Building tables.  I had also included a ClientIDTimeStamp and ProjectIDTimeStamp field in each of the affected tables and as part of the relationships.

                   

                  These timestamp fields are necessary because this app will be running on multiple stand alone portable machines without a persistent network connection. These latter need to be synched to a single common database. Knowing that new records created on such machines would create synch conflicts because of the autonumber field being used as the primary key,  I added the timestamp fields to create a unique compound key.

                   

                  After looking at your test database, I decided to temporarily remove the timestamp fields from the relationships.  Sure enough as soon as I did, then the report worked just fine!  WHEW! 

                   

                  Now that brings me to my NEXT question, namely how to make this work with my necessary compound key of ClientID and ClientIDTimeStamp?  I did not include the timestamps in my initial report definition, since at the moment the database is on one machine and I know that the ClientID is unique at the moment.  But since the time stamp field WAS in the relationship diagram, I suspect I may have confused the report generator. I am going to go back and add the timestamps back into the relationship diagram and add them to the report definition and sort fields and see what happens.

                   

                  I will report back in case someone else stumbles upon this thread and needs the same info.

                   

                  Thanks again,

                  Gary

                  • 6. Re: Multi level relationships
                    garyshell

                    Thanks Stephen.

                     

                    Yes the relationships are one to many, not many to many.  And as you can see in my reply to Beverly, I think I am on the right track now. My compound key is obviously the root cause of the issue.  And I am going to try to rememdy the issue by including the entire compund key in the report.

                     

                    Gary

                    • 7. Re: Multi level relationships
                      garyshell

                      Hmmm, while it was easy to add the timestamp fields back into the relationships, I don't see how to include them in the report definition in any way that makes the reprot do what I want.  Again, it is obvious that since the realtionship diagram shows them, that the report generator is having an issue. Just not sure how to resolve that.

                       

                      Gary

                      • 8. Re: Multi level relationships
                        beverly

                        Sorry, compound keys should not be a problem. I'm not sorting by the key, I'm not using the key(s) in the report at all. The relationship is used to pull the related field(s), but that's all. I've revised to include TS fields:

                        related_summary2_graph.jpg

                        Also attaching this file (as revised)...

                         

                        Beverly

                        • 9. Re: Multi level relationships
                          2ninerniner2

                          Howdy Gary,

                           

                          The following is a link to a Custom Fucntion that generates a Universally Unique ID and would be one way to get where you want to go

                           

                          http://www.briandunning.com/cf/1246

                           

                          Note that you will require FMP Advanced in order to create this custome function. Once created, it is just a matter of setting your PK to an auto-enter calculation, specifying this custom function as the caluculation.

                           

                          I have used this in a situation where too, there are multiple, disconnected copies of the same solution that from time to time have to be imported into "the mothership" for consolidation

                           

                          Cheers!
                          Lyle

                          1 of 1 people found this helpful
                          • 10. Re: Multi level relationships
                            garyshell

                            Hmm,  that bodes well for what I need to do. I was able to get my sample working without the timestamps and even successfully implemented the report with the next two levels down in the heirarchy. I'll go back and re-add my timestamp fields to the relationships and see if I can get it going with those.

                             

                            Thanks so much for your help.  I am quite impressed with this forum so far!

                             

                            Gary

                            • 11. Re: Multi level relationships
                              garyshell

                              GUID's!  Hurrah! When I added the timestamp fields it was not what I really wanted. I had hoped there was a way to make the autonumber fields use a GUID like I can in Access, but didn't find it so I settled on the quick and dirty way by using timestamps.  But I think I'd MUCH prefer your solution. I'll delve into retrofitting it into my app.

                               

                              Thanks,

                              Gary

                              • 12. Re: Multi level relationships
                                beverly

                                Absolutely! search for UUID in this forum (and elsewhere). You'll get lot's of ideas (and arguements). I like Ray C's example..http://www.nightwing.com.au/FileMaker/demos9/demo910.html "Base36 uID System"

                                 

                                Beverly