3 Replies Latest reply on May 22, 2015 6:43 AM by erolst

    Help with query and omitting certain records

    alberto__1985

      My name is Alberto and I'm kind of new to filemaker so excuse me if I miss important information on my question.  I will try to be as accurate as possible.


      Ok here it goes I have two tables joined via a relationship.  Table A (Cases) and Table B (Applications).  The relationship is setup via field 1 (Case Number) in Table A (Cases) = field 2 (Case Number) in Table B (Applications).  I have a records that I want to do a search query on from Table B (Applications). First part of my query  (Query 1) is finding any records in which Field A (Application Type) = "EAD Valid Until" and  Field B (Application Date) = Get(CurrentDate) & "...01/01/2050".  This query is to find any upcoming renewals for application that are expiring in the future.  Sencond part of my query (Query 2) is finding any records in which Field A (Application Type) = "EAD Valid Until" and  Field B (Application Date) = "...01/01/2000" & Get(CurrentDate).  This query is to find any expired applications.  This application is renewed each year therefore any particular case can have one or more entries on the second query per case number, but only one for the first query.  Now the question is how can I do a (Query 2) and omit all expired applications that have a matching case number with a record in (Query 1).  The goal of this query is to find those cases which we forgot to renew an application.  If any one can help with a way to do this it would be greatly appreciated.

        • 1. Re: Help with query and omitting certain records
          erolst

          (Application Date) = Get(CurrentDate) & "...01/01/2050".

          To find all future dates, I suggest you use the more straightforward

           

          ">=" & Get ( CurrentDate ), or Get ( CurrentDate ) & "..." (both expressions will continue to work after January 1st, 2050 …)

           

          And that …

          alberto__1985 wrote:

          "...01/01/2000" & Get(CurrentDate)

          doesn't look like it would produce a valid date query (typo?). Try "<" & Get ( CurrentDate ), or "..." & Get ( CurrentDate ).

           

          Since you want to find cases, not applications, perform your queries from the context of Cases:

          Perform query 1

          "Show Omitted Only"

          Perform query 2 with Constrain Found Set


          If you do want to find Application records:

          Perform query 1

          "Show Omitted Only"

          Go to Related Record/Request [ Application ; match all in found set ]

          Perform query 2 with Constrain Found Set

          • 2. Re: Help with query and omitting certain records
            alberto__1985

            Thanks for the 2 suggestions regarding the future and past queries.  I will try your suggestion on the 2 methods for the query.  I will keep you posted if it worked.

            • 3. Re: Help with query and omitting certain records
              erolst

              Sure; if it doesn't work, my bad; come back and ask again

               

              Have fun.