Automating the process of comparing database records

Scripting Forums AppleScript | Mac OS X
,
#1

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

#2

I would look into the option of using FileMaker’s built in scripting mechanism to handle this task (as much as possible) and then supplement it with AppleScript (if necessary). I think you’ll find that this approach will be much quicker and more efficient.

– Rob

#3

I can perform the find with applescript but when it doesnt find the item the script errors out. Is there a way to handle errors in general? Such as: on error do X else Y

#4 
try
	-- your code
on error errMsg number errNum
	-- your error handler
end try

– Rob

#5

Here is the code I used. Thanks for the help. Any suggestions on how to make this more efficient would be great.

set recordnumbx to 1
set ourdatabase to “List3.fp5” – My Database
set federaldatabase to “503.fp5” – Federal No Call List
set x to 999 – number of records in our database

repeat x times
tell application “FileMaker Pro”
try
set Var1 to “Yes”
go to record recordnumbx of window ourdatabase
set PhoneNumber to cell “checknumber” of record recordnumbx
show (every record of database federaldatabase whose cell “f2” is equal to PhoneNumber)
on error
set Var1 to “No”
end try
set cell “onlist” of record recordnumbx to Var1
set recordnumbx to recordnumbx + 1
end tell
end repeat

#6

Your script currently checks each record in “ourdatabase” against each record in “federaldatabse”. You could simply check the value from “ourdatabase” phone number cell against the entire “federaldatabase” at one time like this


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 Developer"
		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
		
		(* Omit this part
        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
*)
		-- And replace it with this part.

				try
			show (every record of database federaldatabase whose cell "f2" = PhoneNumber)
			set cell "onlist" of current record of database "your database name should be here" to "Yes"
		on error number -1728 -- An error of -1728 means the requested object was not found, in this case a cell "f2" = PhoneNumber
			-- you could have the script set cell "onlist" to no
		end try
				
		set recordnumbx to recordnumbx + 1
	end tell
end repeat

What we are doing here is asking the database to show any record whose cell “f2” is equal to the phone number. If it finds one then there is at least one match then you know not to call this number, if it finds no matches then the number is not on the list and it is okay to call. The specific record is not important, just whether or not the number is on the list. Another way to speed this up a bit is to take out any commands to go to a specific record. By doing this however, you will not be able to see FileMaker’s progress, just the spinning wheel.

Something I notice in your code that may cause problems is when you refer to the cells “zc_phonnumber”, “f2” and “onlist” you do not specify which record or which database these cells belong to. By default FileMaker will assume you mean record 1 of the front database. You should change them to look like this:


set phonenumb to cell "zc_phoneHome" of record recordnumbx of database "ourdatabase" as string

This last suggestion is not a serious issue, what you have works fine, but setting individual variables to the characters of the phone number you want is not necessary either. You can accomplish this task in two lines like this:


set phonenumb to cell "zc_phoneHome" as string
set PhoneNumber to ((characters 5 thru 7 of PhoneNumber) & (characters 9 thru 12 of PhoneNumber)) as string

What you have done is a very good start. I took the liberty of rewriting your code and cleaning it up a bit below. This does everything your code does but with fewer steps and fewer commands. I think you will notice a significant increase in speed.

Rob is right about using FileMaker’s Script steps, it is much faster but I have had problems with mixing FMP/AS script steps because FileMaker does not wait for the completion of an AppleScript before proceding to the next step.


set federaldatabase to "503.fp5"
set ourdatabase to "List3.fp5"

tell application "FileMaker Developer"
	set x to (count records of database ourdatabase)
	repeat with recordnumbx from 1 to x
		
		tell database ourdatabase
			set phonenumb to cell "zc_phoneHome" of record recordnumbx of database "ourdatabase" as string
			set PhoneNumber to ((characters 5 thru 7 of PhoneNumber) & (characters 9 thru 12 of PhoneNumber)) as string
		end tell
		
		try
			tell database federaldatabase
				show (every record of database federaldatabase whose cell "f2" = PhoneNumber)
			end tell
			set cell "onlist" of record recordnumbx of database ourdatabase to "Yes"
		on error number -1728 -- An error of -1728 means the requested object was not found, in this case a cell "f2" = PhoneNumber
			-- you could have the script set cell "onlist" to no
		end try
		
	end repeat
end tell

Scott Lewis
MacScripter Staff | unScripted

#7

You guys were posting quicker than I could get my suggestion up so a lot of what I just said was redundant of what you had alread posted.

You do still need to specify which record of which database to set and compare data to.

Hope I was at least a little helpful.

Scott Lewis
MacScripter Staff | unScripted