1 2 Previous Next 17 Replies Latest reply on Mar 10, 2016 10:00 AM by MrB

    Display multiple hierarchy levels of active record


      Hi everyone,


      I've been searching on this forum but been unable to find an answer.

      I have a database with now 2 tables. A data table with records of topics to which I add detail info with various fields, and one hierarchy table which contains the parent hierarchy levels of the topics. I've made a self-join of this table.

      I have a layout of the data table with info about the topic, related via the hierarchyID I can see what the name is of the next parent hierarchy level, but I want to see all the parent levels. How can I accomplish this ?

      It is possible to make multiple self joints for each level needed, that does work, but I don't think it is the way to go as I have seen people only using one self join to accomplish this, but never was explained how.

      Just to be clear. In the layout of the data table, I just want to show the hierarchy levels connected to that topic on a line like: level 1 > level 2 > level 3 > current topic.

      Like breadcrumbs on a website actually.

      I hope someone could help me on the way, I guess I would need some global calculation fields ??

        • 1. Re: Display multiple hierarchy levels of active record

          Hard to say without seeing how the data is in the hierarchy table. If you had a table for each level with related records in the next level that would be easy to display with the relationship.


          If the hierarchy table has a field for each level you could use a calculation field that outputs the desired text that you want to see.


          hierarchy::level1 & " > " & hierarchy::level2 & " > " & hierarchy::level3


          You might want some sort of Case function usage to account for not displaying the arrows if the topic only has two levels.


          ExecuteSQL is another way to get it done in the calc field.

          • 2. Re: Display multiple hierarchy levels of active record

            As another option you could use a one row portal that display the level fields and have arrows that hide based on the next field being empty. The problem would be that the arrows are in a fixed position compared to the end of the text. A calculated field might give you the best results visually.

            • 3. Re: Display multiple hierarchy levels of active record

              Sorry I wasn't clear. The hierarchy table has the following fields:


              Id - parentid - name - rank.


              The self join is between parentid and id.   The name is what I want to display. The rank shows to which level in the hierarchy it belongs.

              The id field is related to the hierarchyid field in the data table.


              I hope it's now clear how it's setup. If not I can post a screenshot later, don't have access to my pc at the moment.

              • 4. Re: Display multiple hierarchy levels of active record

                This seems a little odd. Do you have one table with a self join or two separate tables?


                Aside from that, the way you have the data setup in the hierarchy table lends itself to ExecuteSQL.


                DoubleCheck syntax:

                ExecuteSQL ( "SELECT name FROM hierarchy WHERE parentid = ? AND rank =1"; ""; ""; data::ID)


                This would be the base for pulling the level name. Change to rank=2 or 3 depending on the level you want. Using this in a calc will give you the cleanest looking text.



                You could also use a number of single row filtered portals where the filter is set to rank = 1, rank =2 and so on.


                There are better ways to set this up with relationships that will make life easier in many ways. I recommend you consider that.

                • 5. Re: Display multiple hierarchy levels of active record

                  I am using 1 table with 1 self join table occurance.


                  I Could try the SQL you suggested, although I have no clue about SQL.

                  But if you say there are better ways to set this up, I better do that. Could you advise me what would be better to do in this case ?

                  I appreciate your help.

                  • 6. Re: Display multiple hierarchy levels of active record

                    Have a table for each level of your hierarchy. The next level having related records to the parent table one level higher. Might not be difficult to move you flat data into related tables, but that would need to be done.


                    Makes the calculations easier and will make other things easier as well. I am guessing you hierarchy list stays the same most if the time for all topics.

                    • 7. Re: Display multiple hierarchy levels of active record

                      bigtom, I used your method in the past, which is NOT flexible. You are forced to create a new table every time there is a new "level".


                      There are solutions that can display hierarchical data in FMP. Just searching 'hierarchical display filemaker' yields these possibilities:





                      http://www.fmfunctions.com/fid/353 (a custom function!)


                      ... and more



                      • 8. Re: Display multiple hierarchy levels of active record

                        Thanks. I know there are ways. I got stuck in the idea that there were only ever 3 levels in this solution. Also tired.

                        • 9. Re: Display multiple hierarchy levels of active record

                          bigtom; thanks, that sounds actually like a simple and workable solution. I did think about it but read somewhere it's not the preferred method, that's why I chose to put everything in one table.


                          beverly; Thanks for the input.

                          I have indeed found a few of the solutions you linked to. First of all they seem complex, at least for me...I'm just a beginner. Also because I don't really need a infinite hierarchy with those dropdown arrows to unfold all levels.


                          I will indeed have more than 3 levels (it was just an example) but in my database I will only need a maximum of 10 levels, if I would even need to add a level (because I forgot something) it's maximum 1.

                          So, I do not need the flexibility to add new levels. I just want to be able to display somewhere on the layout the parent levels of the current record and in another layout be able to perform a find and display all child records (only one level down) of a certain level.


                          So, what would be the best approach in my case ? If I think of it, it shouldn't be to much trouble to make a layout for the entry of hierarchical data, or do I miss something and will one method be a lot easier for this ?

                          • 10. Re: Display multiple hierarchy levels of active record

                            I'd just like to know if one method would be a lot better to use that the other. If it doesn't really matter I can choose what I like best, I just don't want to make a wrong decision due to my not so strong knowledge of Filemaker and then end up with a problem and need to rebuild everything over again.

                            I would highly appreciate the advice.

                            • 11. Re: Display multiple hierarchy levels of active record

                              The answer really depends on how you want to work with the data.


                              Important question: How are you connecting the topic records with the topic levels in the first place?


                              It seems like your data table has a hierarchyID_fk that relates it to a specific row in the levels table. I am also assuming the levels table has a number of columns/fields like this:


                              ID, level1, level2, level3, level4....


                              Where you really have something that looks like a table for a conditional value list. If it is related by the ID I don't see why you cannot see any certain level for that row. This should be easy.


                              From what you have described so far about a breadcrumb like display this is the way I would do it. This technically stores a little more data than a bunch of related tables, but text is cheap these days and it is easier to scale and work with.


                              There is no need for a self join. Just one relationship from data to levels.


                              Then just drop the fields on the layout.

                              • 12. Re: Display multiple hierarchy levels of active record

                                Thanks for the reply.


                                Ok, right now I have as you said the topic records table connected to the topic levels table with a hierarchyID.

                                The levels table containing the hierarchy has 4 columns: ID, parentID, name, rank

                                I made a self-join parentID > ID

                                The rank field contains the level number (up to maximum 10 levels).

                                Via the self join I can see only one level up at the moment.


                                I know I could have just put everything in a table with a row for each level, so every record will display the whole hierarchy of the record. In that case I don't even have to make a separate hierarchy table, I can also just add this to the main table.

                                The thing is I would have a lot of double values.

                                Let's say level 1 is the highest level, that would mean if there are 5000 records with the same name for level 1, this will be copied 5000 times in the level1 column.

                                This didn't seem to logic to me for performance and I thought is anyway sort of a no go for a database.


                                So, with my basic knowledge I choose to go they way I set it up know, discovering I couldn't see the complete hierarchy. That's why with this topic I'm trying to figure out what the best way is to handle this.

                                I'm either on the right way, it's actually the best approach for my application, but just missing the knowhow to get it to work.

                                Or I made the wrong decision and it will be better to put everything in one table after all, or in multiple tables, one for each level, so I at least need only one record for the level one name and not 5000.


                                As I know understand from your reply is it doesn't actually matter so much for performance/storage to just put everything in one table (or maybe create a table for each level and relate those). Am I right ? Meaning I'm actually worrying too much about nothing ;-)

                                Of course it also depends on the size of the database. As said it's not going to be handling more than 10 hierarchy levels.

                                • 13. Re: Display multiple hierarchy levels of active record

                                  What you have setup is something that lends itself to SQL. You can use the ExecuteSQL example, but you will have a query looking up all the records and that will take time. Each way has its trade-offs. This seems to be the only way as I am not sure how you are connecting all the levels to the topic record in the first place.


                                  The important question remains: How do you know which levels belong to which topics? That is something I am not understanding unless you set them manually from a big list. If you know what level4 is how do you know which level3 is associated to that topic?


                                  The size of the data is not that bad. It sounds bad, but not so much an issue on LAN. If you average 10 characters in an entry you have 50kB as an increased instead of 0.01kB. Granted it is 5000 times more data but 50kB is still a small number.

                                  • 14. Re: Display multiple hierarchy levels of active record

                                    I guess I'm not really clear and we have some misunderstanding.

                                    First, ok...I understand I would have to use SQL, but I have no clue about this, so if there's a good alternative it's probably better to not use it. Let's first explain with a simple example to clear things up (i hope;-))


                                    Lets say I have a topic record with all kind of information about a certain street.

                                    Now for this street I've build up a hierarchy: street (level6) > neighborhood (l.5)> city (l.4) > country (l.3) > continent (l.2) > planet (level1)

                                    How I've build it up now, the topic record with street info will have a hierarchyID which has a relationship with the ID field in the hierarchy table. As said the hierarchy table contains ID, parentID, name, rank

                                    Let's take a record with info of Mullholland Drive, the main table with street information has the same hierarchyID of the Mullholland Drive ID value within the hierarchy table. Now this information is connected.

                                    Mullholland Drive has a parentID corresponding to the ID with the name Hollywood Hills (which has rank 5), which has the parentID of Los Angeles > USA > North America > Earth.


                                    In the layout with street info (based on the main table) I can see in a related field (from the hierarchy table) the street name to which the info belongs and because of the self-join I can see the name of the neighborhood, that's it. While I want to see the remaining hierarchy.


                                    I hope I've made it more clear how I set it up and what I want. Thanks for your time.

                                    1 2 Previous Next