5 Replies Latest reply on Feb 6, 2015 11:17 AM by clarkm

    Can a portal show data from grandparent, parent and child table occurences?

    clarkm

      I have a database structure with a "master series" which has multiple "child series" each of which have multiple "books."  I have built a "series master" detail form.  I would like a portal that shows the series and book information for the children of the master series.  The portal displays the book rows properly, but only appears to show the first series name, not the series name linked to the book.

       

      If I create a series portal, the series names display properly.  If I create a book portal, the book entries display properly.  However when I have a portal containing both series and book data on a layout with the context of the master series, the series data is not correct.

       

      Is it possible to show data from multiple levels in a hierarchy on a portal?

        • 1. Re: Can a portal show data from grandparent, parent and child table occurences?
          electon

          This is normal behaviour. The workaround would be to link the grand child TO's to the master via global fields in the master that are set to the id of the child portal record when selected. A transparent button on portal row would work or a script trigger (on object enter ) on the portal. Maybe a commit would be necessary, but it depends. Testing would be best.

          • 2. Re: Can a portal show data from grandparent, parent and child table occurences?
            nicolai

            There is nothing built-in and easy to use in FileMaker, but it is possible to build it from scratch. Google "FileMaker hierarchy" , there are lots of articles. I like this one, although it does not use a portal:

             

            Infinite Hierarchy | HOnza's Bits @ 24U

             

            It is a better approach, as a portal with a complex hierarchy might have performance issues

            • 3. Re: Can a portal show data from grandparent, parent and child table occurences?
              electon
              There is nothing built-in and easy to use in FileMaker

              I would disagree, handling data between UI and database, search, navigation through records, security and many more are built in and fairly easy to use compared to SQL solutions. Filemaker users take it for granted sometimes. It's only when more complex issues arise that the slope can get steeper. Just like in any other systems.

              Infinite Hierarchy is a very nice "virtual list workaround", however can be quite complex to put together. Could it be used for manipulating data, which is easy to do with a TO and a portal?

              Or is it more of a "display only" thing...

              • 4. Re: Can a portal show data from grandparent, parent and child table occurences?
                nicolai

                On my experience hierarchies are used for display and navigation mostly. navgme did not ask for modification of data in his question, but this could be a requirement, so point taken.

                 

                By stating that there is nothing built-in I meant that there is no object similar to portal, which is called "Hierarchical Portal" and you can drop onto layout and setup in a few minutes or there is no special setup in the portal object which will make it hierarchical. I think this is that  clarkm was actually asking

                 

                I do agree with electon, FileMaker is a good tool to accomplish this task, but as I said, it still require some work .

                • 5. Re: Can a portal show data from grandparent, parent and child table occurences?
                  clarkm

                  This solution may require a tree structure like a bill of materials in the future, so it is good to have the reference to the tree view article.

                   

                  For now, it will be a three level hierarchy Master Series -> Series -> Book.  I do not need it to display as a hierarchy right now.  The Master Series is the context of the layout and will not vary on the layout.  The series and book will need to vary.


                  It seems strange that the portal allows for selecting multiple TO's but does not display the middle TO row fields properly.  To confirm, if there is a portal containing both the series and book information linked to master series, the series data will show the first rows fields, not the row linked to the book? 

                   

                  Essentially I would like the portal to show the SQL:

                  • Select Series.Name, Book.OrderInSeries, Book.Title, Book.Author, Book.Genre
                  • From Series, Book, MasterSeries
                  • Where MasterSeries = <Current row of MasterSeries>
                  •   and Series.IDf_MasterSeries = MasterSeries._ID
                  •   and Book.IDf_Series = Series._ID
                  • Order by Series.Name, Book.OrderInSeries, Book.Title;

                   

                  If the portal only supports one child level linked to a non-varying parent, then I understand from the reply to the original posting that two possible solutions are:

                  1. Add two portals.  One for series and one for books.  Use a scripts and triggers to show the books of the selected series, or
                  2. Build a tree view