TL;DR: Use the import function instead of a loop to create many records simultaneously.
In FileMaker Land, it happens that, on occasion, we have to create many records in one fell swoop. For example, when a new cohort of students enrolls at a school, we have to make a bunch of records in a student database. Or, a retailer would need to add new seasonal stock items to its inventory database over the course of the year.
New Record Race
So, let’s step through the retail store example. We are a small, scented candle shop that needs a process to create our seasonal inventory. We will have 1,000 new pumpkin spice candles during the fall months. Never mind, 1,000 records would still process pretty quickly. Say we’re a large candle company, the biggest candle store this side of the Mississippi. We have locations up and down the seaboard. Fall is approaching, our biggest season, and we have to add, as a default, 10,000 new pumpkin spice candles in our inventory system to sell. We will specify the following fields for each candle:
- Season: Fall
- Price: $15.99
- Year: 2022
- Scent: Pumpkin Spice
We might immediately attempt to tackle this work with a scripted loop. Essentially, we would create a loop with 10,000 iterations that would create a new record and set the appropriate fields. It would look something like this:
The problem with this approach is that it would take a very long time to process. Whenever we create a new record and set a field, FileMaker has to process the data for all the other clients within the solution. Each one of the script steps is a separate transaction that FileMaker has to manage. It makes the workflow very slow.
By way of demonstration, we created a special subscript to record the time it takes to run each script. Unfortunately, creating 10,000 records via a looping operation took 24 minutes! Therefore, using a loop function is not performative and will not scale.
A better way to do this would be to use the import function to simultaneously bring in all the records. FileMaker handles imports as one single transaction and thus doesn’t have to communicate the changes for all of the records to the other clients until the import is complete. If we’re doing this manually, we can create a template in Excel or a local FileMaker file. The entire process will run even faster when we change the data locally and then import it.
But we like to keep as much functionality as possible within the applications we design, so we do it differently. For our Big Candle solution, we created a new table called CandleInventoryTemplate. We removed FileMaker's standard default fields to keep the table clean and concise. So, we created four new global fields ("Season," "Price," "Scent," and "Year") for the values we want to set for the new records. We spent some time making enough records in this table to handle all of our batch record creation tasks.
Once we have everything set up, the general idea is to specify the values for our global fields for the new records. First, create a found set to the number of records you want to create within the new staging table. Next, export the found set to a CSV file, and then import that same file into the table where you want to store the records. The final script should look something like this:
We ran this script with the same subscript that captured how long it takes to process.
13 seconds!! We just went from 24 minutes to 13 seconds. That’s a significant performance improvement, and it is scalable.
When creating many records at one time in a FileMaker solution, avoid using loops. Instead, find a way to make them through an import process, and save tons of time.
Check out more FileMaker development tips and tricks, like how to move data from repeating fields and record and track GPS data using the FileMaker location function.
And sign up for our newsletter to keep up with the latest custom app development news and trends.
This article is also published on FileMakerProGurus.com.