5 Replies Latest reply on Nov 30, 2009 1:44 PM by philmodjunk

    Filter relationship

    SPED75

      Title

      Filter relationship

      Post

      I am new to Filemaker running version 10, creating a database for property management.

       

      In my database I have a Properties tables and a Leases table in a one to many relationship.  There is an Actual End Date field in Leases.  If this is less than or equal to the current date then the lease has expired.  

       

      How do I create a relationship filter to only show the leases that are current ie the Actual End Date is empty or greater than the current date?

       

      Thanks,

      Steve 

       

       

        • 1. Re: Filter relationship
          littledog
             Add CurrentDate<=Lease Date in relationship.
          • 2. Re: Filter relationship
            SPED75
              

            I want to show the current leases, where the LeaseActualEndDate is greater than the current date or is empty.

             

            I have created a global field for the current date and have created a relationship where CurrentDate<LeaseActualEndDate but how do I include the records where the lease date is empty? 

             

            Thanks,

            Steve 

            • 3. Re: Filter relationship
              philmodjunk
                

              Maybe you could use the following calculation in place of your End lease date field in the relationship:

               

              IF ( isEmpty(EndLeaseDate); Date ( 1 ; 1 ; 3000) ; EndLeaseDate )

               

              Clear the "Do not evaluate if all referenced fields are empty" check box and specify a "date" return type before you click OK.

               

              I've set this calculation to use an end lease date that is a rediculously future date when no EndLeaseDate is specified so that "no end date specified" fields will be included in the relationship..

              • 4. Re: Filter relationship
                SPED75
                   Sorry, I can be a bit slow sometimes, but where do I put the calculation?
                • 5. Re: Filter relationship
                  philmodjunk
                    

                  As a field in your Leases table.

                   

                  This is what I picture:

                   

                  Properties:: PropertyID = Leases:: PropertyID AND

                  Properties::GlobalDateField < Leases::cLeaseDate

                   

                  Where cLeaseDate is the new calculation and GlobalDateField is a date field with global storage selected and the auto-enter calculation I mentioned.