Getting Duplicate Records
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.