FileMaker 15: Truncate Table

A seemingly small but important change in FileMaker 15 is the addition of the Truncate Table script step. This may seem easy to overlook, but the functionality is actually a very welcome change. If you have developed with SQL databases, this function will be familiar to you, but you may be interested in some details and differences with how it works in FileMaker.

In practical terms, Truncate Table works the same as deleting all records in a table. The difference is that when you perform truncate a table, this happens in a single operation, regardless of dependencies. So even if your table contains millions of records, they are all marked for deallocation and the index is cleared. There is no dialog showing records deleted, this just happens.

Ordinarily, FileMaker will delete record in a very “safe” manner whereby it deletes each record individually and updates the record indexes accordingly. In the event that the process is interrupted, data integrity is maintained and the field indexes are always current.

If you are in a found set, it still works this way. However, with Truncate Table, all records are simply removed as noted above.

A Couple Points of Import

The same behavior is observed in either case:

If you are currently showing all records and you select “Delete All Records…” (see Figure 1), or
If your current found set equals the total record count and you select “Delete Found Records…” (see Figure 2)

Found set showing all records — Figure 1 – Showing all records.

Figure 2 – Found set equals total records

If there are no dependencies such as cascading deletes, FileMaker 15 appears to automatically truncate the table, since all records are being deleted in either case. I would call this an implicit truncate.

If you have ever needed to delete a lot of records, for whatever reason, then this is effectively a huge performance increase.

Another Point to Note

The Truncate Table script step will delete all records in a table regardless of your current found set.

So if your record indicator looks like Figure 3.

Record indicator showing partial, not all records — Figure 3 – Record indicator shows partial, not all records in the found set.

And you perform a script that uses “Truncate Table” without dialog, your record indicator will then look like what is shown in Figure 4.

Figure 4 – Record indicator shows no records in the found set.

IMPORTANT:

When you Truncate a table, schema dependencies are not taken into consideration. So if you have cascading deletes turned on, those will not occur. If you need that functionality, stick to deleting records as usual.
Truncate will not occur if there are any open records in that table, so make sure all records are committed before deploying this script step.

One More Note

This script step must be run in the same file in which the table physically resides. This is due to the operation being performed on the actual table itself, and not the table occurrence in the relationships.

So this is a very handy tool to have at your disposal. Any time you need to use a temporary table for any purpose, this is a huge time saver and you can quickly clear out records in an instant. For clearing out scratch records or temp tables, you no longer need to show a disconcerting dialog to users that displays records being deleted, not any more.

Another added benefit is that when you close the file, as with previous versions, FileMaker will automatically recover any used space that might have previously been used. When the file is next closed, you will see a difference in file size, especially if this concerned an arbitrarily large amount of records.

Just be sure to follow best practices, especially in served solutions where more than one user might be using the temp table.

Conclusions

Truncate Table functionality is a nice addition to the platform, but one that you might never explicitly need to use. My recommendation would be to script using “Delete All Records” in a found set of all records, because you implicitly get the Truncate behavior while not having to worry about schema dependencies. I like that this new script step is available to us, but will use it sparingly.