11 Replies Latest reply on Jan 30, 2015 7:47 AM by MarkGores

    Multiple filters on a portal

    MarkGores

      Title

      Multiple filters on a portal

      Post

      I have a table of test plans that contains fields for Customer, part type and spec

      I am trying to set up a portal for a user to select a plan on the Job layout.  I set up 3 global search fields for the portal to use in the filter, but can't get the filters to restrict based on values each time. 

      i.e. I type Boeing in the customer field and it filters to just show the Boeing plans.  Now I want to narrow that down to part type = diode, but when I type diodes it shows all Boeing plans AND all plans for diodes rather than restricting the results, it expands them.  I am using "or" in the filter so I guess that makes sense, but if I change it to "and" I don't get any results.

      Is there a way to do this through a portal?

        • 1. Re: Multiple filters on a portal
          philmodjunk

           ( Isempty ( LayoutTable::field1 ) and LayoutTable::Field2 = PortalTable::Field2 ) OR
          (LayoutTable::Field1 = PortalTable::Field1 and LayoutTable::Field2 = PortalTable::Field2 ) OR
          (LayoutTable::Field1 = PortalTable::Field1 and IsEmpty ( LayoutTable::Field2 ) )

          Can you follow the logic to that?

          • 2. Re: Multiple filters on a portal
            MarkGores

            I think so.  It looks as bad as this calculation

             

            (If (Data1 = "-" ; $$FS; If ( STD_met = "MICROINCHES" ; Round ( Average ( Data1 ; Data2; Data3; Data4; Data5 ) ; 0 ); Round ( Average ( Data1 ; Data2; Data3; Data4; Data5 ) ; 2))))

            Which displays an average of 3 readings, rounding to a different decimal place depending on the units selected unless a "-" is entered into the first data field; in which case it displays either "Flash" or "Strike" depending on a radio button selected field.

            • 3. Re: Multiple filters on a portal
              MarkGores

              OK, the layout is based on table MasterTracker with portal to PLAN SELECT.  The intent is for Psearch1 to filter PLAN SELECT::NewPlanName and Psearch2 to filter PLAN SELECT::Master.  I added the pattern count to include anything in the field that matches.

              IsEmpty (MasterTracker::Psearch1 ) and PatternCount ( MasterTracker::Psearch2 ; PLAN SELECT::Master ) or
              PatternCount (MasterTracker::Psearch1; PLAN SELECT::NewPlanName) and PatternCount ( MasterTracker::Psearch2 ;PLAN SELECT::Master )  or
              PatternCount (MasterTracker::Psearch1; PLAN SELECT::NewPlanName) and IsEmpty (MasterTracker::Psearch2)

              It works when I enter something in Psearch1, but the portal goes blank when I enter anything in Psearch2

              • 4. Re: Multiple filters on a portal
                philmodjunk

                You are missing some parenthesis from my original example

                ( Isempty... and search1 = field1 ) or
                ( Search 1 = field1 and search2 = field 2) or
                ( Search 2 = field 2 and Isempty...)

                Some developers prefer to use Case in place of all the Or operators in this type of Expression:

                Case ( Isempty ( LayoutTable::field1 ) and LayoutTable::Field2 = PortalTable::Field2 ; True ;
                            LayoutTable::Field1 = PortalTable::Field1 and LayoutTable::Field2 = PortalTable::Field2 ; True ;
                            LayoutTable::Field1 = PortalTable::Field1 and IsEmpty ( LayoutTable::Field2 )  ; True ;
                          ) // Case

                Not sure that this is really any less complex, but use what works best for you...

                • 5. Re: Multiple filters on a portal
                  MarkGores

                  Still not working.

                   

                  (IsEmpty (MasterTracker::Psearch1 ) and PatternCount ( MasterTracker::Psearch2 ; PLAN SELECT::Master )) or
                  (PatternCount (MasterTracker::Psearch1; PLAN SELECT::NewPlanName) and PatternCount ( MasterTracker::Psearch2 ;PLAN SELECT::Master) ) or
                  (PatternCount (MasterTracker::Psearch1; PLAN SELECT::NewPlanName) and IsEmpty (MasterTracker::Psearch2))

                   

                  Must be missing something ...

                  • 6. Re: Multiple filters on a portal
                    philmodjunk

                    I think you have some fields reversed if I am decoding the names correctly:

                    Shouldn't you have this?

                    PatternCount ( PLAN SELECT::Master ; MasterTracker::Psearch2 )

                    I am assuming that Psearch2 contains the partial search text that needs to be found somewher inside Master.

                    • 7. Re: Multiple filters on a portal
                      MarkGores

                      That was it.  Thank you.

                      Now if I want to add a third search, I am guessing it will add a few more iterations of the IsEmpty

                      • 8. Re: Multiple filters on a portal
                        philmodjunk

                        And it's not a linear progression. This type of filtering expression gets very complex very quickly as the number terms needed increase geometrically with each added search field.

                        There are a pair of alternative approaches that you might investigate:

                        a) Try setting up an SQL query with the ExecuteSQL function that returns a list of _pk values from the portal field. Use this unstored calculation in a field of the parent table as a match to the portal table's primary key field.

                        b) Use a script that performs a find for these records on a different layout. You can use list view inside a small floating document window to display the results or you can capture the _pk values of this found set and set a text field in your parent table to a list of those values--this is then a similar set up to a) above where this field then serves as the match field. In FileMaker 13, you can define a "list of" summary field in the portal's table in order to easily capture a list of _pk values from a found set. In older versions, a special layout and Copy All Records can be used.

                        • 9. Re: Multiple filters on a portal
                          MarkGores

                          I got it working, took 7 lines which makes sense in a binary way.

                          I had another thought, but it doesn't work for the portal refreshing.  Instead of using a set field Master::Psearch1 to Master::Psearch1

                          I tried set field by name (Get(activefieldname) to Get(activefieldcontents).  Running through debugger and adding a dialog box step, it looked like it should have worked but it doesn't.  Ended up just making the script 3 lines and setting each search field to itself.

                          (all 3 are set up in the relationship for the portal so changing any one will refresh it)

                          • 10. Re: Multiple filters on a portal
                            philmodjunk

                            You would need to use: Set Field By Name [ Get ( activeFieldTableName ) & "::" & Get ( ActiveFieldName ) ; ...

                            • 11. Re: Multiple filters on a portal
                              MarkGores

                              *smacks self in forehead*

                              Thanks Phil, wasn't thinking there.  This has been quite a project so far.  The old solution was a flat database for each job.  I had to take thousands of Job records, export the test "Plans" - remove duplicates, then take those and extract a possible 34 "Tasks" (each Task has 3 fields) then remove empties and duplicates from there and refer them back to the appropriate plans.

                              I'm trying to set it up so when the users enter a new job, they can easily see and find a Plan that fits or at least is close.  Then from that plan they can import the Task steps that they want into the Job_Task table, then modify or add Tasks as necessary.  Also need to make provisions for throwing any changes back to the plan (if there has been a revision to a specification that will change all future jobs) or to leave it as a one time custom modification.  This is what I have so far.