7 Replies Latest reply on Oct 20, 2009 11:38 AM by Tarj

    Deleting just one duplicate file but retaining the other

    Tarj

      Title

      Deleting just one duplicate file but retaining the other

      Post

      I'm using FileMaker Pro 7.0.2.  I have created a file that has names, street addresses numbers and names, city, state.  My problem is that I have double entries in the street address numbers and streets because married couples are inputted and they the same address number and street.

       

      What I want to do, regardless of which duplicate address is removed, is remove one of them and save the other.  I have the file set up in table format and have isolated all the duplicate files and haven't the slightest idea how to eliminate one of the two duplicates.  I've tried numerous scenarios to no avail and am stumped.

       

      Please keep in mind that I'm an infrequent user of FileMaker and am definitely not the sharpest tack in the box.  That means any directions have to be as though you were talking to a new kid in kindergarten........... one and one equals two............ maybe.   

        • 1. Re: Deleting just one duplicate file but retaining the other
          ninja
            

          Howdy Tarj,

           

          How many records to delete are we talking about?  10? 100? 10,000?

           

          Edit: And with a Mr. and Mrs.  if you delete one of the addresses, have you considered how you would access the address information?  If the address is only on one of two records, and you were looking at the address-free one, how would you know to look elsewhere to find the address...and where to look?

          • 2. Re: Deleting just one duplicate file but retaining the other
            Tarj
              

            This is a list of 1,864 duplicates in the form of Mr. & Mrs.. I just need the addresses of either one of the parties since they reside at the same address and will receive the same information mailer.  Thus, I want to cut that list to 932 where the result would be that I could find the names and addresses by means of the street names.

             

            I know there must be a formula to remove either one of the parties from the duplicate street name and address but I can't find it or either am looking at the formula and don't know it.  I am working with the records in the "View as Table" record format.  Hope this helps with clearing up any questions you asked. 

             

            Thanks,

             

            TJ 

            • 3. Re: Deleting just one duplicate file but retaining the other
              mrvodka
                

              You should create a second table for the addresses. Rename your current table as 'names'. You can create a looping script that will create a new addresses record. After all the records have new related addresses, you can delete out the address specific fields in your orig 'names' table.

               

              The easiest way would be to create a calculation which would identify the unique address such as ( lets call is cUnique ):

               

              Address & "_" & City & "_" & State & "_" & Unit

               

              Also create a new field in 'names' called: fkAddressID ( number field )

               

               

              Now in your new table "Addresses" create the following fields ( etc if you need others ):

               

              pkAddressID (turn on serial option under auto-enter field options)

              Address

              Unit

              City

              State

              Zipcode

              TempKey

               

               

              Create a relationship from cUnique to TempKey and turn on allow creation of related record on the Address side.

               

               

              Now go to your layout based on names and run a script such as:

               

              Go to layout [ "names" ]

              Show All Records 

              Go to Record [ First ]

              Loop

              Set Field [ Address::Address; names::Address ]

              Set Field [ Address::Unit ; names::unit ]

              Set Field [ Address::City; names::City ]

              Set Field [ Address::State; names::State ]

              Commit Record

              // Sets new foreign key

              Set Field [ names::TempKey; Address::fkAddressID ]

              Go to Record [ Next; Exit After Last ]

              End Loop

               

               

               

              Once this script has run it should have created one record for each of the unique addresses.

              Now you can change your relationship to be keyed on 

              pkAddressID = fkAddressID and you should be able to have your one to many.

               

               

              Last step. Double check everything and remove unnecessary fields.

              • 4. Re: Deleting just one duplicate file but retaining the other
                Tarj
                  

                Thanks Mr. Vodka.  I'll work with what you set out and get back to you tomorrow with the results achieved.  Thanks to all ............ I have to sign off for now.

                 

                TJ 

                • 5. Re: Deleting just one duplicate file but retaining the other
                  Tarj
                    

                  I've been working on this problem using your suggestions on and off for the past several days now to no avail.  The specific fields that I have that are duplicates of information which I want to eliminate one and have the other remain are:

                   

                  Street Number

                  Street Name

                  Last Name

                  Zip Code

                  Election Names

                   

                  I believe that by using the "Street Number" as the target to eliminate one of the duplicates the end result will be that I have the remaining single "Street Address" with all the other related information.  As I said, I'm not the sharpest tack in the box but believe there should be a relative easy way to do this without going insane.  The last time I used FileMaker before the present time was in 2004 and have forgotten almost everything except how to open the program.

                   

                  My version of FileMaker Pro 7.0.2 and I'm working on a Mack Pro with OS 10.11 if that helps.  Any help directing me to the solution would be appreciated ...... more than greatly. 

                  • 6. Re: Deleting just one duplicate file but retaining the other
                    ninja
                      

                    Howdy,

                     

                    Mr.Vodka's method is elegant...here's a brute force one that I've used sparingly (sometimes a hammer CAN drive a screw...if you hit it hard enough).  I would exhaust the elegant solutions first, and use the hammer only if you've got a couple of hours free and a big cuppa coffee.

                     

                    List the records in list view with enough info to recognize duplicates.

                    Add a button on the right set to "Delete Record/Request"

                    Sort the list by street name, and street number and zipcode.

                    Scroll through and manually kill the duplicates.

                     

                    Long, tedious, inelegant, but totally functional.  Again, I would exhaust the elegant solutions first...perhaps you didn't implement Mr. Vodka's suggestion the way he intended???

                     

                    Hope you don't need this approach, but know that it exists.

                     

                    • 7. Re: Deleting just one duplicate file but retaining the other
                      Tarj
                        

                      I tried working with Mr. Vodka's method/procedure for hours to no avail.  It's possible I wasn't applying it correctly since I'm wasn't to sharp at plugging in his formula as I read it.

                       

                      What I did to solve my problem was similar to yours except I manually went through them after acquiring the "duplicates" and omitted one of the two duplicates.  Long process but it worked.

                       

                      Thanks for your input.

                       

                      TJ