3 Replies Latest reply on Dec 7, 2009 7:27 AM by DrDave

    Getting Duplicate Records

    DrDave

      Title

      Getting Duplicate Records

      Post

      I have a database (Customer) that is linked to another database Countries, using Customer:AddressCountry. Imagine 500 customers, living in 40 Countries.

       

      I'm writing a script that populates the Countries database and at the same time, tallies the number of customers per country, using the field Countries::CountryCount.

       

      I have other fields in the Countries database that are used for other purposes, but they are not relevant here.

       

      I begin by sorting the customer database by Country (no reason for this, except that it shows my problem more clearly)

       

      As I loop through the Customer database, I check to see if we have a Countries record for that customer's country. If not, I add a record to the Countries database. I then add 1 to the CountryCount. If the Countries record already exists, I simply add 1 to the CountriesCount.

       

      As the end of the process I have an extra set of Countries! :-) More specifically, if I have 60 customers from the USA, I have a USA record with a count of 59 and another USA record with a count of 1. 

       

      I'm guessing that this has something to do with the fact that I'm replacing a field (Countries::fkCountry) that is used to link the Countries database to the Customer database.

       

      I have tried flushing the cache when I create a new Countries records, but that does not help.

       

      Any and all ideas are appreciated.