Searching for Records Outside of a file

[klauern]

I have a question regarding records. Currently, our database integrates a list of new users every year, and we have a list of records that we have to update periodically. The developer before me had made this database, without thinking of updates in the future to the list of users.

Currently, we import a file that has, among many other attributes, a transaction flag attribute: "A", "D", or "U". "A" means to Add the customer, "D" means to delete the customer, and "U" means update the customer information.

I have a list of records with all "A" on them, and many of these customers are already in the database, however, there is no other check to verify that you're not adding a duplicate. I am trying to figure out if it is possible to perform a find in the master Customers.fp5 file, to compare to the records in this Import List.

I would be performing this script from within the Imports.fp5 file, and would like to find records matching a key value in the Customers.fp5 file. I don't know if it is possible, and wouldn't know how to do it if it were. I would be most likely performing this script as a loop, so could I pull out of the script to perform a find in the other file, and then return to where I left off in the loop?

Any help would be appreciated.

Also, if anything is unclear, let me know.

-Nick

[-Queue-]

You could find all 'A' and 'U' records and treat them the same, importing using your key field as the match field in the import dialog, and 'add remaining records' to add only the new ones while updating the rest.

[klauern]

Another option, and I haven't checked the first option either, is that when I do import, I end up with many many duplicates. The guy who developed our database decided it was best to create CustomerIDs based on Indexed numbers. The first customer created was lucky #1, and the second #2, etc. Anyway, currently, #3818 customer IDs were created. When I import the customers, I end up with dual records, with different CustomerID's. I was wondering if there was a script to:

1) loop through all the records,

2) search for duplicate records.

3) compare the customerIDs, for the one with the lower Number.

4) set the newer record's ID to the lower number(since all the relationships are tied to this older number)

5) delete the older record(with the newer record's customer ID having been changed to maintain the relationships).

6) go to next double records.

There is a master key that can be searched so as to show whether there are two records of the same person, but what kind of script would be able to do this? Can Find Mode be entered, perform my changes, and then exited, so that I can just "Go To Next Record" and do it again?

I would prefer to be able to fix my problem from within the Imports.fp5 file as my first post suggested, but if I'd have to, I would also like to be able to fix it within the master Customers.fp5 file, which is what I'm proposing with this solution.

[Oldfogey]

Define a relationship from Imports to Customers using your Master Key.

In Imports, define a field:

'Dupe' calculation = (Relship::Master Key = Master Key)

Do a find on Dupe = 1. The found set will be the potential duplicate records. Use 'Show omitted' and import the new found set (the non-duplicates.)