4 Replies Latest reply on Mar 6, 2012 8:57 AM by DrewTenenholz

    Recursive calculation

    JulianJohnson

      Hi There,

       

      I think it is a recursive calculation I need here!

       

      I have a table in my database, which self-joins as it records areas within areas. So, if it were describing a campus there would be one record for Building 2, one record for fourth floor, several records describing the rooms within those floors and possibly records for each areas within those rooms. As it's used for describing many different ways of splitting a location up, this model allows it to record as many areas as we want in as many levels of joined records as we want.

       

      Each record has a primary key, a foreign key that is the primary key of the area it is within and a name. The top level record can be distinguished as the parent_area_id field would be empty.

       

      The problem I have is that I need to have a calculated field in each record that returns a text breadcrumb trail of the names of the area and those above it.

       

      So, in the example above I'd have one that says for instance: Building 2 > Fourth Floor > Room 1 > Bar Area.

       

      I can do this by using lookups etc but they then don't change if areas are assigned to other areas and would also require processing to apply to existing records, Since these are imported from a SQL system that holds the basic records this would be a problem.

       

      I'm sure it's possible to do through a calculation but it's making my head go funny!

       

      Any ideas?

       

      Thanks in advance.

      Jules

        • 1. Re: Recursive calculation
          DrewTenenholz

          Julian --

           

          Take a look at the sample files posted by Ernest Koe at (http://proofgroup.com/blog/2011/aug/devcon_2011_recap#attachments)

           

          The Accordion Demo is a great example of what you are looking for, plus about 20 other things you should be thinking about.  He encourages people to take ideas from these files, and even has started a design-centered site for great interface design ideas that are portable to your FileMaker solutions. (http://fmpatterns.com/)

           

           

          Within the Accordion Demo, take a close look at the relationships graph and the function definitions for node::children_idList which uses the List() function along with the relationships to produce the kind of information you are looking for.  It may take some adjustments if you want the text to appear as you typed "Building 2 > Fourth Floor > Room 1 > Bar Area", but after seeing the file, you might want to use more of it than what you have built already.

           

          -- Drew Tenenholz

          • 2. Re: Recursive calculation
            comment

            I believe this should work:

             

            cBreadcrumb =

            Substitute ( List ( Parent::cBreadcrumb ; Name ) ; ¶ ; " > " )

            • 3. Re: Recursive calculation
              JulianJohnson

              Hi Michael & Drew,

               

              Thanks very much for your help on this. I had a look around the Accordian file but the breadcrumb calculation was quick and perfect thanks.

               

              Kind Regards

              Jules

              • 4. Re: Recursive calculation
                DrewTenenholz

                Julian --

                 

                I'm glad the simple calculation suffices.  It is very similar to the one used in the accordion file, except that there is some text manipulation to make it look pretty. 

                 

                The accordion example does many things including a nice set of portal key value manipulation steps to give a very pleasing and intuitive 'drill-down' interface for your hierarchical tree of values. 

                 

                With a little manipulation, you can set up the script triggered when clicking an item with no descendents to display some very specific information like another portal of all reservations for a room, limited to only a certain date range.  It is sort of springboard for making an interface that lets your users zip through their work.

                 

                -- Drew