3 Replies Latest reply on Nov 25, 2009 8:17 AM by philmodjunk

    Filter/query help required

    SPED75

      Title

      Filter/query help required

      Post

      I'm creating a database for property management.  I have a table for properties and a related table for safety certificates.  I want to create a new table layout that only shows the properties that do not have any related records in the safety certificates table. Is there a simple solution to create this filter?

       

      Thanks,

      Steve 

        • 1. Re: Filter/query help required
          philmodjunk
            

          I don't know how you have linked your tables. I am assuming you have the following relationship:

           

          Properties:: PropID = Certificates:: PropID

           

          In your Properties table, define the following calculation field, cPropIDCert:

           

          Certificates:: PropID

           

          For manual finds, place this field on a layout based on your Properties table.

          Enter find mode

          Enter an =  in cPropCert

          Perform the find

           

          The resulting group of records will be all property records that do not have related records in Certificates.

          • 2. Re: Filter/query help required
            SPED75
              

            Thank you, that certainly answers my query, but taking it a a step further...

             

            The Safety Certs table is actually a Join Table (containing the certificate expiry dates) between the Property Table and Certificate Types table ie Gas, Electric, Fire Safety, etc...

             

            How do I create a new layout that shows the properties that do not have a certain type of certificate? 

             

            By the way, I am new to Filemaker, currently running version 10. 

            • 3. Re: Filter/query help required
              philmodjunk
                

              Instead of creating a new layout, you can perform a find to show just those properties lacking a given certificate.

               

              Enter find mode on  a property layout that also displays the "Certificate type" field in a portal.

              Enter the type of certificate in question.

              Click the omit button

              perform the find

               

              This finds all Property records that don't have related record with the specified certificate type.