9 Replies Latest reply on Aug 12, 2015 9:24 AM by philmodjunk

    filtering portal records with more than one popup menu

    jmyers.svt

      Title

      filtering portal records with more than one popup menu

      Post

      I have a popover established with a 'Products" portal in order to add fields to a 'workorderlineitems' portal on a work order. The problem im having is with filtering the products portal. I have two popups that run off value lists that reflect qualities of each product. (Category and Brand). I also have a search field. 

      Im fairly new to FM and am having issues with getting these to sort properly. I have tried several different methods by looking at example files but still come up short. Any guidance is greatly appreciated. Ive attached a screenshot of the popover. 

       

      Thanks

      addlineitempopover.PNG

        • 1. Re: filtering portal records with more than one popup menu
          philmodjunk

          Am I correct that if no Category is selected, you want products from All Categories and if No Manufacturer is selected you want products from all Manufacturers?

          What logic did you implement for the search text? Equals, Starts with or "contains"?

          In other words, If you typed in "Ap" as search text field would it only show a product named "Ap"? or would it also show "apple", "ape" and "applesauce"? Would it also match to "Wrapper"?

          All of the above is possible, but the resulting filter expression gets pretty complex.

          • 2. Re: filtering portal records with more than one popup menu
            jmyers.svt

            I was trying to use 'contains' for the search feature. Right now I essentially have a blank slate because everything I have tried has failed.

             

            Edit - you are correct in the assumption that if no category or brand is selected that it would display all products.

            • 3. Re: filtering portal records with more than one popup menu
              philmodjunk

              The fact that an empty field means "match everything" for that part of the search logic complicates this as you could have any combination of 1, 2 or 3 fields empty at a given time. That works out as 8 different combinations of fields that are empty or hold search criteria.

              You'll need to substitute your portal table field names for mine and your layout table occurrence name for mine in the following calculation.

              (IsEmpty ( LayoutTO::PopoverSortCategories) and IsEmpty ( LayoutTO::PopoverSortManufacturer ) and IsEmpty ( PopoverSearch ) ) OR

              (IsEmpty ( LayoutTO::PopoverSortCategories) and IsEmpty ( LayoutTO::PopoverSortManufacturer ) and PatternCount ( Products | Popover::Description ; LayoutTO ( PopoverSearch ) ) OR

              (IsEmpty ( LayoutTO::PopoverSortCategories) and LayoutTO::PopoverSortManufacturer=  Products | Popover::Manufacturer and IsEmpty ( PopoverSearch ) ) OR

              ( LayoutTO::PopoverSortCategories = Popover | Product::Category and IsEmpty ( LayoutTO::PopoverSortManufacturer ) and IsEmpty ( PopoverSearch ) ) OR

              ( LayoutTO::PopoverSortCategories = Popover | Product::Category and LayoutTO::PopoverSortManufacturer=  Products | Popover::Manufacturer and IsEmpty ( PopoverSearch ) ) OR

              ( LayoutTO::PopoverSortCategories = Popover | Product::Category and IsEmpty ( LayoutTO::PopoverSortManufacturer ) and PatternCount ( Products | Popover::Description ; LayoutTO ( PopoverSearch ) ) OR

              (IsEmpty ( LayoutTO::PopoverSortCategories) and LayoutTO::PopoverSortManufacturer=  Products | Popover::Manufacturer and PatternCount ( Products | Popover::Description ; LayoutTO ( PopoverSearch ) ) OR

              ( LayoutTO::PopoverSortCategories = Popover | Product::Category and LayoutTO::PopoverSortManufacturer=  Products | Popover::Manufacturer and PatternCount ( Products | Popover::Description ; LayoutTO ( PopoverSearch ) )

              Whew! That's one long filter expression and I don't guarantee that I haven't introduced an error somewhere...

              • 4. Re: filtering portal records with more than one popup menu
                jmyers.svt

                I implemented that into the portal filter and it returns absolutely nothing. Doesn't matter what is selected/deselected. I must have something messed up with either relationships or table occurances. 

                • 5. Re: filtering portal records with more than one popup menu
                  philmodjunk

                  Before you give up on the expressions that you've set upm run this script:

                  Refresh Window [Flush Cached Join Results]

                  See if you then get anything appearing in your portal--particularly if you leave all three filter fields empty. (They really aren't "Sort" fields BTW...) I don't recommend that script step as the final fix here, but it's a good diagnostic test to see if the problem lies with the portal filter expression or in how the portal refreshes after a filter field's value has been changed.

                  Also, this type of search portal normally is based on a relationship using the X operator. Is this the case?

                  And you need at least one record in the layout's found set for this to work.

                  • 6. Re: filtering portal records with more than one popup menu
                    jmyers.svt

                    I still havent gotten this to function properly. No matter what I do, it just displays all records, or none at all. So im going back to the basics. See attached screenshot. Do I even have my relationship set up properly to make this work?

                     

                     

                    • 7. Re: filtering portal records with more than one popup menu
                      philmodjunk

                      I am guessing that you have a layout based on WorkOrders with a portal to Products | Popovers. Your relationship, as I would expect, is correct. All you need for the relationship is the X operator, the fields you select as match fields have little, if any effect on the function of your portal. (Though there are ways to manipulate the value of the layout table's match field to force an update of the portal.)

                      I suggest that you start with just one filter field and an expression for showing all records if the field is empty and filtering on the selected value if it is not. Then try moving up to two filter fields and see if you can get that working, moving on to three fields only when filtering on two works for you.

                      • 8. Re: filtering portal records with more than one popup menu
                        jmyers.svt

                        My "category" and "Manufacturer" fields that are being used for the filter fields, I currently have them in the WorkOrders table. Is that correct or do they need to be in the Products table?

                        Also, they are globally stored. 

                        • 9. Re: filtering portal records with more than one popup menu
                          philmodjunk

                          Since they are globally stored, it does not matter which table. Global fields are accessible by any script, layout or calculation in your file.

                          But for filtered portals, they can result in a portal refresh issue. Changing the value of a global field would not automatically update what records appear in the filtered portal. If you used local fields in the layout's table, committing records after a change is all that is needed to update the portal.

                          A quick way to test to see if the portal has failed to refresh is to perform a script with the script step: Refresh Window [Flusch Cached Join Results] and see if the records shown in the portal change.

                          If that resolves an issue, you can use Refresh Portal ["portal Object name here" ] if you are using FileMaker 14. With older versions, you can use set field to "goose" the portal by setting a match field on the layout table's side of a Cartesian join (X operator) used for your portal to any value--even it's current value. This triggers a refresh of the portal even though the value of the field has no effect one what records appear in the portal.