Deleting all records in a FileMaker Pro 7 table

I am having no luck in deleteing all records from a table in FMPro 7. The code below will delete all records but the last one added (ie, the table always holds 1 record after execution):


set myTable to "myData"
tell application "FileMaker Pro"	
    delete (every record of table myTable)
end tell

Most curiously, if i leave to view another table’s layout and then return to the layout of 'MyData" (still showing the remaining record) and run the same code again, it will remove this record, leaving the table empty.

Why does this code not remove every record the first time?
Some other post referred to ‘table occurences’ (and suggested telling the table occurence) - is this relevant and what are they anyway?

This must be simple - any suggestions welcome.

Interesting: looks like a screen update/refresh problem if switching to another layout and back clears it. Maybe you should force the layout to refresh somehow.

I tried this:
I created the FM template “Inventory” and filled it with 4 records.

The interesting thing is that

tell application "FileMaker Pro"
	delete every record of table "Inventory"
end tell

won’t even get compiled (expected end of line…) although table 1 exists.

Then I tried this:

tell application "FileMaker Pro"
	delete every record of database "Inventory.fp7"
end tell

And that performed without a hitch. Every record gone!

Do you also have FileMaker 6? Is it running? Whenever an AppleScript with “table” in it won’t compile I suspect that AppleScript is still stuck on version 6.

Both the scripts you wrote should compile and work. The thing to remember is that unless you specify a particular database and/or table, the target will be the current database and/or table. If you include a specific database and/or table, that will be the one targeted, whether it’s the frontmost one or not. It’s different in 7 because of the multiple tables.

OK…

FM v6 is not running - it was completely replaced on my machine. So that is probably not it.

Chhanging the postfix (adding fp7) does nothing either.

…but i do believe there may be a db flush problem here because this is the errmsg returned when trying to run it:

“Filemaker Prog got an error. Data is being used by another user, script or transaction”

I tried rebooting and keeping only the running apps to FMPro and ScriptEditor and this is the behaviour i now get:

a - open layout to table MyTable (which is empty) and add 5 new records
b - toggle to ScriptEditor and run my script
c - errorMsg above returned so dipose of errorMsg
d - toggle to FMPro
e - goto another layout against another table in the db and display its records
f - go back to the layout of MyTable (the records i tried to delete are still there)
g - toggle to ScriptEditor and run my script again and…
h - … all the records completely dissapear.

On the basis that the simple code is ok, how can i force a flush or further find out whats happening?

[/b]

Are you leaving a cursor in a field in FileMaker? Version 7, because of the multiple windows, requires that you “Commit” a record before trying to modify the same somewhere else (and AppleScript is “somewhere else”).

Many things implicitly commit the records, clicking out of the field, changing records, etc… Scripts should usually explicitly commit, with the script step “Commit Record” (was “Exit Record” in 6). There is an excellent article about this in their big white paper “Migration Foundations,” on the FileMaker site. The chapter is “Record Ownership,” by Ilyse Kazar.

I tried this code to flush things out…


set MyTable to "TradeData"
tell application "FileMaker Pro"
	do script "FlushCacheToDisk"
	delay 5 -- let the flush take effect
	delete (every record of table MyTable)
end tell

where the FlushCacheToDisk FMP script is just a one liner calling the step ‘Flush Cache to Disk’. But no change in behaviour.

Seems that the data is locked and the lock is released by going to another layout.
Privilages for both the table and the records seem ok (just the defaults).

Any thoughts?

Fenton - you are spot on…

There was an active field and the the cursor was in a field. Deselecting this obviously clears the lock and so all records deleted as one would expect.

Thx for help!