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

    Filter/query help required



      Filter/query help required


      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?




        • 1. Re: Filter/query help required

          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

            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

              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.