6 Replies Latest reply on Apr 3, 2014 11:30 AM by AdamQuintero

    Portal Filtering by Date Range and Name

    AdamQuintero

      Title

      Portal Filtering by Date Range and Name

      Post

           Greetings-

           I'm trying to filter a portal using a date range where the user selects the date from two global fields.  Also, I want to narrow that set with a text search on the "Name" field. I've been successful at both, but not at the same time. I think it's just a bit of logic that I'm missing.

           I'm using this filter for the dates(quoted from an older post by PhilModJunk):

      "Tasks 2::Date_Start ≥ Tasks::g_StartDate

      and

      Tasks 2::Date_Start  ≤ Tasks::g_FinishDate"

      I'm using this filter for the name:

      PatternCount(Tasks 2::Name ; Tasks::g_Name ) > 0

      or

      Tasks::g_Name = ""

            
           In the combined filter, I did this:
            
      "Tasks 2::Date_Start ≥ Tasks::g_StartDate
            
      and
            
      Tasks 2::Date_Start  ≤ Tasks::g_FinishDate"
            
      and

      PatternCount(Tasks 2::Name ; Tasks::g_Name ) > 0

      or

      Tasks::g_Name = ""

           What happens in the portal is nothing ever changes no matter what dates I put in, and either all records show (when nothing in Tasks::g_Name) or no records show (when anything in Tasks::gName). Is there a better or correct way to do this?

        • 1. Re: Portal Filtering by Date Range and Name
          philmodjunk

          I removed some quotes that shouldn't be there. Then added parenthesis. (I could have left in > 0 as that's not wrong, but removed it to keep the expression as simple as possible.)

          ( Tasks 2::Date_Start ≥ Tasks::g_StartDate

          and
                
          Tasks 2::Date_Start  ≤ Tasks::g_FinishDate )
                
          and

          ( PatternCount(Tasks 2::Name ; Tasks::g_Name )

          or

          Tasks::g_Name = "" )

          • 2. Re: Portal Filtering by Date Range and Name
            AdamQuintero

                 I totally overlooked the use of parenthesis in order of operations. There is one extra closing parenthesis on the second to last item. Slight edit and this works perfectly:

            ( Tasks 2::Date_Start ≥ Tasks::g_StartDate

            and
                  
            Tasks 2::Date_Start  ≤ Tasks::g_FinishDate )
                  
            and

            ( PatternCount(Tasks 2::Name ; Tasks::g_Name

            or

            Tasks::g_Name = "" )

            • 3. Re: Portal Filtering by Date Range and Name
              philmodjunk

                   What I posted was correct. What you posted is incorrect. You need: PatternCount(Tasks 2::Name ; Tasks::g_Name )

                   here or you are doing a pattern count against the values 1 (True) or 0 (False) instead of the text in g_Name.

              • 4. Re: Portal Filtering by Date Range and Name
                AdamQuintero

                     PhilModJunk-

                     First off, thank you so much for your help. This is what I did:

                     I first type the expression you posted exactly how it was written. Filemaker gave an error when I pressed ok. I looked more closely and noticed there are 2 "(" and 3 ")". When I removed the second ")", the expression was accepted and appears to work. Here is the original expression, before I edited it:

                ( Tasks 2::Date_Start ≥ Tasks::g_StartDate

                and
                      
                Tasks 2::Date_Start  ≤ Tasks::g_FinishDate )
                      
                and

                ( PatternCount(Tasks 2::Name ; Tasks::g_Name )

                or

                Tasks::g_Name = "" )

                     I really appreciate the help. I'm wondering why Filemaker raises the error "An operator is expected here" when I type this into the calc. I checked and double checked to make sure I typed exactly what you wrote. Maybe there is an "(" that needs to be added, rather than taking out an ")"? I'm just starting to get my head around this, and I don't really know how to change it so it's accepted as a valid calc expression.

                • 5. Re: Portal Filtering by Date Range and Name
                  philmodjunk

                       On the contrary, there are 3 left parenthesis. Don't forget the on after the "t" in PatternCount.wink

                  • 6. Re: Portal Filtering by Date Range and Name
                    AdamQuintero

                         You are absolutely correct. I entered the expression wrong. Thank you for your patience.