FileMaker add-on Found Set: Changing the Limit and Offset

The Claris FileMaker Add-ons, released just a few updates ago, are one of the components that make up the Open Platform release. They’re useful in solving problems for FileMaker developers. Calendars and Kanban boards are popping up all over the community. They’re not without their issues, however, but most of what’s there we can control. We have written lots about how to work with these add-ons, and I offer another explanation to edit add-ons: allowing them to display more or less records than the default found set. It’s rather easy, and I’ve included here a file in which you can copy a few script lines into your add-on (any add-on) scripting. So let’s look at the FileMaker add-on found set changes we can make.

Found-Set Based Add-ons

The Limit

The query used in the EF DAPI script step, in most cases contains a property that defines the limit. The query itself is generated from the add-on and sent back to FileMaker as part of the “Find” script parameter. In there, at around line 17 or 18 or 19, the $data variable gets the query. You can see that if you debug the script. Here’s the Activity Timeline’s query:

{
    "layouts":"ActivityTimelineSampleData",
    "limit":500,
    "query":[
       {
          "ActivityTimelineSampleData::PrimaryKey":"*"
       }
    ],
    "sort":[
       {
          "fieldName":"DateStarted",
          "sortOrder":"descend"
       }
    ]
 }

That little ol’ limit property defines the limit. In this case, the Activity Timeline will only return the first 500 records in the found set. In this case, the records are sorted in descending order and THEN the first 500 records are returned. WIthout the sort property, a query would simply return the first 500 records in the table.

Almost every add-on contains this limit property. It seems the Photo Gallery one does not; its query is missing this property. In this case, the query uses the built-in default of 100 as the limit.

The Limit is Good

There are good reasons to have a limit to the number of records that are returned. For one, the larger the found set–say 10000 or 32999 records, the slower the FileMaker script step will be. The step is doing a find basically, and we know all about Finds! As with the Data API functionality, this query has a built in limit of 100 records if one isn’t defined, to speed up the return.

Also, limits are built into APIs for speed and server resource conservation.

So there’s a limit. Okay. But we can work around that. I can think of two ways to do so: change the limit, or play with the offset property. Let’s explore each one.

Change the Limit

The limit of each add-on is defined (or assumed, as in the Photo Gallery), but that’s not a problem for us. The query in the $data variable is simply JSON and we all can work with JSON nowadays. So the simplest course of action is to UPDATE (or add) the limit number in the Find script.

All we’d have to do is use the FileMaker JSON functions to update this query. Immediately after the step where the $data variable is set, include a step to either add this property or update the existing property in the query:

Set variable [ $data ; value : JSONSetElement ($data ; "limit" ; <<yourNumberHere>> ; JSONNumber)]

And that’s it. The query will return the number of records we place in the <<yourNumberHere>> part of the JSONSetElement() function.

This gets me thinking: since I’m using a script step to update the limit, I could easily make the limit value a user-facing choice. I could put on the layout a field with a value list of, say “10, 25, 50, 100, 500, All” and use whatever value a user selects. Obviously if the user selects “All” my script will have to replace all with some incredibly large number: 9999999999 (as long as that’s larger than the entire possible found set).

The Offset

As with other API responses, the Execute FileMaker Data API script step query (that’s a long phrase) has an offset property. This option tells the script step which block of records to return. Here’s an example:

{
    "layouts":"ActivityTimelineSampleData",
    "limit":500,
    "offset": 501
 }

This query is saying: I want you to return 500 records, but start at record 501. That is: I want the second block (or page) of numbers.

The offset property is used all over in APIs, and it is referred to as pagination. Because of possible issues with server load in returning records, there’s a limit, but the offset gives us the chance to eventually get all the records.

In Real Life

Using this in real life is very practical and helpful. In a Photo Gallery add-on, showing records in the Photos table, it is not useful to return all the photos. No one can reasonably see all of them at once. Instead, we can build a paging mechanism that allows the user to page back and forth through blocks of photos. Also, with the offset value in place, we can return fewer records at a time. Rather than returning even 100 photos, our scripting can return 20 at a time and update the offset.

Updating the Offset

We can update the offset in the same way as we updated the limit:

Set variable [ $data ; value : JSONSetElement ($data ; "offset" ; <<yourNumberHere>> ; JSONNumber)]

We can work with this scripted offset-update in many ways. If we’re building a paging workflow, for example, a “Next” and “Previous” button on the layout above the gallery can provide the number for the offset. This offset number can be tracked in some manner and increased or decreased as necessary.

Here’s the product of about 30 minutes of playing with the limit and offset property. You can see how I used this in combination to update the number of and page of records being returned. I’m sure there’s some bugs here and some optimization to do, but this is the first attempt.

And here’s the extent to which I hacked the “Find” script (which you can do to any found-set add-on):

Onward

Each of the Claris FileMaker add-ons work directly out of the box, but eventually we’ll all need to customize them in some way. What I’ve described above is just another way to make minor updates to the scripting to make it your own. In this case we’re looking at the FileMaker add-on found set, and you can replicate what I did here for the Photo Gallery for your own implementation of any of the “found-set” add-ons. Feel free to download this sample file to see how I’m working with the offset and limit parameters.