I have run into a problem when performing a find command in FileMaker Pro using an AppleScript. I was wondering if anyone knows of a better way performing the find.
The database consists of 128,000 records of just text based information. The database gets daily updates via a downloaded text file. I wrote an AppleScript to download the text file and perform the update. The problem is that it takes over ten seconds to perform the find. It appears that FileMaker does not use it’s indexing which slows the process dramatically. The daily updates range from 200 to 5000 changes per day and at 3000 changes it will take over 24 hours to update the database, which means it’s already time to do the next update.
When I perform a find like this it takes ten seconds to find the record:
tell application "FileMaker Pro"
show every record of database 1
show (every record whose cell "PartNo" = PartNo)
end tell
When I perform a find like this it finds the record immediately:
tell application "FileMaker Pro"
activate
tell application "System Events"
keystroke "f" using {command down}
keystroke tab
keystroke PartNo
keystroke return
end tell
end tell
I would perform the find using the second method but there is no way to detect an error when FileMaker can’t find the record. When my script can’t find the PartNo it creates a new record and fills the record with the new data. Does anyone know how to perform the find without such a delay?
tell application "FileMaker Pro"
tell window "Your Window Name"
go to
if (count of requests) >0 then
delete every request
end if
set newRequest to create new request
go to newRequest
set cell "PartNo" of newRequest to "==" & PartNo
find
end tell
tell document "Your Database Name"
if (count of records) is 0 then
display dialog "No records were found"
end if
end tell
end tell
Works like a killer on a local file but doesn’t work on a remote file residing on a FileMaker server.
I made a slight mod, but it dies on the line “go to newRequest” when ran using a remote file. When I use the document name in the “tell window” command the script dies at the “go to” command. Both giving me an error “object not found”.
set PartNo to "C00635"
tell application "FileMaker Pro"
tell window 1
go to
if (count of requests) > 0 then
delete every request
end if
set newRequest to create new request
go to newRequest
set cell "PartNo" of newRequest to PartNo
find
end tell
tell document 1
if (count of records) is 0 then
display dialog "No records were found"
end if
end tell
end tell
Be aware that many properties and objects that work locally simply do not work when running a database residing on FileMaker server, they literally are not supported near as I can tell. I haven’t sussed-out which ones do and do not work, but for example if you create a new record on a remote server, most of it’s properties (like it’s ID) are unavailable to AppleScript.
That drove me bonkers when I had a nice, fine-tuned AppleScript/FileMaker combo that worked locally and then it stopped working when on FileMaker Server. I ended-up having to re-write a bunch of the script to take advantage of certain FileMaker behaviors instead. In the above example, new records are always the last record created so to add data to the new record you have to direct FileMaker to “go to last record” then enter data, rather than “add data to record x.”
You may be experiencing this same issue. The way I finally figured it out was using ScriptDebugger’s very thorough debugging mode and querying properties every step of the way. I started noticing that some of the objects and properties I was taking for granted locally were not returning data from the same queries remotely. Real pain.
Ah here it is, my original experience with the “Object Not Found -1728” error and remote FileMaker databases. I knew I had a better, more detailed response to the issue. Read this entire thread, especially the last few posts:
I just dug through some stuff I’ve done in this area.
I use
set theNewRec to ID of (create new record at theDatabase)
with no issues on a remote server.
However, the database is already open. Maybe the answer is two step: one AS to open the DB and one to do the “work”. Or maybe just breaking up each process in its own tell block?
–What version of FileMaker client and FileMaker Server? (I ran into my problems using FMPro and FM Advanced 8.5 and Server 8).
–Does theNewRec actually work? I recall in my case I didn’t get any errors “loading” the reference, only when I tried to use the reference (only to find the reference loaded either blank or a bogus value).
I’m kinda curious since if you got it to work, why it didn’t in my original case (or the original poster from the thread I referenced). I also did a query of properties and did a trace on the reference request (at the time of that thread) and ScriptDebugger was showing nothing returned to AppleScript. Interesting little mystery I wish I had more time to explore…
Yeah I remember this thread, as a matter of fact I archived the script sample on my computer and used it in my current script for creating a new record.
I am using FileMaker Pro 8.5v1 Mac client with FileMaker Server 8.0v4 Mac.
As a friendly reminder here, I need a solution to a find command and not creating a new record. thanks
There are several reasons why I don’t use the ability to execute AppleScripts from inside FileMaker. One biggest reason is when an AppleScript is executed that affects multiple records the FileMaker screen gives no visual feedback. The screen freezes on the record that was on the screen when the script is started and lands whatever record its on when the script finishes. Second reason is long scripts execute a lot slower when executed this way.
But I tried your suggestion and tested the script from within a FileMaker script and it gives me the same error.
I wasn’t referring to running an Applescript from within the database, but create a script using built in Filemaker script functions, then call THAT script with an external Applescript.
I believe they are usually much faster than Applescript for doing finds.
I had to wait for my update to finish to try your suggestion.
Not sure what you were saying. First I thought you were saying to execute an external AppleScript file. I discovered the new FileMaker 8.5 no longer has that capability.
After reading your suggestion I think you’re saying to call an internal FileMaker script to do the find. The problem with that is I can’t pass an argument to the FileMaker script from AppleScript. Even if I could it leads me back to the second script example I had in my original post. I won’t be able to detect when an error occurs when find comand fails to find my request.
I am not sure this works on a remote database but have you tried the whose clause ?
Create a filemaker action to show all records. (show all records in applescript does not work) then try
tell application "FileMaker Pro Advanced"
tell database yourdata
do script "Show all records"
set x to (every record whose cell "PartNo" is equal to PartNo)
set y to the count x
if x is equal to 0 then
--your actions
end if
end tell
end tell