9 Replies Latest reply on Mar 17, 2012 8:50 PM by kbsaunders

    Use Find Or Relationship?

    kbsaunders

      I am trying to set up a layout that will display owners who's property is in a selected zip code. The owners don't live in the property. In other words, their address is different than the property's. Once found, I want to print mailing labels using the owner's address, not the property's.

       

      I would like to do this using a relationship but can't figure it out. I am selecting the zip codes via a self-join relationship but can't figure out how to have a found set of owners who's property is in that zip code. Is this possible or do I have to use finds instead?

       

      If possible, how would I set it up? If not, how would I set up the finds? What table would the layout be based on?

       

      I am in over my head on this one and would appreciate any help.

       

      Thanks,

       

      Kevin

        • 1. Re: Use Find Or Relationship?
          kbsaunders

          I forgot to mention, I have three tables: owners, properties and zip codes.

          • 2. Re: Use Find Or Relationship?
            LyndsayHowarth

            One owner could own more than one property and one property can have more than one owner.... so you need a join table between these 2 tables... and if each has an ID this is easy and will allow access to the information from either table.

             

            Properties have postcodes... so the relationship is a match with the Postcode in the postcode table.

            REALLY, you don't actually need the Postcode table as you can do a self-relationship of postcode=postcode inside the Properties table.

             

            To display the properties in a single postcode you could use the postcode table and have a permanent portal which shows the related properties... then by finding a postcode you could see the related properties for a new postcode, This would also allow a range of postcodes to be found. This technique could be used equally easily in Properties.

             

            Another way to do this is to ignore Postcodes table and create a global _Postcode field and create a new relationship with the postcode field where _Postcode=postcode. This would allow you to display the related properties in a Portal or GoToRelatedRecord() in a new window in a list view of the properties... for example.

             

            Many ways to do this and it all depends on what you want...

             

            - Lyndsay

            • 3. Re: Use Find Or Relationship?
              comment

              kbsaunders wrote:

               

              I forgot to mention, I have three tables: owners, properties and zip codes.

               

              You have a table of zip codes? What information does it keep?

              • 4. Re: Use Find Or Relationship?
                kbsaunders

                It is only zip codes. This is a subset of all the property zip codes in the database to whom I want to mail to. (Some are not in my marketing area.) I select a few of thes zip codes every month to which I want to mail to the owners.

                • 5. Re: Use Find Or Relationship?
                  kbsaunders

                  Each property has only one owner (that is mailed to) at any time. But ownership does change. I do use IDs and understand the join table concept.

                   

                  I like the idea of not using a zip code table but the zip codes in the properties table instead. However, how could I select a few zip codes to mail to from a subset of all the zip codes in my properties table (I don't do marketing in them all). For example: there are 40 different property zip codes in the table. I am mailing to owners of properties in 29 of the zip codes. Each month I mail to the owners of properties in five of those zip codes.

                   

                  Kevin

                  • 6. Re: Use Find Or Relationship?
                    comment

                    Assuming you have the following chain of relationships:

                     

                    Owners -< Properties >- ZipCodes

                     

                    If you find the zip codes you want to use in the ZipCodes table, you can then do:

                     

                    Go to Related Record [from Owners, Show related only, Match found set]

                     

                    to create a found set of owners who own a property in (at least one of) the selected zipcodes.

                    • 7. Re: Use Find Or Relationship?
                      comment

                      NOTE: this part is not clear to me:

                      kbsaunders wrote:

                       

                      I am selecting the zip codes via a self-join relationship

                      • 8. Re: Use Find Or Relationship?
                        wrwaugh

                        If you make a relationship between Owners and properties (I am not sure how you would want this relationship to be defined... while it might create the occassional mistaken ownership, you could relate the two by Owner::Name and Properties::OwnerName)

                         

                        Now, make a layout of Properties (viewed as a list) where each record shows the property information and the related owner information.

                         

                        If you do a manual or scripted find for the zip codes of interest you will now essestially have a list of properties with that zip code showing the owners and their addresses for mail merge purposes.

                         

                        Depending on how you do a mail merge and other factors, this might work for you while avoiding the need to populate a join table.

                        1 of 1 people found this helpful
                        • 9. Re: Use Find Or Relationship?
                          kbsaunders

                          Thanks everyone for your help. With your suggestions and some ideas of my own, here is what I came up with:

                           

                          Screen Shot 2012-03-16 at 2.58.12 PM copy.jpg

                          Click to inlarge.

                           

                          This allows me to have a list of owners for mailing to that updates automatically based on the property zip codes selected in the Zips table. Using the boolean global FULLADDRESSCHECK with the boolean flag field hasCompleteMailingAddress allows me to not print labels that don't have a full address.

                           

                          The one thing I am having difficluty with is using a self join table of Zips to select the zip codes being mailed to. Once selected, I want to only have properties in the selected zip codes. In other words, the Zip » SelectedZipsForMailing TO all the way at the right should only have the zip codes selected via the self join.

                           

                          Any ideas?