If you have privileges to delete records, you might try using the Execute SQL script step (NOT the calculation function ExecuteSQL) and the SQL DELETE command (here's one page for how to use it):
Might be much faster than trying to do a Find.
I would concur with Mike.
FM is not going to handle more than a couple thousand records from an ESS table in the Found Set without seriously bogging down.
In addition, once you dump the records from the SQL database using a direct call, Filemaker is going to go haywire trying to reconcile what has happened with the records in the current table. So, if you go this route, do one of two things, don't go to a layout showing records from the table until AFTER you've done the delete. Or, even better, disconnect and reconnect to the SQL data source via a script procedure. This will clear Filemakers record cache and reset things, which will save Filemaker many gyrations trying to reconcile what just happened in the table behind it's back.
What is your ESS server ?
Making a table in it and import all fm records, then set the constraint to fk cause all invalid records
But I don't know how long it takes.
If 150,000 records "in the FM table", sending all keys to ESS server takes a while anyway.
Yes, I'm assuming you can easily quantify the "where" clause in a SQL Delete statement and push that back to the server.
If you are allowing the user to do various Find and Omit operations to get to a specific found set, you will have to push the record IDs to the SQL server. Even with the overhead of doing that, the operation in total will be much faster if you handle the deletes on the SQL server instead of in the context of Filemaker.
The OP described removing the SQL records that are missing a FK to the FileMaker table. Should be simple enough.
FM Execute SQL script step is limited as
SQL statements are limited to a maximum length of 256 K characters (512 KB).
So SQL like as
DELETE FROM esstable WHERE fk NOT IN ( list all fm pk )
should be over it if there are 150,000 pks.
DELETE FROM esstable WHERE fk IS NULL
The OP was to delete ESS records where there was no FileMaker foreign key, right?
I didn't think so since "put the primary key from the FM related table on the ESS layout." is not need if so.
I read that mean finding on FM related table::primary key with == (empty field)
SELECT * FROM esstable WHERE esstable.fk=fmtable.pk AND fmtable.pk IS NULL
(this is not executable SQL)
Hmn. Well, if that's the case (where the fk is populated but there's no matching record in the FM table), then you're right, it gets more complicated.
We might need more information to recommend anything more specific.
Yes, there's a limit to how much you can send through the SQL Script with each call.
I break my PKs up into chunks, and then loop calling a stored procedure to update a utility table with the data.
Then I run another stored proc to execute the actual table update/delete using a reference to the record. When dealing with large sets, it may be better to store the PKs in their own records in a utility table, or you may want to marke the actual records with a Flag field and then run the delete against the flag.
It's certainly less straightforward than it should be, but it's doable.
In FM13 with FM13 SERVER, I am now exporting a text field with the values and then importing them via a stored procedure. This is much easier to do now that we have the option to Execute Scripts on the Server.
Thanks, everyone for the hlep
In the end, I exported the FileMaker records to Excel put that file on the SQL server. Then I wrote a SQL statement to delete records in the SQL table with no match in the Excel spreadsheet. Since this was a one time need, it worked fine.