variable-scope duplicate checking

[MrTemp]

Hi!

The current project I'm working on is quite a doozy. Since I've found this is a tough nut to crack, I thought I'd share the problem so that maybe some of you can give suggestions (and help me out a bit) or just have fun mulling it over if you have the time!

Problem background:

The secretaries in my office maintain our contact database, which is constructed and edited in Filemaker 8. The database does not really contain any complex calculations, only fields for data entry (name, address, phone#, etc.). We've had this database for several years now, and it's grown hideously large (8000+ records) and difficult to maintain. Recently I was asked to implement a feature that would allow the secretaries to check if a certain contact already existed in the database prior to entry, to avoid redundant records.

I was asked to implement this feature as a static part of the database. The secretaries are...well...to put it gently, less than technologically proficient. Using "!" as a parameter in Find Mode and other such Filemaker-provided methods are a little too advanced for them to handle, unfortunately. So, essentially I have to create a algorithm/script to hold them by the hand through a duplicate check. The complications in scripting this arise from the fact that duplicates of a given set of information (for example: first name, last name, phone number) will not exactly match text-wise.

Example: The secretary wants to add a Jane Doe at 555-5555 to the database, however a record exists in the database for a Jane Bruno at 555-5555. These are actually the same person (Jane got married). All other information for Jane is the same, so obviously simply changing her name in the existing record would be desirable....if only the secretaries could find it. The secretaries will not know that Jane's information is already in the database (they don't want to search through 8000+ records for her), and will simply add her again, creating a redundant record, which is what we want to avoid. If the secretaries had some way to see all existing records that might match what they want to enter, and decide to add or no based on what they see, that would be a satisfactory solution.

Problem:

So, the goal to to create - from scratch - a duplicate checking method that can adjust the power of it's scope, based on criteria given by the user. To simplify the problem, let's restrict the criteria to first_name, last_name, and last_4_digits_of_phone_number, each with its own field in the database. The output of this solution will be a portal view of all records matching the criteria of the user; for example:

Portal of records matching only "last_name"

Portal of records matching "first_name & last_name" or

Portal of records matching "last_name & last_4_digits_of_phone_number".

For simplicity, let's say only the above 3 matching methods will be possible for the secretaries to use. The secretaries will enter the 3 pieces in information (names and phone) into 3 seperate fields in a seperate table (this 'matching' table does not preserve record information, it rewrites the existing record for each duplicate check), and based on which fields they want to match, a portal will come up with the records existing in the 'database' table that match the selected criteria. If the secretary rewrites information in the field, the portal will update to accomodate the new criteria, via a script or other method.

Possible Approaches:

Relating the 'database' table to the 'matching' table via a field in each table that merges the appropriate values is a must. I see no other way to produce the portal. However, things get messy because these merge fields must update in both tables when a new matching combination is selected. I've tried creating a script to loop through the database and update each merge field for new combinations, but that has proved to be prohibitively time-heavy. It is only barely tolerable for the secretaries, so this might be a solution only if no other is found.

==================

Wow that was a long first post. Anyway, thanks a bunch for any help, and have fun with this!

-MrTemp

[mr_vodka]

I would suggest that first you should create and run a report to fix your current duplicate contact information. Once the report is generated, it might take a little time to clean it up but at least it will be clean.

To prevent your end users from entering in duplicate info, I think this check would be better served when the user is entering in a new contact. You can use globals to temp store the data that is being entered. When they go to add the record, it could do its check for your duplicate criteria and if there already exists a certain record with for example the same name or telephone, you could prompt the user that there are records that already exists with that info and if they would like to view them.

If the user decides to proceed, then a popup window with the related possible matching duplicates can be shown and used to select a contact.

Just my :twocents:

[MrTemp]

Using a global field as a comparitor within the 'database' table would make the processing really quick. Thanks, I'll try that.

-Mrtemp

[David McQueen]

What you need is a filtered search portal.

Record key for each contact is a calculated compound concatenated key. The key for Jane at 555-5555 would show as:

j

ja

jan

jane

5

55

555

555-

555-5

555-55

555-555

555-5555

Your search key is a global field of type text. If the secretary enters j and then clicks out, all names starting with j will show in the portal. Entering ja would shorten the list and so on.

If you want the keys to reflect first name , last name, street etc, use prefexes in the calculated field so for Jane Doe on Smart Street, a partial key would be:

J

Ja

Jan

Jane

XXXD

XXXDo

XXXDoe

YYYS

YYYSm

.

.

.

HTH

Dave McQueen