4 Replies Latest reply on Apr 17, 2012 9:18 AM by aklobby

    Create contact list omit duplicate addresses

    aklobby

      Title

      Create contact list omit duplicate addresses

      Post

      Hi, I am looking to create a mailing list for my database. Many of my records share an address. I would like to be able to create a list that would group the household records under one address or at least have a list that would omit the duplicate mailing addresses and show only one address per household. Is there an easy script I can write to accomplish this? I have been doing it by exporting my list into excel and then finding the duplicates and using the excel file to create my mailing labels, but would prefer to do it all in Filemaker.

      I am using Filemaker Pro11

      Any assistance, thoughts, guesses would be most appreciated. 

      Allison

        • 1. Re: Create contact list omit duplicate addresses
          philmodjunk

          A long term solution is to set up a separate related table of address records. People with the same address would then link to the same address record. This makes it possible to set up a set of records in the Address table and you'll get one record for each address.

          Here's a possible short term solution for working with your data as it currently exists:

          It's possible to "mostly" remove your duplicate addresses, by the following method, but it will fail in cases where two people have addresses entered that are really the same address but some minor detail, such as "ave", "suite" or such is entered differently in their individual records.

          Define a calculation field that combines all address fields into a single string. Something like:

          addressLine1 & " " & AddressLine2 & " " & City & " " & zipcode.

          Enter layout mode and double click the "body" part label to change the body part of your labels layout to a Sub Summary part "when sorted by" this new calculation field.

          Then sort your records by this field and see what you get when you preview this layout to see if you get the correct set of labels laid out in columns (if you are printing an 'avery' type sheet of address labels). We may need to make a change in layout set up if it does not preview correctly.

          • 2. Re: Create contact list omit duplicate addresses
            aklobby

            Phil, 

            Thank you so much for your response. I will employ the long term solution to future projets but the short term solution might just work out for this one. I do need to put a line break or something in between the address and the city, state, etc.. to have the avery label print correctly. 

            One more question, is there a way to get a count of the records showing? The record count shows the total records found not the total when sorted. Also, when I export this sorted mail list it exports all records (including the duplicates). If I am asking too much of the short term solution then I will go ahead and employ your long term suggestion. 

            Again, Thanks for the help. 

            Allison

            • 3. Re: Create contact list omit duplicate addresses
              philmodjunk

              The calculation field I recommeneded is not for printing, just to group records with the same address. It need not be visible on any layout for this to work.

              Actually, it is counting your records. It's just not counting your addresses as you have multiple records for some addresses. There is a method to get the count you want but it's kinda hairy. Check out this link and decide if you want to try it: How to count the number of unique occurences in field.

              When it comes to exporting the data, you can get one row per summized group if you use the "group by" options when exporting the data.

              • 4. Re: Create contact list omit duplicate addresses
                aklobby

                Export worked perfectly. Thank you. I will work on the other count at a later time. 

                Your help is much appreciated. 

                Allison