6 Replies Latest reply on Apr 25, 2016 1:11 AM by FrankvanderMost

    How to handle nested hierarchical structures


      Hi all,


      I've been using FileMaker since before it was called FileMakerPro, which was before it was called FileMaker again.  That's a lot of years, but I still consider myself only an experienced user, not a guru.  I'm hoping someone can help me out.


      What is the easiest way to visually display and interact with a hierarchical outline of nested elements where each element is stored in a separate file for relational linkage to other similar hierarchical outlines?  For example:


      Subject 1

           Topic 1

                Idea 1

                     Data Point 1

                     Data Point 2

                Idea 2

                     Data Point 3

                     Data Point 4

                Idea 3

                     Data Point 2  (same as in Idea 1)

                     Data Point 4 (same as in Idea 2)

           Topic 2

                Idea 4

                     Data Point 5

                     Data Point 6

               Idea 1 (same as in Topic 1)

                     Data Point 1

                     Data Point 2

                Idea 2 (same as in Topic 1)

                     Data Point 3

                     Data Point 4


      What I want is the ability for the user to see all this data, formatted similar to above, with the ability to modify the structure.  For example, breaking the link between Topic 2 and Idea 4 and replacing it with a link to Idea 5.  In total my database has hundreds of thousands of stored bits of information, so the inability to view things from a higher level in context really stinks.


      I've had a bad time trying to get this to work with Portals, though Portals works excellently at the lower levels.  The problem is Portals do not show nested relationships this complex.  For example, I can easily show Subject 1 contains Topic 1 and Topic 2, but not Ideas are contained, nor what Data Points are contained within each of those.  That produces very inefficient information gathering because the user has to select a Topic and then view its contents out of context with the whole.


      Any thoughts on how I can do this within FileMaker?





        • 1. Re: How to handle nested hierarchical structures

          I don't know if this will help, but you might check this thread:


          • 2. Re: How to handle nested hierarchical structures

            If all the segments are related in a chain: Subject --> Topic --> Idea --> DataPoint you should be able to view data from all along that chain within the same portal on a Subject-based layout.

            • 3. Re: How to handle nested hierarchical structures

              I think we should see a post of the RG (relationship graph) before proceeding with more assistance.


              • 4. Re: How to handle nested hierarchical structures

                Hi Steve,


                please note that your example logically speaking does not show a hierarchical structure but a network structure since Ideas 1 and 2 have two parents and most of the data points also have multiple parents. The solution of Soliant may be helpful here because it works with shadow records, so it would be relatively easy to produce multiple shadows.

                If you are going for a different solution which depends on related records and one item should indeed be the very same (rather than a copy) as its other occurrences in the hierarchical tree, then  you may run into problems when you implement the hierarchy through simple one-to-multiple relations (i.e. children records merely containing their parent's record id). You'll need to go through multiple-to-multiple relations.


                Well, while I am writing and thinking about this. I don't know how experienced you are, but this is what I would attempt.


                It should be doable with one table (A) that contains all the items (subject, topics, ideas, data points, and more if you want. You distinguish them by some type-field) and one table (B) that contains the relations. The relations table will have to contain a binary field that indicates whether the item is collapsed or is showing its children.

                The harder part is the following: To make a portal that shows the list in a certain state you would have to write a recursive function (You'll need FMP Advanced, or use a solution involving a script) that generates a list of records of table B using the ExecuteSQL function. Right now, I'm not sure how that would look like exactly, in principle you would first be looking for all level 1 items, then for each item check its collapse state and if it is on 'expand' then insert level 2 (there is your recursion) before you go to the next item in level 1, etc.

                Once you have the function, you put it in a calculation field and make a relationship to table B. That relationship will be the base for the portal.

                If you get it to work it will be much neater than the shadow records solution which was developed before the ExecuteSQL function was introduced if I remember correctly.

                Actually, I'm quite excited about this solution, but right now, I don't have the time to program it. If you're interested, remind me in a week or two.



                • 5. Re: How to handle nested hierarchical structures

                  Sorry for the delay.  Other things pulling me in different directions this week.


                  First, thanks to Beverly for pointing to some resources to check out.  Nothing jumped out as directly applicable, but I'll recheck.




                  Thanks for the feedback and suggestions. Yes, networked is what we've got.  I'm obviously not up on the database lingo   FM is simply a tool for us, not a direct part of what we do.


                  First, I am not using SQL in any way-shape-or form, therefore I'm not sure that shadow tables are applicable.  I've not used one before and my quick check as to what they are seems to indicate it is SQL specific.  Therefore I'm not sure your suggestions are directly applicable.  I also don't have FM Advanced.  I have Pro 14, though I only started using it after a long overdue upgrade from 10.


                  What I was hoping for when posting here is some sort of new way in FM 14 to view portal data within portal data within portal data within... you get the idea!  It seems that this is still not easy to do with FM and therefore I expect I'm out of luck.


                  My current "solution" is to have the data output to text and then view it as one views a roadmap (for you youngsters, it's silar to GPS, except it doesn't speak ).  It's not ideal, but it is better than what we currently have.


                  For the intrepid viewers, here's a bit more background on our problem.  I don't think there's much point in reading it, but since people have taken time to read and respond I think it's right that I not drop this and move on too quickly.


                  In any event, thanks very much for the thought put into the problem.






                  All data is unique, not copies.  That is the inherent challenge because if the values themselves were stored where they were used I do think this would be a fairly easy nut to crack.


                  The database consists of 13 individual files with 3 or more tables per file.  The files are organized as a reflection of the intended end use, which are exported files that are manipulated by AppleScript to be compiled in C++.  Most files contain two primary tables; one for storing the "parent" info and a second one to store references to the "child" data stored in separate files.  For example, the child table in File 5 references the parent table in File 6.  The child table in File 6 references the parent in File 5.  Etc.


                  The shortcoming is that there is no way to get the "big picture" for the higher level structures.  Using my simplified example above, when I select a Subject I currently can only see its Topics (1 level lower), nothing deeper that.  I can select a Topic and see the Ideas (1 level further down), but nothing deeper than that and nothing higher either.  So on and so on.  What I really want to see is about 5-6 levels down all at one time.  I don't care about expanding/collapsing too much as the primary point is to see everything at once.


                  I did experiment with a "viewer" which assembled IDs on the fly, set file specific handles so they pointed to the right place, and then attempted to display them in a way that was useful to me.  This failed for a number of reasons.  One of which is that the associations are not straight forward or as neat as my example up top.  In the real database levels 5 and 6 can be attached to levels 1, 2, 3, or 4 in any combination in any order.  So trying to have something assembled on-the-fly based on a current selection quickly became a nightmare and I abandoned it.

                  • 6. Re: How to handle nested hierarchical structures

                    Hej Steve,


                    thank you for elaborating on the problem. I'm afraid you're right about your wish for a portal-in-portal possibility. I worked with it in 2004 in  MS Access which could do this. It would have worked here, I  but I'm not sure it is such  a panacea.


                    Considering your setup, I think you were on the right way with the viewer experiment. I'm not sure what you mean with 'assembled IDs on the fly', but if it means importing ID's into a viewer table, then that goes in the direction of table B in my previous post. In such a table B, you actually separate the levels of the tree viewer from the item-type, so it should be possible to deal with the not-so-neat attachments that you have in your setup. It should even be possible to hook Subject 1 as a child of Topic 1, rather than the other way around (although I wouldn't recommend it).


                    It's up to you of course to continue with this or not. Thank you in any case for bringing up your question because for me it was a good way to revisit the problem. I left it unsolved some years before, but this time I think I nailed it - at least in principle (I'm not sure about how the portal will actually behave and hence how the user experience will be). Hopefully, I'll be able to come back and present a demo.