3 Replies Latest reply on Jul 1, 2013 11:32 PM by philmodjunk

    finding records that match another table

    chadmccomas

      Title

      finding records that match another table

      Post

           I have a question.  In Filemaker, I have two tables:

            

           1.  customers

           name, address, city, state, zip, phone

            

           2. zipcodes

           zip

            

           Is there a way to search through the customers table and only show results where the zip exists in the zipcodes table?  

           In other words, I have like 20,000 customers over the years.   I created the zipcodes tables with specific zipcodes so I can find only customers that have those zip codes.   It is 400+ zips though.  So I don't want to click on New Request 400+ times. 

            

           Does this make any sense?   Thanks for your help.  : )

        • 1. Re: finding records that match another table
          philmodjunk

               Manually:

               Put Zipcodes::zip on your customers layout.

               Enter find mode and put an "*" in this field. Perform the find.

               By script.

               Go to layout ["customers" (Customers)]
               Enter FInd mode[]
               Set Field [Zipcodes::zip ; "*"]
               Set Error Capture [on]
               Perform Find[]

               And with the script, you do not have to add Zipcodes::Zip to the customers layout.

          • 2. Re: finding records that match another table
            chadmccomas

                 So I ended up making a relationship between the two fields in question because it said unrelated table when I added zipcodes::zip to the customers layout.

                 And then it showed in that field - zip codes on ones that matched.

                  

                 So why does that happen?  I didn't check any of the boxes on the relationship thing - so that means I can add and delete from customers or zipcodes without the other being effected right?

                  

                 I can't believe how simple that was.  Maybe I don't understand relationships correctly.   Thank you very much for your help.  : )

            • 3. Re: finding records that match another table
              philmodjunk
                   

                         I didn't check any of the boxes on the relationship thing - so that means I can add and delete from customers or zipcodes without the other being effected right?

                   That is correct.

                   "Allow creation of records..." allows you to set up a portal to the related table and then you can create records in the related table.

                   If you enable the delete option, deleting a record in the other table also deletes all matching records (such as all records with a matching zipcode) in the table for which you enabled the delete option. This is a very powerful an useful feature, but it can have disatrous consequences if not set up correcly.

                   The sort option enables you to specify an order for the related records. If you specify descending order for a date field in the table, then the record with the latest date will be the first related record and the one with the oldest date will be the last related record. This is one of two ways to control the order of records shown in a portal and it can effect how some calculations that refer to related records evaluate.