Have you ever wondered which tables in your database are taking up storage space on your server? Perhaps you want to monitor how fast your table size is increasing in order to work out an archiving strategy for controlling storage space consumption.
This article will demonstrate a technique that creates a modular, portable script that can be copied and pasted into any FileMaker solution to measure table size. This can help you control your solution storage size.
How to Measure Table Size
Let’s start by talking about how we measure table size. This method for measurement can be compared to the way I measure my cat’s weight:
I hold my cat, measure our combined weight on the scale, put him down, and then measure the weight on the scale again. The difference between the two measurements is my cat’s weight.
When we measure table size, we save a compacted copy of the database, measure its size, truncate the table we want to measure, save another compacted copy, and measure the size again. Then the difference between the two measurements is roughly the size of the table.
How can we automate this?
So now we know how to measure the size of one table, but I don’t want to do this manually. I want to measure and record the size automatically. How do we do that? The simplest solution is to use a script to do these steps for me.
To save a compacted copy, we can use the script step “Save a Copy As” and specify the option to be “Compacted Copy”:
To truncate a table, we can use the Truncate table script step:
To measure the size of the compacted copy, we can use the Get File Size script step:
Combining them gives me an automated workflow that will save, measure, truncate, save, measure, and compare.
How can we do this dynamically?
Unfortunately, the Truncate Table script step does not allow us to specify a calculated table name as a parameter. This means we either have to create many copies of the block of script steps we just saw, one for each table, or we need to figure out a way to truncate tables dynamically. This brings me to step 3, making this process dynamic, modular, and portable.
As mentioned earlier, the constraint is that the Truncate Table script step does not support dynamic input. However, it does have the option to truncate the current table. If I can go to at least one layout per table, I can truncate them all.
So now the challenge is to figure out a way to go to one layout per table. We can easily get a list of all layouts in the system by using the design function LayoutNames, and the Go To Layout script step supports dynamic input, meaning you can go to a layout by name. Combining those two will allow us to cover all layouts in the system. If we assume that each table we want to measure has at least one layout, this method will achieve our goal.
Getting a List of all Base Table Names
Let’s optimize this just a little bit. In a FileMaker system, we all know it’s very common for one table to have many table occurrences based on it. We also know that each table occurrence can have many layouts based on it. We don’t need to perform a truncate table on all of them; we just need to do it once. In my script, I added a mechanism to register which tables have been truncated or measured. If a table has been measured already, we can skip it the next time we land on a layout based on it.
I’m using the ExecuteSQL function to query a FileMaker system table to get all base table names. This is kind of a hidden functionality of the ExecuteSQL function; it allows you to query the metadata of your database, like TO names, base table names, and field names.
Here is a link to the FileMaker SQL reference, which explains this in more detail. In the reference, go to the chapter named FileMaker system objects, and you’ll see how to use this.
Crossing Items Off After Measurement
Now that I have a list of base table names, I need a way to cross them off one by one. To do that, I need to know which base table a layout is based on.
This sounds more trivial than it is. We have the function Get (LayoutTableName). However, this function returns the table occurrence name instead of the base table name that the layout is based on.
To get the base table name, I’ll need to use the ExecuteSQL function again to query the system tables and find out which base table the table occurrence is based on.
With the ability to get the base table name of a layout, we can remove the corresponding base table name from the list every time we measure one. So the next time we run into a layout based on the same base table, we can skip it.
Putting everything together
Now let’s put everything together.
We start by dynamically getting a list of all base table names to help us track what to measure.
Then we use a loop to go through all layouts in the system. For each layout, if it’s based on a base table that hasn’t been measured, we will measure the file size, truncate that table, then measure again and calculate the truncated table size. And we cross off this base table from our list, so it doesn’t get measured again.
In the end, we save the log to the user’s desktop path as a CSV file, using FileMaker Data File script steps.
Everything in here is FileMaker native. No plugins or custom functions are used, which means it can be easily copied and pasted into any file.
Here’s demo file that contains the full script: