6 Replies Latest reply on Mar 13, 2014 9:59 AM by philmodjunk

    Newbee Question - creating tiered relationships



      Newbee Question - creating tiered relationships



           Im new to this database thing, and Im trying to create a simple three tiered database that is basically like this:




           Where one parent may have several "childs", who each have several toys. The DB must be able to have multiple parents with the lower trees falling into place for each one. I have watched several tutorials on how to use ID fields and relationships, but I cant seem to get it so that if I pick Parent A, then select, child A, I see all of Child A's toys. I then need to be able to select child B while still under Parent A, and see Child B's toys.

           I am trying to create this so I can use it for an inspection company where we have Projects with multiple buildings, which each have deficiencies we need to track per building, within the project. I have made a rudamentary flowchart to show the basic layout.

           Please provide me with an link to an example of what I am looking for or clear instructions on how to do this. Everything I find seems complex and gets me part way there, but doesn't seem to group everything the way I need it, and it seems like it should be simple!

           Please help!


        • 1. Re: Newbee Question - creating tiered relationships

               While not meaning to be snooty or snarky (given your post has received no replies in two hours)  I must say that what you're asking is clearly spelled out in the FileMaker documentation. Check out Primary and Foreign Keys, or match fields in FileMaker parlance. As well, check out Enable Creation of New Records in the Relationshp Graph.

          • 2. Re: Newbee Question - creating tiered relationships

                 Personally, I dropped out of working in the forum this afternoon as it was logging me out and killing my comments every few minutes. Things have stabilized now or at least it appears to.

                 What you are describing is this:


                 Parent::__pkParentID = Child::_fkParentID
                 Child::__pkChildID = Toys::_fkChildID

                 But that's just the relationships. To be able to select a child and then see the child is also an interface design issue. One method is to use Master Detail portals. On the Parent layout, you put a portal to Child. Clicking a button in the portal row performs a script that brings up all Toys records in a second portal for the child record you clicked in the first portal. See this link for the implementation details: Need layout solution for nested portals...

                 Another option would be to take that same button in the portal to child and set it to take the user to a different layout that lists only the Toys records for that child. And that new layout can be opened in a different window if you wish.

            • 3. Re: Newbee Question - creating tiered relationships

                   Thanks for your input, but I guess I'm more stunned than I thought!

                   I have tried doing all of these suggestions, and others, but it always seems like there is one step or another that is left out of one example or the other that would make it work exactly.

                   Is there any chance someone could whip off a very quick three level file that shows me what the set up should look like? Idealy it would have a main layout with one portal for child and one portal for toys, then choose the child, and it show that childs toys.

                   I can get as far as the child changing from parent to parent, and the parent having multiple children, but when I add the third "toy" element, I get all confused and it doesnt work out. I cant get to the point where I choose the child and it shows the toys for only that child.

                   (I feel pretty, pretty, pretty stupid, this should be easy!)

              • 4. Re: Newbee Question - creating tiered relationships

                     But there are multiple options of how to work with that. And your abstract example may or may not accurately apply to a real world application due details in the real world project that may differ from the examples.

                     Can you spell out in more detail exactly what you are trying to do? I especially would like to know what you expect to see on your layout when you work with the records in these three tables.

                     If you are working with an actual project to do this instead of this abstract example, I recommend that you start over and describe your problem in terms of your actual project.

                • 5. Re: Newbee Question - creating tiered relationships

                       I have an inspection company and i need to track projects, which may or may not have several buildings. For each building within the project i will have several issues that need to be tracked and related to each building within its project.

                       i created three tables respectively named, and linked project ID to Project IDFK in the building table, then linked building ID to Building idFK in the issues table. When i try to set up my layout, i have the main layout based on project with fields for project id and project name, then i add a portal based on buildings and add building id and building name fields, this works and i can cycle through the projects looking at a list of buildings in the portal. When i add a portal for issues and add the issue id and issue name fields, they dont populate and i dont know how to make it so that i can select a building in the building portal and have only that buildings issue records show up. I would then like to be able to click on the issue and bring up a more descriptive window that allow editing and additional issues to be added. 

                       I also need each issue to be number, starting at 1, under the building name. So not like a serial number that runs the length of the DB, but only runs the length of the amount of issues per building. Then its reset for the next building, and so on.

                       im also confused about whether or not i should have both sides flowing (create and delete) between each relationship.

                       i did try a version where i copied the issues table and linked one version os issues to the other, but that didnt get me where i want to go either.

                       is that descriptive enough?

                  • 6. Re: Newbee Question - creating tiered relationships

                              I have an inspection company and i need to track projects, which may or may not have several buildings.

                         A quick question: Is it possible that, over time, a given building might need to be linked to more than one project? If so, you have an additional complication to your database design in that your relationship between Projects and Building is actually many to many instead of one to many.

                         For strictly one to many, what you describe sounds correct for simply showing the relationships to your data:


                         Projects::__pkProjectID = Buildings::_fkProjectID
                         Buildings::__pkBuildingID = Issues::_fkBuildingID

                         But they aren't the relationships to use for the "Master - Detail" method for working with a portal to Issues on a Projects layout.

                         You'd need:

                         Projects----< Issues|SelectedBuilding

                         Projects::SelectedBuildingID = Issues|SelectedBuilding::_fkBuildingID

                         You'd place a portal to Issues|SelectedBuilding on your layout, not a portal to Issues to show the issues for a selected building.

                         A button in the buildings portal row would perform this simple script:

                         Set Field [Projects::SelectedBuildingID ; Buildings::__pkBuildingID ]

                         I don't think you need to, but a Refresh Window step can be added to that script if you need it to get the portal to update after clicking the button in the Buildings Portal.

                         Please note that Issues|SelectedBuilding is a new Tutorial: What are Table Occurrences? of Issues created in manage | Database | Relationships. It is not a new table created in Manage | Database | Tables.

                         For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained