Need creative solution in Table Lookup component.

[Fulltimer]

I have clients with a lot of customer records and they use my pop-up table lookup feature to add new medical clinics. The problem is with near duplicate medical providers. The reason it is tricky is because computers are very exacting in their comparative analysis.

Look at the below two providers:

Early Medical Care Inc.
1234 Hope St.
San Jose, Ca. 90022
Phone: 555-3556
----------------------------------------------
Early Medical Case Corp.
1234 Hope Street
San Jose, Calif. 90022
Phone: 555-3556 X14


I do not currently have a separate list of providers for them to pick from. I present them with UNIQUE providers we find within their files. Any typo or modification in the company/address information we have on hand will ASSUME that this different record is ACTUALLY a new and different location. And therefore it will display it because it thinks it may be important. I have a sophisticated SQL command call that filters out the duplicates.

Now a human can usually tell that each of the above are one and the same place, but a computer can not.

It has been discussed that this issue may need a more creative solution. I would like your opinions. In short the question is, how much of a record needs to be considered in order for it to do a comparative analysis. (i.e. company,doctor,address, phone, fax).

One of the problems is that some staff members tend to type info they feel they know, because they are fast typists etc. The better choice is to REQUIRE that a staff member selects from a list, this will guarantee an EXACT match being recorded. And if it's an EXACT match, it gets eliminated from duplicate views in the pop up list. It is an issue we would like to address.

I hope the above makes a little sense. I'm soliciting the views of A5 users that have already addressed this issue.

Thanking you in advance.

[GGandhi]

Re: Need creative solution in Table Lookup component.

they use my pop-up table lookup feature to add new.....
..
some staff members tend to type info they feel they know.....
..
views of A5 users that have already addressed this issue.....

I don't think I have liberty to answer to this thread since I am not a pro programmer. being that I am not so bright I would just do it.

this problem is not unique to a5 programmers/users.
this has been the story of any programmer's life.
programming to me is like building a super highway. no matter how well you design it, build it with all kinds of safeguards, signage and foolproof directions there will still be accidents or traffic jams. not because of the highway but because of the people using it, they just don't follow the rules.
computers never make mistakes it is the human who puts in the garbage gets the garbage out.
you can reduce the errors by limiting who enters the initial data or use soundex or any other system. but still it is going to be the responsibility of the person inputting the data to see if they are inputting similar data as a variant data. you can only guide them to a level but never will be able to force them to follow the right way. they will find a way to break the system.

[Steve Wood]

Re: Need creative solution in Table Lookup component.

Believe it or not I am actually working on a project using a specific product that does exactly this type of "fuzzy search/match". Specifically I am referring to the matching algorithm, not the rest of your question on how to make it more manageable going forward. Tom if you want to know more, send me a PM.

[Steve Workings]

Re: Need creative solution in Table Lookup component.

Yeah, it's a losing battle, but we all keep trying. One of our key customers insists on the right to add animal species to our master list. Yeah, the species' names which are a little tough to spell without a lot of care. And despite me presenting them with lists of potential duplicates before committing the new record, we still end up with a mess.

I use Alpha's Soundex() function for some pretty good results and checking. Now if only I can get the users to pay attention to the wonderful job it's doing for them.

[Fulltimer]

Re: Need creative solution in Table Lookup component.

Thanks for all the comments listed above. For a while there, I thought I may have been doing something wrong. Seems that a "real solution" is evasive and the problem is widespread. Since my original post and reading the replies, I've given some thought that I may try some version of the below.

I may add a new field to the medical providers table. I'll call it, "OK2DISPLAY". I'll then allow a "key" person in each office to scan an A5 Grid with the pertinent data from the table in Company Name order. This "key" person will then grant the MOST CORRECT entry a chance to enter 1 for OKAY2DISPLAY. All other entries will be branded with the number "0" as the default for that field. Then when I show the lookup table I can filter the list to only include the records marked "1".

The hope here is that the staff will be presented with a reasonably short yet "meaningful" list of medical providers. Obviously care will have to be taken for new entries that do not already have a "common" entry within the existing list.

[miket48]

Re: Need creative solution in Table Lookup component.

You came up with the suggestion I was going to put forward. It is a business process issue and not a programming issue. You need one user/owner for the customer table. In this case clinic table. Only what they say is correct for the drop down becomes the drop down. Since dBase II days the soundex() function has been a miracle for fuzzy searches.