possible duplicates

[martinwcole]

been so long since I've worked with 4.5 that its hard to think in the language version - so used to 5.0

if someone enters an address in a record
"10332 Black Jack Oaks Dr."

I am trying to come up with a script that would mark for showing in an array all customers that had at least two of the words in their address

some customers might have the following addresses

10458 Black Jack Oaks

[Tom Cone Jr]

RE: possible duplicates

Martin,

Perhaps there's a more elegant solution, but a brute force examination of all the records in the table will be complex to code and slow to run.

Any two?

Here are the search keys in 10332 Black Jack Oaks Dr that you'd have to process using CONTAINSI()

10332 Black
10332 Jack
10332 Oaks
10332 Dr
Black Jack
Black Oaks
Black Dr
Jack Oaks
Jack Dr

I'd be inclined to use separate fields for
Street_Number i.e. 10332
Street_Name i.e. Black Jack Oaks
Street_Type i.e. Ave, Dr, Rd, Blvd

Then you could search for matching street names.

This kind of thing might benefit from using a soundex search, to catch misspellings.

-- tom

[martinwcole]

RE: possible duplicates

Thanks Tom. The reason the client wanted this was because they are a manufacturer/self-finance company. And a client who had been repossessed, and written off, a year later had his sister reapply again.

We already track for the first word in the first and last names, the phone, dl#, and ss#, but this one got through, as the sister used her info and they had a new phone.

I haven't had a chance to talk to them again yet, but am hoping they will be satisfied with [the same mapsco# and the first two words in the address]

when i put the mapsco as the first part of the query filter string, i think it evaluates to that first and then to the address. anyway that is instantaneous

*************
have you ever used a keywords index on a field before. fiddling around, i made one for the address field

and
rec=cust.fetch_find("black")
returns the first record with "black" correctly

if they are not satisfied with my idea for mapsco+first two words i'll keep going on this

since 999 out of a thousand times this situation would not be true, you could eliminate the possibilty, quickly, but then if it was true, it would be more complicated to find them for an array

temp="1333 Black Oaks Drive"
n=0
for i=1 to w_count(temp)
rec=cust.fetch_find(word(temp,i)
if rec " 0
n=n+1
next
this would be fast using a keywords index on that field

since there is a keywords index, and also an index on mapsco, I might be able to pull off a reasonably quick scheme using "mapsco + address"