Need to match Name in table 1 to Name in table 2 when names sound alike

[russgoeckner]

Looking for advice on how to match records in 2 different tables based on a name field that is similar. The bigger picture of what Im trying to achieve is this: I have a table named "Checkbook" and a table named "Vendors". I download data from the bank in CSV and import it into the Checkbook table. The downloaded data from the bank has more words and characters in it and often also includes a date value in the name field. The Vendors Name field is clean with 1 to 3 words as the vendor name.
For example, Checkbook NAME field has a value of "FedEx 034F7G00000". The Vendor NAME field has a value of "FedEx. Or Checkbook Name field= "StrangeThings 11/3" and Vendor Name field = "Strange things".

My goal is to use a Post operation to change the names in the Checkbook to match what the Vendor Name is.

[madtowng]

Re: Need to match Name in table 1 to Name in table 2 when names sound alike

Hi Russ,

I would think

 vendor name field $ checkbook name field

should do the trick.

[russgoeckner]

Re: Need to match Name in table 1 to Name in table 2 when names sound alike

Thanks for the reply Gregg. Could you please expand though, I don't understand how "&" would do this. I've tried LIKE, WORD_I, CONTSAINSi, etc..sometimes I get a couple of matches but nothing that is useable to match up thousands of records.

[madtowng]

Re: Need to match Name in table 1 to Name in table 2 when names sound alike

The & should be a $.
Here is a short example.

dim checkbookvendor as c
dim vendorfilevendor as c
checkbookvendor = "FedEx 034F7G00000"
vendorfilevendor="FedEx"
? vendorfilevendor $ checkbookvendor
= .T.

[madtowng]

Re: Need to match Name in table 1 to Name in table 2 when names sound alike

I can help more if I see your actual code.

[russgoeckner]

Re: Need to match Name in table 1 to Name in table 2 when names sound alike

Thanks again, No code really to share. Im just trying to link records in the Master table and Transaction table of a post operation. Then if the records match, I'll replace the name field of the Master with the Name field of the Transaction.

[madtowng]

Re: Need to match Name in table 1 to Name in table 2 when names sound alike

https://www.alphasoftware.com/docume...ion%20operator explains better than I do

[Stan Mathews]

Re: Need to match Name in table 1 to Name in table 2 when names sound alike

? soundex("StrangeThings 11/3")
= "S365"

? soundex("Strange Things")
= "S365"

? soundex( "FedEx 034F7G00000" )
= "F320"

? soundex("FedEx")
= "F320"

There are chances of getting an unintended match.

? soundex("Smith Optical")
= "S530"

? soundex("Smythe Originals")
= "S530"

[Al Buchholz]

Re: Need to match Name in table 1 to Name in table 2 when names sound alike

Russ

There are multiple ways to try to match names.

Since the logic to match is fuzzy, I would add a field to the the transaction table Master_Name and put the value in there rather than overwriting.

[Ted Giles]

Re: Need to match Name in table 1 to Name in table 2 when names sound alike

This works provided that the Vendor contains the name only, and the Check has the same characters with or without spaces. The pattern of characters must match at some point.
In the past, I have imported from 2 sources and created a calculated field which shows an absolute match, and no match. The no match required a manual update.

dim chk as c
dim ven as c
chk = "STRANGE things 1234"
ven = "strange things"
? strtran(ven," ","")
= "strangethings"
? strtran(chk," ","")
= "STRANGEthings1234"
? strtran(ven," ","") $ strtran(chk," ","")
= .T.

[Mike Wilson]

Re: Need to match Name in table 1 to Name in table 2 when names sound alike

Hi Russ,
If you are still in need, I have done this before. I found it in my case necessary to occupy the process so discrepancies can be rectified by the user as the processes is sequencing. I took what I had and made it to a Vendors table I had (attached). This sequence has the discrepancy stop for the user to rectify, but you could also collect the discrepancies and have them present at the conclusion instead of stopping the sequence with each found discrepancy. Hope this helps.