Delete all Records from many tables in one fell swoop

[JoniS]

Every time I open my FileMaker file, I run a script that imports records from SQL tables to FileMaker tables so I can use the SQL information in value lists. In other words, I make duplicate tables (a FileMaker table that is a copy of the SQL table) specifically so I can show the SQL data in a dropdown box (via the duplicate FileMaker table).

Unfortunately, this technique doesn't take into account when SQL records are deleted. Importing the SQL records upon opening the file will not get rid of records that no longer exist in the SQL table. So, when I delete an SQL record, it stays indefinitely in the duplicate FileMaker table that I use for value lists.

I need a way to delete all records from the FileMaker tables that I use for value lists, and THEN import the records from the SQL tables, so there is no question that the tables are identical.

There are a lot of tables that I need to do this on.

The only way I've found to accomplish this is to

set up a layout for each FileMaker table, then navigating to it with a script, and then deleting all records, and then going on to the next table's layout to delete all records.

Is there any easier way? Is there a way to script to delete all records from a table without navigating to a layout?

Thanks,

Joni

[LaRetta]

The only way I've found to accomplish this is to set up a layout for each FileMaker table, then navigating to it with a script, and then deleting all records, and then going on to the next table's layout to delete all records. Is there any easier way?

Not really. How many records per SQL table are you dealing with? It might be easier to use a SQL Query to import only DISTINCT records. It is really very quick. If you use SQL query, your script, including deleting original FM records, might look like:

Go to Layout [ layout with first set of FM records ]

Show All Records

Delete All Records

Import ODBC data query of:

SELECT DISTINCT valuelistField

FROM SQLtableName

Commit Records/Requests

Go to Layout [ next layout ] ... and so on.

If you import from table occurrence of your SQL table, you can't pull just unique field values but rather you'll be pulling ALL values. This doesn't seem like a big deal but 1) this newly imported data will not be indexed and FM must take the time to do so and 2) it will be slower to delete all the records again. SQL provides many methods of filtering the data which saves a lot of time. Having a table occurrence of it in the graph is only really necessary if you want to create calculations on the SQL side or use the TO in relationships (for displaying the data in FM layouts).

LaRetta :wink2:

[David Jondreau]

I know very little about SQL and Filemaker, so I can't speak to that part of your issue. But I think you could relate TOs of all your value list tables to one another via a Cartesian relationship and check the "Delete record..." option for each relationship. That should cause a cascading delete off all records when one tables records are deleted.

[LaRetta]

Hi DJ! I considered suggesting that but I believe cascading deletes are slower. It may only be true if there are a lot of dependent relationships. I've meant to test it but haven't gotten around to it. It would be very interesting if anyone has speed tested it. Maybe I'll try to do just that yet this weekend. Thanks for the nudge! :wink2:

[David Jondreau]

Cool. I'd love to see your results.

I would predict "daisy chaining" the TOs would be slower than a "star" set up.

[David Jondreau]

I just ran a quick test. 6 tables in a 15MB file.

Using the star formation, and deleting from the start, took 33 seconds.

Using the set of daisy chained TOs, took 30 seconds.

Going layout to layout with a Show All then Delete All took 29 seconds.

[LaRetta]

Thank you very much for testing this, DJ! I was off playing with my dawg. :crazy2:

[JoniS]

How many records per SQL table are you dealing with? It might be easier to use a SQL Query to import only DISTINCT records.

It varies from 15 to 1500 records per table. Unfortunately, all the tables already have distinct records. What would fix my problem is if FileMaker allowed me to create value lists with sql tables from my external data source. That's the only reason I'm doing all this. I've got tables of Categories, Makes, Models, Users, Status, etc that need to be available for dropdowns...

Deleting all records by going from layout to layout works, but it's painfully slow. Then when it's done, I re-import all the tables from sql. Is there an easier way to tell FileMaker that I just want an exact copy of the sql table, all the same records? I'm resorting to the delete and re-import because if a record is deleted from the sql table, it will stay in the FileMaker table forever...unless I delete and re-import all the records every time the file is opened...

[Ano Nimus]

Just thinking out loud here so I may miss something obvious, but you may not be able to base value lists on the values in the SQL table, but you *can* see them.

If you import them once, then somehow compare the values in the SQL table and FMP over a relationship and in the value lists show only the values that exist in both, a sort of conditional value list?

Going forward, you'd only need to import from the SQL source, (updating existing records) because any values that are too many in FMP would be filtered out.

[JoniS]

If you import them once, then somehow compare the values in the SQL table and FMP over a relationship and in the value lists show only the values that exist in both, a sort of conditional value list?

Bingo! That is exactly what I need! Hmmmm, a conditional value list that shows only values that exist in both tables... That should take care of any records that were deleted from the sql table...

Hmmm, I think this is possible. Now, I'm off to make the attempt.

Thank you!

[Joe Payne]

JoniS, I did the same thing as you, pulling info for value lists from SQL Server into FMP tables on startup and every time I save data on the SQL Server table. I could not do a conditional value list to remove the deleted records because I was already doing a conditional value list. I found that to remove the deleted records from FMP, you can set up a relationship between the table on the SQL Server and the import table in FMP by the unique key and set the record in FMP to delete automatically when a delete happens on the SQL Server. Of course, this will depend on your use situation, since we only delete the records thru FMP and the small group of users share the same file with Open Remote. Another solution we have used, in the record, we put a Active/Inactive flag instead of deleting the records. In FMP, you Active/Inactive the record on the SQL Server. Then, do a conditional value list with a self join to the SQL Server view using the Active flag field and a global calculated field which just returns 1 or "Active". Then your value lists show only the Active records in the drop downs, popups. This usually works pretty good because for the most part, the stuff that we show in our value lists doesn't change too much and does not depend on deleted records. If you have no other conditions on the value list, I'm sure the delete record will probably work.