By now, you should have read Execute FM Data API Performance Test – Part 1, and I recommend it if you want to test this on your own or step through the scripts.

Continuing where we left off, let’s go back into the demo file and take a look at the scripts. You can see the three utility scripts for Create, Update, and Delete.

Housekeeping Items

I assume you understand how to create and parse JSON in this post. However, if you don’t, you can follow along and test with Script Debugger. Lastly, I won’t be covering the actual SendGrid API used, but you have access to the code, and the API reference is easy to work with. Below are references to the two relevant API calls:

  • Retrieve paged transactional templates.
  • Retrieve a specific transactional template version.

Not to sound like a broken record, but as mentioned in Part 1, there is a series I posted previously where you can learn how they work. You can find them using these links:

You can find the scripts to look at in the SendGrid folder. We’ll start with SendGrid – Get Templates and FM DataAPI – SendGrid – Get Templates. If you compare them side by side, they are identical up to line 46, as seen in the screenshot. Both scripts set up and process the API call and retrieve the data. From there, I parse out the templates and get a list of keys and a count, so I know how many records to loop through. I could have created another subscript for the beginning process, but I chose to keep them separate for this demo.

The difference starts at line 47 in the SendGrid – Get Templates script opens a card window to the SGT_processing layout, based on the SGT table, which is the main template. I add a pause so the window draws, but it’s not needed. The script then retrieves the individual template from the array using the key. I parse out the individual values from the object for the id, name, generation, and versionId. The script then searches for the record using the id. If there is an error (401), ithe script creates a new record; otherwise, the script updates the found record.

You might be wondering why this works since the script includes Truncate Table steps at the beginning. The answer is simple: I wanted to test how long it takes to fetch data from scratch separately from how long updates take. If you don’t want the records to be deleted, just disable those two lines.

Another thing to note is the versionId. SendGrid can have multiple versions of a template, but only one can be active. You’ll notice that I am parsing just the first object returned in the array. This is because only one version exists for each of the templates in the account I’m pulling from. If this is your first time using SendGrid, you should only have one version as well.

Once you create or update the SGT record, the script runs the subscript SendGrid – Get Template Version {json}. This script follows a similar pattern: setting up the API and performing the call. As with the templates, it performs a search by navigating to the STV_processing layout, based on the STV table for version records, using the versionId. If the script doesn’t find a record, it creates a new one before setting the fields.

Once the script completes, it exits and continues the loop. If more templates need to be created, it continues; otherwise, it exits. For housekeeping, I clear variables before moving to the next record. Once the script processes all templates, it closes the window and displays a dialog showing the time. The start time was set after the truncate step.

What’s different?

Now, let’s look at what’s different in FM DataAPI – SendGrid – Get Templates. The first thing to note is that this script opens the SES_processing layout, which serves two purposes:

  1. It provides a visual indication that records are being created and updated.
  2. It uses a global relationship to check if records exist.

At line 61, the script sets the template id into a global field. This relationship checks for a FileMaker Record ID, which is automatically set when records are created. If the id exists, it is stored in the recordId variable, and the global field is cleared. The id is only used for updating the record.

I initially attempted to use an SQL query to get the value, but performance was almost twice as slow. That’s why I now use a global relationship lookup instead.

At line 69, the field data is set, whether for creating a new record or updating an existing one. The script then checks for the record ID. If one does not exist, it creates a new record; otherwise, it updates the existing record.

Next, it creates the version record using the script FM DataAPI – SendGrid – Get Template Version {json}, which is nearly identical to the template script. The only difference is that it sets the global field with the id for the version. It also performs the same record existence check before proceeding. The script continues looping until it creates all templates and versions.

Why Show the Differences?

The main reason I’m showing the differences is that scripts didn’t use layout navigation at all. While both scripts use the same layouts, the Data API version remains within the SES_processing layout throughout the entire process. I liked that I didn’t have to change context, and I expected it to be faster.

Performance Comparison

Now, let’s compare the results.

Creating all 60 records from scratch:

  • The Normal method (as I’ll call it) completed in 21 seconds.
  • The FM Data API method took 30 seconds—9 seconds longer.

When I disabled the Truncate Table step and allowed updates instead:

  • The Normal method still took 21 seconds.
  • The FM Data API method increased to 35 seconds.

Currently, I am unable to test this on a server to see if the results differ, but locally, it seems that the Normal method performs better for higher record counts.

That said, the Execute FileMaker Data API approach is still a useful tool—it may not be ideal for this use case, but it might shine in another scenario. Keep an eye out for another post in the coming days, where I’ll demonstrate a case where the Data API method excels in both functionality and performance. Have good weekend!

    Want to know more about how you can make monumental shifts in your file’s performance? Contact us today by clicking the button below!

    Contact Us