13 Replies Latest reply on Jan 5, 2011 11:03 AM by Sorbsbuster

    Filter portal issue

    ultranix

      Title

      Filter portal issue

      Post

      I have a FORM layout (my problem would be gone in LIST view, but it would also remove most of the interface), in which I placed portal of players and pop-up menu, the value in which determines which players from which team are shown. For that I use global field and filter portal option.

      I also have some stats and averages next to players. Below the portal, I have a summary-like row, where averages of all the players are shown. I tried to make it dynamic and update depending on what team is currently shown (filtered in portal) and for that I use one-line, no-field portal with the same filter portal formula as the portal with players above. But I just doesn't work.

      I am having self related table occurence, and all the records in that particular layout are shown no matter which record is current. I use only one table for all the stats, just different occurences, but I believe it's enough have two table occurences (main and related table, both are occurences of the same table) to make this work.

      How do I make it work?

        • 1. Re: Filter portal issue
          philmodjunk

          I tried to make it dynamic and update depending on what team is currently shown (filtered in portal) and for that I use one-line, no-field portal with the same filter portal formula as the portal with players above. But I just doesn't work.

          You'll need to describe that in more detail is this method should work for you, provided that the fields you put in this one row portal are summary fields.

          How doesn't this work for you? Can't see any data in this portal? See the wrong values? Doesn't refresh correctly when you select a different team in the drop down?

          • 2. Re: Filter portal issue
            ultranix

            Wrong data shows up.

            I used John Mark Osborne's filter portal video tutorial, where he described how, depending on what the minimum product price you enter, the total always updates accordingly. Strangely enough, when I tried to mimic the technique, results don't update.

            I have script trigger "OnObjectModify" to run that refresh window script for the filter portal, and it works fine. Do I need another one for summary row? As I've seen in John's example, it's not used.

            As I said, I'm using self relations, so i tried to use merge calculation values from both original table occurence and particularly from the relational table occurence (as seen in video), but results just don't update, I see averages of all players, no matter which team I pick.

            • 3. Re: Filter portal issue
              philmodjunk

              If both portals refer to the same Table Occurrence (List the same "box" from Manage | Database | Relationships in Show Records From... of Portal Setup) and both use exactly the same filter expression, and the "total" fields in the second portal are summary fields, the correct data should appear in your second portal.

              Since it doesn't, you'll need to check each of those requirements and see what needs to be corrected.

              • 4. Re: Filter portal issue
                ultranix

                1) Both portals use the same table occurence.

                2) Both use exactly the same filter expression.

                3) Total fields on the layout are merge fields, using data from summary fields, Summary. Summary method: Average of:

                Correct data appears only in the portal, but the summary fields don't update. They display averages of all players, but never update when i select particular team.

                • 5. Re: Filter portal issue
                  philmodjunk

                  As a test, try using a straight data field instead of a merge field to make sure that the field is completely within the borders of the portal.

                  • 6. Re: Filter portal issue
                    Sorbsbuster

                    BTW: my understanding is that using the global field as the relationship key field will cause the portal to display only those matching records, as you would expect.  And calculations based on that relationship would calculate across all the matching records - as you would expect.  But using the 'Filter Portal' function is (as I understand it) a display feature - the portal may be additionally filtered to show a sub-set of the related records, but any calculations will still use all the records matching the relationship.  This may not be a problem now, but it might be something that throws you later if you are indeed using the global field to 'filter' the related records, and also the 'Filter Portal' function.

                    Are you sure you are using the 'Summary' function correctly?  I am (was) amazed that simply picking up the Summary Field from the other table works - but I just tried it as simply as you've described it and it works perfectly, so every cloud (your problem) has a silver lining for someone (me: finding a new way of showing data) - thanks for that...

                    Oh - just noticed one difference (though it should not make any difference) - I didn't bother with the one-line portal to display the summary field, I just set it on the layout, below the portal.

                    • 7. Re: Filter portal issue
                      Sorbsbuster

                      Just tried it using the 1-row-portal, and it still works fine - it's just unnecessary, I think.

                      Also tried it as a merge field - works perfectly.

                      Tried it in Form View and List view - works perfectly.

                      And I didn't need to add any 'Refresh' - as soon as I change the field the summary value updates

                      Set the key field as a global, and then as an indexed field - still worked perfectly.

                      So the good news is it will work the way you want.  (I'm using FM 11)

                      • 8. Re: Filter portal issue
                        philmodjunk

                        You need the one row portal so that the filter controls the total computed. Otherwise, you get a summary that does not summarize only the records that match the filter expression.

                        • 9. Re: Filter portal issue
                          Sorbsbuster

                          As I said, I was amazed that this worked at all, but what I tried was:

                          Parent Table                  Child Table

                          KeyField                          KeyField

                                                                 Quantity

                                                                 SumQuantity (as average of Quantity)

                          I entered a series of records in the Child table with various KeyField values and quantities.

                          In the Parent table I created a layout with the KeyField and a 6-line portal to the Child Table.  I added the related field Child Table:SumQuantity directly on the layout.  I added another one-line portal to the Child Table directly below the 6-line portal and set another Child Table:SumQuantity in it.

                          As I changed the Parent Table KeyField value the portal immediately re-drew (no screen refresh needed), and both instances of the average for those records calculated and displayed correctly.  It worked if the Parent KeyField was normal or global.

                          Methinks I have perchance misunderstood what we're trying to achieve...

                          (But I appreciate the trick it has made me discover, anyway!)

                          • 10. Re: Filter portal issue
                            philmodjunk

                            Yes, what you just described is not a filtered portal, just a standard unfiltered portal.

                            In FileMaker 11, you can specify an expression and only those portal records for which the expression evaluates as true will be displayed. If you want to display summary data that only reflects data from the records that pass through the current filter settings, you need to use summary fields from the portal table inside a second, one row portal that uses the same filter. Otherwise, you will see a summary value based on all the related records, not just those currently visible in the original portal.

                            • 11. Re: Filter portal issue
                              Sorbsbuster

                              I didn't really understand in this scenario what the value was in applying two filters - by relationship and by Portal Filter.  I was thinking that they could remove the portal filter.

                              You have made me re-read the section of FM Help I had glanced over, and now I can see that the filter will apply to calculations across the same records - if it is inside the (a) portal.  Thanks for the clarification.

                              • 12. Re: Filter portal issue
                                philmodjunk

                                There are two advantages to portal filters over relationship filters:

                                You can create a variety of different portal based "views" of your data without creating a new Table Occurrence for each.

                                Some filter expressions can control what records are visible in ways that are difficult or even impossible to achieve with a FileMaker relationship. (Try using a relationship that only relates to records that have the string "xyz" somewhere within a larger body of text. I won't say you can't do it, but it won't be easy or simple.)

                                • 13. Re: Filter portal issue
                                  Sorbsbuster

                                  I recognise what you say - I've used the Filter Portal to make unnecessarily long portals (ie: a large number of related records) sort much quicker specifically for IWP users, for example, by filtering them by a global date.  Maybe I have not understood correctly what the poster is trying to achieve, but I didn't pick up what the advantage was in this case.

                                  I'll duck out now.