9 Replies Latest reply on Jan 14, 2011 4:23 AM by miw

    Getting a portal to show (filter) only the found set

    miw

      Title

      Getting a portal to show (filter) only the found set

      Post

      I have a simple database with a table called "Inventory".  In realtionships, I also created a duplicate table, "Inventory2" that links the InventoryID field.  I did this so that in my main Inventory window, I can show inventory details as well as have a portal (based on Inventory2) that shows a listing of Inventory.  On this layout, I can do a search on InventoryName (in my main table for the layout which is "Inventory") to get a subset of the Inventory that matches my search.  How can I also get my Inventory2 portal just to show those Inventory IDs that match my Inventory search?  I have tried various portal filters with out good results.  Any ideas?

        • 1. Re: Getting a portal to show (filter) only the found set
          philmodjunk

          I may be reading this wrong, but it seems like you are using one table where it may make more sense to use two. It sounds like you have a self join linking Inventory to a 2nd table occurrence of the same data source table called Inventory 2. That seems an odd structure to use here, but I may simply not know enough about how you've set this up.

          Define a new field with global storage called gInventoryNameSelect.

          Place gInventoryNameSelect on a layout.

          Use this script to find your inventory records:

          Enter Find Mode[]
          Set Field [Inventory::InventoryName ; Inventory::gInventoryNameSelect ]
          Set Error capture [on]
          Perform Find[]
          Refresh Window [Flush cached join results] //forces portal filter to update

          On the portal, define this portal filter: Inventory::gInventoryNameSelect = Inventory2::inventoryName.

          If you are using FileMaker 10 or older, you can get the same result by defining the relationship to Inventory2 as:

          Inventory::gInventoryNameSelect = Inventory2::InventoryName  (and the refresh window step will not be needed)

          • 2. Re: Getting a portal to show (filter) only the found set
            miw

            I gave it a try but then I had the issue of the portal only showing the found set; when I am not in a find mode, I want to have the portal otherwise show 100% of the Inventory population.  I can accomplish what I need in a list view on my layout, but I find such views provide limited flexibility for other data and good appearances.

            • 3. Re: Getting a portal to show (filter) only the found set
              philmodjunk

              You might want to use two layouts for this or you can make your portal filter something that can be switched on and off.

              IF ( IsEmpty ( Inventory::gInventoryNameSelect ) ; True ; Inventory::gInventoryNameSelect = Inventory2::inventoryName )

              You can then put a "show all" button on your layout to perform a script that clears gInventoryNameSelect and then refreshes the window.

              With FileMaker advanced, you can even customize the Show All option in the records menu to run a script that does the Show All records and then these same two steps.

              • 4. Re: Getting a portal to show (filter) only the found set
                miw

                I think I am almost there; however my find requires that I input the full input for the Inventory field in the Inventory::gInventoryNameSelectfield.  If I want to type just the first few letters to determine a match between the Inventory::gInventoryNameSelectand Inventory2::InventoryName (ie. type "Cof" to get all inventory items starting with a "Cof"), what does the calculation in my portal filter look like?  I tried using the "right" function and wildcards but I couldn't get it to work.  

                • 5. Re: Getting a portal to show (filter) only the found set
                  philmodjunk

                  Can't use wild cards but you might use:

                  Inventory::gInventoryNameSelectfield = Left ( Inventory2::InventoryName ; Length ( Inventory::gInventoryNameSelectfield ) )

                  • 6. Re: Getting a portal to show (filter) only the found set
                    miw

                    On a related note, is their a calculation that would allow me to do the filter in the portal based on an input into Inventory::gInventoryNameSelect of any text that might be the middle (rather than the start)  of InventoryName2:InventoryName?

                    You insights also helped me come up with a better interface - rather than a "find" mode and "showall" button, my portal filter has an if statement that uses Inventory::gInventoryNameSelect only if it has data in it, otherwise it filters Inventory2::InventoryName to Inventory::InventoryName which will essentially gives me unfiltered results (so I do not need the showall button).  Whenever I input or deelte anything in the Inventory::gInventoryNameSelect field, it has an on modify and on exit script to refresh the window.

                    • 7. Re: Getting a portal to show (filter) only the found set
                      philmodjunk

                      I suggest you look up text functions in FileMaker help. They can be very useful.

                      Either PatternCount or Position can be used to text to see if a text pattern is contained within a string of text such as the contents of your name field.

                      • 8. Re: Getting a portal to show (filter) only the found set
                        philmodjunk

                        Just for the fun of it here's a filter expression that let's you pick from "exactly equals", "starts with", "contains" and "All records" for your portal filter:

                        Define a field called, FilterType and place it on your layout with either radio buttons or a pop up menu of the above values that I've listed.

                        Case ( IsEmpty ( Inventory::gInventoryNameSelect ) ; True ;
                                   Inventory::FilterType = "Exactly Equals" ; Inventory::gInventoryNameSelect = Inventory2::inventoryName ;
                                   Inventory::FilterType = "Starts With" ; Inventory::gInventoryNameSelect = Left ( Inventory2::InventoryName ; Length ( Inventory::gInventoryNameSelect ) ) ;
                                   Inventory::FilterType = "Contains" ; Position ( Inventory2::InventoryName ; Inventory::gInventoryNameSelect ; 1 ; 1 ) ;
                                   TRUE )

                        Be sure to use script triggers to perform the refresh window script when a new value is entered, selected for gInventoryNameSelect or FilterType or the portal won't refresh to apply the new filter values.

                        • 9. Re: Getting a portal to show (filter) only the found set
                          miw

                          Too many "best answers" here so I will select this one so that followers can peruse the different aspects of all the answers.  Each of PhilMod's responses were very helpful whether it related to filtering the portal or the expression for comparing the search parameters to values in a field.  I was able to create a custom layout with a list portal and essentially a dynamic (always on) search capablity to refine my portal results.