1 2 Previous Next 24 Replies Latest reply on May 20, 2016 1:29 AM by GwenChen

    Filter Portal to Show All or Active Only

    Sciman

      I have a portal currently showing all matching records related to the main record being viewed.

       

      The table with the matching records includes a "Status" field indicating whether the record is "Active" or "Inactive".

       

      I would like to provide the ability to select and show the matching portal records according to the following possibilities:

      1) show "All", allowing the portal to show all matching records (i.e. regardless of "Active" or "Inactive" status),

      2) show "Active", limiting the portal to show only those matching records that are "Active"

      3) show "Inactive", limiting the portal to show only those matching records that are "Inactive"

       

      While I have been able to filter the portal using a global field in the main table that can be set to either "Active" or "Inactive" (accomplishing 2 and 3 above), I have not been able to accomplish 1 above. Ideally, I would like to provide the option "All" in the global field, to show all matching records in the portal, regardless of the record status.

       

      Any suggestions?

       

      Thank you in advance,

       

      Sciman

        • 1. Re: Filter Portal to Show All or Active Only
          erolst

          Try

           

          Case (

            ThisTable::gFilterField = "All" ; // for "All" …

            True ; // return all records; otherwise …

            ThisTable::gFilterField = YourPortalTO::status ; // … status must match filter

          )

          • 2. Re: Filter Portal to Show All or Active Only
            beverly

            I do something similar, only I allow "empty" selection to show all the records.

             

            my filter:

             

            If ( IsEmpty( table::selection_g ) ; not IsEmpty( table::recordID ) // use whatever makes a valid record to show "all"

             

            ; table::status = table::selection_g // otherwise use whatever is selected

             

            )

             

            This way there is no need to change the value list to include "All". You may need to test for values, such as a multiple selections. The above calc can change from "if" to "case", but it may be rather complex for multiple selections.

             

            Beverly

            • 3. Re: Filter Portal to Show All or Active Only
              erolst

              Actually, I would use 1 and 0 in the background, and Conditional Formatting/Hide to display the selection.

               

              Anyway, either/both of our approaches should help the original poster getting familiar with the logic involved.

               

              Beverly Voth wrote:

              … not IsEmpty( table::recordID ) // use whatever makes a valid record to show "all"

               

              Wouldn't that simply be 'True', as in my example?

              • 4. Re: Filter Portal to Show All or Active Only
                Sciman

                Thank you for the response. 

                 

                I have been working through your suggestion, but seem to be missing something.  Am I correct in assuming that the suggestion you provided would be used in the "Portal Setup" / "Filter Portal Records" / "Specify Calculation" entry location?  If so, something is not working correctly in my implementation. 

                 

                To troubleshoot this, I have simplified the calculation in this location to simply look for a match between the global filter field in the parent table and the status field in the child table and this seems to be at least one of the problems being encountered (there may be others lurking behind this...).  When I set the "Specify Calculation" entry to simply "YourPortalTO::status = ThisTable::gFilterField", as in your example except with the actual table and field names used in my file, no records are displayed.

                 

                When I set the "Specify Calculation" entry to "YourPortalTO::status = "Active"", the active records are successfully displayed so the logic seems fine for this simple case.  When replacing the "Active" value specified directly in this calculation with the reference to the value stored in the global field "thisTable::gFilterField" as above, however, nothing is matched regardless of the value stored in "thisTable::gFilterField".  I believe that both fields are setup correctly, as when I was earlier filtering the table through the database "Relationship" to the TO used, specifying the join criteria just as above, the results were successfully limited by the selection made in the "ThisTable::gFilterField".

                 

                What am I missing?

                 

                Thanks,

                 

                Sciman

                • 5. Re: Filter Portal to Show All or Active Only
                  Sciman

                  Thank you Beverly for your response also.  Please see my previous reply to erolst, for latest status of this.

                  • 6. Re: Filter Portal to Show All or Active Only
                    erolst

                    Sciman wrote:

                     

                    Thank you for the response. 

                     

                    "YourPortalTO::status = ThisTable::gFilterField", as in your example except with the actual table and field names used in my file

                     

                    "YourPortalTO::status = "Active""


                    "ThisTable::gFilterField"

                     

                    I'm noticing an awful lot of double-quotes here. Are they here merely for the purpose of reference, or are you, per chance, using these expressions literally in quotes within the Specify Calculation dialog?

                     

                    Anyway, see if the attached sample gets you going.

                    • 7. Re: Filter Portal to Show All or Active Only
                      Sciman

                      The quotes were simply to reference either specific FileMaker locations (e.g. Portal Setup) or actual strings being entered.  In the case of actual strings being entered, everthing between the outer quotes was entered exactly as indicated, but without the outer quotes as shown below

                       

                      YourPortalTO::status = "Active"

                       

                      I apologize for the confusion.

                       

                      Thank you for attaching the sample.  If I understand it correctly, you are first creating a variable for the value stored in the global field (i.e. f = FilterPortal::gStatus) and then using this variable to apply the filter (rather than using the global field directly)?  Is this required?  If so, that would be what I was missing!

                      • 8. Re: Filter Portal to Show All or Active Only
                        erolst

                        Sciman wrote:

                         

                        The quotes were simply to reference either specific FileMaker locations (e.g. Portal Setup) or actual strings being entered.  In the case of actual strings being entered, everthing between the outer quotes was entered exactly as indicated, but without the outer quotes as shown below

                        I thought so, but wanted to make sure.

                        Sciman wrote:

                        If I understand it correctly, you are first creating a variable for the value stored in the global field (i.e. f = FilterPortal::gStatus) and then using this variable to apply the filter (rather than using the global field directly)?  Is this required?

                        No; it merely makes for a briefer formula (good for you / the next reader; and the longer the formula / referenced field names, the more pronounced the gain), and FileMaker only needs to read in the referenced field's value once (good for performance, though in this here case the effect of that optimization will be miniscule).

                         

                        What you're missing (and we forgot to tell you) is that you need to refresh the portal display. That is what the script attached to the selector field does – reset a field used in the portal relationship to itself, which is “cheaper” than refreshing the window and resetting ALL caches using Refresh Window[].

                         

                        btw: next time you have trouble with a value coming from a “value list-type” formatted field, duplicate that field object and display the copy as an edit box, to see the actual value it holds. In your first attempt, the global field may have held the desired value, but also plus some other value(s) that was/were obscured by the value list formatting.

                        1 of 1 people found this helpful
                        • 9. Re: Filter Portal to Show All or Active Only
                          morten_r

                          The following hack works for me:

                          In addition to your status field, set up a calculated text field (status_ctxt) in the child table, that is

                           

                          Equal to the general text field (status_gtxt) in the parent table (All) when that field equals All

                          Equal to the child  status field otherwise

                           

                          Case (

                          Parent::status_gtxt = "All" ; "All";

                          status_txt)

                           

                           

                          Then filter on status_ctxt = status_gtxt


                          Children::status_ctxt = Parent::status_gtxt

                           

                          One should probably make a value list for possible status entries as well

                           


                          • 10. Re: Filter Portal to Show All or Active Only
                            erolst

                            morten_r wrote:

                            The following hack works for me:

                             

                            Why would you need such a roundabout “hack” when you can do it in a proper fashion (as described in this thread, and shown “in action” in the attached sample file)?

                             

                            One of the great benefits of portal filtering (as opposed to relationship predicates) is that it allows you to manipulate the data on the front end within a calculation context to get the desired results/matches – no need to create unnecessary fields in the backend!

                             

                            That being said: welcome to Technet!

                            • 11. Re: Filter Portal to Show All or Active Only
                              beverly

                              ++1

                               

                              That is one of the reasons we have portal filters - to minimize the number of relationships, calculations and to "dynamically" change the filter.

                               

                              Beverly

                              • 12. Re: Filter Portal to Show All or Active Only

                                Why not making a checkbox set with Active & Inactive?

                                And add global status field in relationship.

                                 

                                When selecting both, you see them all.

                                When selecting one, you only get those.

                                 

                                No refresh window needed in this case...

                                 

                                Screen Shot 2014-11-17 at 15.21.46.png

                                • 13. Re: Filter Portal to Show All or Active Only
                                  erolst

                                  Jan Stieperaere wrote:

                                   

                                  Why not making a checkbox set with Active & Inactive?

                                  And add global status field in relationship

                                  Because this thread discusses filtering portals, not relationships.

                                  • 14. Re: Filter Portal to Show All or Active Only
                                    Sciman

                                    Thank you for clarifying.  Refreshing the portal display was what I was missing and all is working now. 

                                     

                                    Thank you for all of your assistance and patience!

                                     

                                    Sciman

                                    1 2 Previous Next