6 Replies Latest reply on Sep 4, 2014 5:32 PM by TimoThoms

    portal filtering of child records based on multi-join table

    TimoThoms

      Title

      portal filtering of child records based on multi-join table

      Post

      I'm developing a database with a built-in reference manager for academic sources. I have a three-way join table that connects the following tables: 'items', 'creators' and 'creator_types'. Each item can have more than one creator, and each creator can have produced more than one item. In addition, each creator can be of different types for different items; for instance, a creator may be the author of a chapter in an edited book, or may be the editor of a book, and many creators are both authors and editors for different items. (There are also different 'item_types' but that does not matter to my question below.) Depending on item_type, my data entry forms have portals for entering and displaying authors or editors or both. 

      My initial approach was to set up the relationships as in the image below. In the layouts I then have an author and/or editor portal. This works well for data entry, in that it fills in 'Join_Items_creators::creator_type_ID' based on the respective global fields, but of course the portals do not display the correct data; rather, both the author and editor portals (using the 'Creators_Authors' and 'Creators_Editors' TOs) display exactly the same data regardless of creator_types. I thought I could remedy this with portal filtering based on 'Join_Items_creators::creator_type_ID' but this has led to some strange behavior where all creators are shown in one portal but not the other despite the fact that the underlying 'Creators' table contains the correct data. I have not been able to figure out why.

      This whole episode made me realize that I don't understand portals as well as I should, and I wonder whether there is a simpler way to achieve the functionality I'm looking for. I would appreciate any advice on how to use portals here for both data entry and display. 

      graph.jpg

        • 1. Re: portal filtering of child records based on multi-join table
          philmodjunk

          I suggest that you:

          a) identify which of the above table occurrences was specified as the table for your layout where you had your portal.

          b) describe how your portals were set up and..

          c) post the actual portal filter expressions that you used that were not working for you.

          • 2. Re: portal filtering of child records based on multi-join table
            TimoThoms

            Hello Phil, 

            Thank you for your response. 

            a) 'Creators_Authors' is the TO for the 'Authors' portal, and 'Creators_Editors' is the TO for the 'Editors' portal.

            b) The portals are in several layouts based on the 'Items' TO and have two fields from the 'Creators' table, 'last_name' and 'first_name'. 

            c) I used the Filter Portal Records option in the Portal Setup with the expressions:

            Join_Items_Creators::creator_type_ID = "creatortype01" [for authors] 

            Join_Items_Creators::creator_type_ID = "creatortype02" [for editors] 

            Thank you!

            • 3. Re: portal filtering of child records based on multi-join table
              philmodjunk

              Your portal filter expressions look like they would work perfectly--if your portal were based on Join_Items_Creators. wink

              But from a layout based on items, I think you'll have to make additional TO's of the join table, link each to a different "Creator" TO and then define your filter expressions to refer to fields in the added TO linked to the portal's particular Creator TO. Sometimes we indicate that these expressions (and conditonal format expressions) need to refer to TO's that are "down stream" from the Portal's TO. When the expression refers to a TO that lies between the layout's TO and the portal's TO, the reference evaluates from the context of the layout and this won't produce the results that you want.

              Hmmm, that's yet another "context" issue... a "context" issue of Adventures in FileMaking is a possibility that I've been considering. This thread may have just sparked another part of that concept to include into such a venture...

              Caulkins Consulting, Home of Adventures In FileMaking

              • 4. Re: portal filtering of child records based on multi-join table
                TimoThoms

                Phil,

                Thank you for thinking about this. I suspected that there was an issue of TO mismatch, but I have trouble figuring out the solution. I had not thought of using two TOs of the join table, and I'm not yet clear on how that would solve the issue, because I would still need to define the filter expression based on a field in the join table, which is "upstream" from the Creator TO, i.e. between layout and portal TOs. I wonder whether I need an entirely different approach that does not use portal filtering. 

                By the way, I had a look at your first AIF... very helpful for FM learners such as myself! 

                 

                 

                • 5. Re: portal filtering of child records based on multi-join table
                  philmodjunk

                  Your filter expressions would refer to the new TO's not the original TO of the join table and this then works within the correct context.

                  In this expression, the blue text would need to be changed to refer to the "downstream" TO of the Join table:

                  Join_Items_Creators::creator_type_ID = "creatortype01"

                  • 6. Re: portal filtering of child records based on multi-join table
                    TimoThoms

                    Phil, 

                    I see... I previously misunderstood where to place the additional table occurrences. Now I have 3 TOs of the join table, one upstream and the other two downstream from the Authors and Editors TOs respectively. That seems to have done the trick. 

                    Many thanks for your advice. I look forward to your "contextual" AIF.