Comparing fields from one different databases

[thelast1toknow]

I am looking for a way to automate the process of comparing two records. The pourpose is to compare the national no call list with my current database of customers. According to the law a business has to have been in contact with his customer in the last 18 months for there to be a "established" business relationship. I have customer records from the business I bought that are up to 11 years old. I have been able to compare records but my method is too slow. It involves moving from one record to another. The following script is what i have so far. Has anyone scripted the find feature in Filemaker Pro? Any suggestions? The no call list for one area code I am checking is over 600,000 records.

The Script:
set recordnumbx to 1
set federaldatabase to "503.fp5"
set ourdatabase to "List3.fp5"
set x to 666 -- number of records in our database
repeat x times
tell application "FileMaker Pro"
go to record recordnumbx of window ourdatabase
set phonenumb to cell "zc_phoneHome" as string
set char5 to character 5 of phonenumb
set char6 to character 6 of phonenumb
set char7 to character 7 of phonenumb
set char9 to character 9 of phonenumb
set char10 to character 10 of phonenumb
set char11 to character 11 of phonenumb
set char12 to character 12 of phonenumb
set PhoneNumber to {char5, char6, char7, char9, char10, char11, char12} as string
set numberofrecords to count of records of window federaldatabase
set recordnumb to 1
go to record recordnumb of window federaldatabase
repeat numberofrecords times
if cell "f2" of record recordnumb of window federaldatabase is equal to PhoneNumber then set cell "onlist" to "Yes"
if cell "f2" of record recordnumb of window federaldatabase is not equal to PhoneNumber then set recordnumb to recordnumb + 1
go to record recordnumb
end repeat
set recordnumbx to recordnumbx + 1
end tell
end repeat

thanks for the help

 

[Schlogg]

Get FMP to do your work
Define a relationship, Comp, from Local to Federal based on phone number.
In Local define OnList = case(Comp:hone, "Yes", "No&quot
You might need to tidy your phone numbers to eliminate spaces and cater for missing area codes.
(I might have the relationship around the wrong way. The above will flag the Local records that exist in Federal.)

Cheers,
Paul J.