A Deep Dive into FileMaker 2024’s new Semantic Search Functionality

I’m excited to see the new version of FileMaker delivering new AI features such as semantic search. I’ve been building AI systems in Python for some time now and it’s great that I’ll be able to bring some of this work to my favourite development platform. I presented a deep dive of the new features at the dotfmp conference in Berlin on June 6, just two days after the release of FileMaker 2024. I’ve based this post on my presentation.

This is the first in a series of posts doing a deep dive into the new semantic search features in FileMaker. Since so many people have asked about how to benefit from the new capabilities, I’m starting with the technical post as it pertains to FileMaker. This describes the “how”. Getting into the details of making it work. This will be followed by another article discussing the “why”. That will provide specific use-cases demonstrating how businesses can generate value from the functionality. Other articles will discuss questions such as how to improve search quality, choosing the right model, Retrieval Augmented Generation (RAG), and how to choose which chunking strategy to use.

I start with the simplest example to get started. This is followed by a deep dive into the script steps and the functions. I’ve spent a lot of time exploring the performance boundaries of the new functions and outline my findings here.

This post was adapted from my dotfmp presentation of June 6 2024, and as such may omit a few areas I discussed in the presentation. I’ve also taken the liberty of pasting some slides from the presentation as getting Medium to format them sensibly is beyond me as I’m writing this at midnight.

Start with the Simplest Possible Example

Our database is a simple database that will allow us to search FileMaker script steps. It looks like this:

We’ve got an id, name, description, a field for our embedding and a global field for our search term. The field for our embeddings is a container field.

To keep things simple we’ll use the OpenAI API. I’ll make the assumption that you’ve already got an account set up and have an API key. We’ll use the text-embedding-3-small-model for this example.

Claris have implemented this in a way so that you can set things up using only two script steps, then perform the search using only two more steps!

The simplest script that can populate this field with embeddings looks like this:

First we configure our AI account name, then using xname we use the Insert Embedding in Found Set script step.

After running that script we’re ready to search! Our search script also includes only two steps. Configure AI Account followed by Perform Semantic Search. If we run these scripts one after the other we wouldn’t actually need to use Configure AI Account, but that won’t always be the case so we’ll go ahead and do it.

Some things to notice about the search results. The default behaviour is to show 10 records. We can change this in our search options and we’ll get to that in a bit. The second thing is that the records are sorted in descending order of similarity to the search term. When you view the sort order you’ll notice that it’s sorted by a container field. So you’re unable to sort further in this way or recreate the sort.

That’s the quick tour of the basics. It’s really that simple to get started, but let’s get ready for a deep dive into the new script steps and functions.

The New Script Steps

The first thing you’ll notice is that there’s a new Artificial Intelligence section in Script Workspace. I’m not going to discuss the Configure Machine Learning Model step as it’s not new, but I’ll delve into considerable detail on the others.

So let’s get going…

Configure AI Account

As you can see from the screenshot there are two main options for Configure AI Account. Most people will use OpenAI as the model provider, but you can also use a Custom Model Provider.
In both cases you specify an Account Name which is used to refer to this configuration in scripts. You can have multiple AI Accounts configured at any one time. The accounts are scoped to the individual FileMaker file and persist until the file is closed. Unfortunately there is no way to query which are active. So far most of the early testers have dealt with this by configuring in each script where they’re used.

Configuring for OpenAI is straightforward. Select OpenAI as the model provider from the drop down menu and provide your OpenAI API key. If you’ve got several scripts using this it makes sense to add a custom function with the key or store it in a global variable on system startup.

Custom Endpoints are where things get interesting. And a bit complicated. You choose Custom from the drop down menu for model provider and you will be prompted to enter the endpoint for the provider. In my screenshot I’ve used a custom function to provide my endpoint. This avoids issues with typos and ensures I’ve got one place in my system to change it if necessary.

Claris supports embedding endpoints that are compatible with the OpenAI API. With one annoying exception. It expects the endpoint to be /embeddings rather than /v1/embeddings as OpenAI implements it. This is likely to change in a future revision.

The current options for a custom embedding provider are:

• Write your own embedding API in either Python or JavaScript
• Use the Claris Open Source LLM Server which comes with FileMaker Server
• LM Studio

Unfortunately Ollama is not supported as while it provides an OpenAI compatible API for completions, their Embedding API is not structurally OpenAI compatible and it won’t work as expected. This may change in the future, but is the case as of June 2024.

Claris and Soliant have both written extensively on configuring the Open Source LLM Server, so I’ll leave that to them.

As part of a product development effort I already had my own embedding endpoint in place, so revised it to be more compatible with the OpenAI API and it works fully with all FileMaker embedding script steps and functions.

The screenshot shows FileMaker attempting to connect to the /embeddings endpoint. I have now implemented both /v1/embeddings and /embeddings endpoints that call identical functionality. I could also have dealt with this by adding an nginx proxy in front of my API.

The remaining complication is that if you want to call Insert Embeddings for Found Set via Perform Script on Server your endpoint must run on port 8080. It appears that this can’t be changed when running via PSOS, though when running from FileMaker client it behaves as expected and correctly uses the port you specify in the endpoint configuration.

If you’re using LM Studio as your embedding server you’ll need to do two things to make it work.

1. set LM Studio to run over port 8080
2. make sure nothing else is running on port 8080
3. set the endpoint in Configure AI Model to http://{ServerIPAddress}:8080/v1/ NOT http://{ServerIPAddress}:8080/ as you would use for your own API or the Claris Open Source Server.

LMStudio is not 100% API compatible with OpenAI, but it’s pretty close and will work fine.

One other thing to bear in mind is that the endpoint here is not the same as you might use with Insert from URL. For that you use the complete endpoint such as http://{ServerIPAddress}:8080/v1/embeddings.

Oh, and the final gotcha is that the name you give to the AI Account is case sensitive!

So to summarise Configure AI Model

Or maybe it’s just simpler to use OpenAI?

Back to the script steps…

Insert Embedding

Acts on a single record and inserts a single embedding

Insert Embedding in Found Set

Does as it says and acts on the found set.

Insert Embedding ≠ Insert Embedding in Found Set!

They look the same and do basically the same thing, but there are some important differences. The Account Name and Embedding Model are the same in both cases, but otherwise the options are different and need to be understood.

The following table summarises the differences and similarities.

Insert in Found Set has Specify Field as both the source and destination whereas Insert has the ability to access the calculation engine for the source and you can assign the result to either a field or a variable. This provides more flexibility for deciding what text you want to embed as opposed to embedding the contents of a field in it’s entirety. I’ll delve more into this important distinction in a further blog post dealing with chunking strategy.

Insert Embedding in Found Set also has the option to replace field contents. If not selected it won’t update the field contents, but will otherwise replace them. You also have additional parameters for the API operation. These are specified as a JSON object.

Insert Embedding in Found Set is much faster than looping through a found set and using Insert Embedding on each record in turn.

This brings us on to an important question: Where do we store the embeddings?

The correct answer is in a Container field. The wrong answer is Text. Storing them in a Container is faster, it occupies less space, and it won’t destroy your database performance when someone accidentally switches on an index.

Embeddings in a text field have one valid use case. They can be used for a temporary storage place if integrating via Insert From URL. In this case you would store the embedding in a text field then set the container embedding field using the GetEmbeddingAsFile function.

If you really insist on using a text field for storing embeddings make sure you disable indexes in the Storage Options.

Do not store embeddings in the same data table as your primary data. Use a separate table for embeddings and relate it 1:1 to your data table. You can theoretically use external container storage, but it’s going to be slow. More on performance later. Consensus among those with early access is to store the name of the embedding model along with the embedding so you can easily separate them for searching in the event that you are using multiple models. They are incompatible and you cannot search across different models. So keeping them separate is important.

Different embedding models have varying numbers of dimensions and therefore occupy different amounts of space. Looking at the chart above, you can see that the embedded representation of a 65 character piece of text will produce a container occupying 24,584 characters. That length is the same no matter whether your embedding a 5 character or 8000 character piece of text. So the total size of the embeddings will grow in direct proportion to the number of records that you have embedded. As you can see, for 100,000 records you’re looking at over 1GB pretty much no matter which model you choose.

As for cost, well embeddings are cheap unless you’re doing huge quantities of them. But you can generate a surprising number of tokens quite quickly if you’ve got large volumes of data flowing through on a regular basis.

Embedding Performance is interesting.

You can get much better embedding search performance by adjusting some parameters.

The following test results were generated in the following way.

• Tested by generating 1000 embeddings at a time
• Varied batch size from 10 to 500
• Called via PSOS
• I used a mxbai-embed-large-v1 model of 1024 dimensions on local API

Perform script on server gave a substantial performance boost.

You can see that at the smallest batch size I tested it took just under 90 seconds to generate embeddings for 1,000 text records. FileMaker applies a default value of 20 as the batch size. This means it’s passing 20 chunks of text at a time to the API. At the default value it took just over 50 seconds to do the same embedding.

Once the batch size was increased to 75 the time to process 1,000 records was down to about 23 seconds and it fell to under 20 seconds once batch size was increased to 400.

We can double the speed of embedding by changing this setting!

FileMaker support a maximum batch size of 500. If you set the preference to over 500, your entry is ignored and it applies the default of 20.

But, there’s another interesting thing where we can optimise even more. If you wrote your own API then you’re in control of the batch size for Torch when running the embedding model. If you set the FileMaker batch size to be an even multiple of the Torch batch size, which I had set to 16, you get another performance boost because the batch sizes are aligned and there are no partial batches. The following chart demonstrates this.

Interestingly a batch size of 16 is faster than a batch size of 20 because of the alignment of the size.

So where do we find this setting? It’s a parameter in Insert Embedding in Found Set. The parameter name is MaxRecPerCall and I’ve found with my configuration that I’m getting the best performance to 496, which is close to the maximum value of 500 and is an even multiple of my models batch size of 16.

But back to the script steps…

Perform Semantic Find

There are two main options for Perform Semantic Find.

Query by Vector data

This allows you to set a variable or a global field with an embedding value then perform a find with that. This is useful when you want to find similar records, as you can simply take the embedding value for a record then search using that as the input.

Query by Natural Language

This takes a piece of text as input then behind the scenes generates an embedding for it and uses it as the input to the Semantic Find Operation.

Both have similar options.

You can search on the entire Record set or on a Found Set. If you’ve got a large database you will obtain better performance if you’re able to pre-select records. For example if you’re looking at a fashion database and know the person is interested in shoes rather than dresses, then doing a search for an indexed field of “shoes” then performing the embedding find on the Found Set will give you better performance. I’ll discuss performance in more detail in a moment.

The target field is the field containing your embedding. It is going to be a container field. Please! yes it will work as a text field but don’t!

By default the search will return 10 records. You can choose a different number to return. The returned records will be sorted in descending order of similarity. If your file is hosted, the search will happen server-side. Obviously your server will need to be Server 2024 for this to work.

You can also specify conditions around Cosine Similarity. In other words showing only those records that have greater than a (for example) 0.75 cosine similarity to the search term.

Your probably wondering about query performance?

I tested on a 940,000 record database running on a MacMini server with 16GB RAM and 8 cores. I used the mxbai-embed-large-v1 model with 1024 dimensions as my model.

My test script performed the find on the found set and started with a found set of 1,000 records increasing up to 940,000 records and recorded the search time for each.

Please bear in mind that search times vary by model, so your results may vary, but I think this gives some sense of how performance scales.

The good news is that search performance is pretty consistently linear with the number of records. An embedded search on 5,000 records is about 0.2 second and anything up to around 30,000 records is sub 1 second. At 100,000 records we’re looking at 2.5 seconds.

And it’s pretty linear up to 500,000 records at 11–12 seconds. This is where we see that pre-filtering where possible might be important. Also note that a smaller model with fewer dimensions would exhibit faster search.

Here’s where it gets interesting. At around 700,000 records there’s a huge step in the time taken. My theory is that this is where my server ran out of ram and started to swap. I’ll test further and look at the hardware the next time I run these tests.

A couple of further notes on performance. Search on a layout with the context of your embeddings. Searching through a relationship to an embedding table is painfully slow.

It is slower than using PostgreSQL with PGVector extensions. A search in my image embedding example database of 25,000 records takes around 5 seconds in FileMaker but around 0.6 seconds in PostgreSQL.

FileMaker Server does take advantage of hardware acceleration for these calculations where possible.

New Functions

FileMaker have provided a number of new functions, again under the Artificial Intelligence heading.

GetTokenCount(text) ≠ GetWordCount(text)

The GetTokenCount function can be used to get an estimate of embedding cost before running the embedding. While the Token Count is generally going to be close to the word count they’re subtly different things. I’ll go into more detail in another post, but for the moment just bear in mind that they’re not quite the same thing.

As you can see from the following some of the concepts translate into multiple tokens. Anti-virus for example could be interpreted three ways. Anti, virus, and — are all treated as separate tokens.

Cosine Similarity

This is the function used by FileMaker to determine how similar two embeddings are to one another.

When performing a semantic search you can store the embedding of the search term in a global field or variable, then use this function to display the similarity to the user.

It’s quite interesting to see how different embedding models will show different values for similarity. Open AI large produces slightly different search results and orderings than does Open AI small. The cosine similarity can be used to help evaluate which model might be best for you. Again, that’s a broad topic and I’ll have another post going into more detail on that.

Some key points here for using the Cosine Similarity function.

• Must be the same model
• Must be the same number of dimensions
• The data type of the fields should be the same
• Must be normalised vectors

Normalised vectors are a function of the model and embedding method. Open AI models produce normalised vectors by default. If you write your own API you must normalise the vector when applying the model or you will get unpredictable results.

Interpreting Cosine Similarity

• Cosine similarity scores range from -1 to 1
• 1 indicates that the two vectors are identical
• 0 means they are orthogonal (perpendicular)
• -1 means they are opposite vectors

GetEmbeddingAsFile

This function allows you to convert from a text representation of an embedding vector to a container version. This would normally be done as part of the process when integration with an external API via Insert from URL.

GetEmbeddingAsText

This one goes the other way, converting from an embedding in a container field to a text representation. Allowing you to integrate with an external API via Insert from URL.

GetTableDDL

Well this function can wait till next time as it doesn’t directly relate to Semantic Search

My Overall Thoughts on the New Features

I love them. Claris have done a superb job of building important new capabilities while implementing them in a way that feels like idiomatic FileMaker to me.

When I first saw Semantic Search by Natural Language and played with it I was hugely impressed. While the feature name mentions Semantic Search, it’s capable of so much more.

With a custom API endpoint I’ve already implemented text search in images within FileMaker. My prior implementation of that used PostgreSQL with pgVector as the database back-end. Yes, that’s faster, but the FileMaker implementation is much much simpler and more accessible to FileMaker developers. I’m currently prototyping similarity search for music clips.

If there’s a semantic search project you’re interested in, but you’re unsure where to start, feel free to contact me to discuss it. I’d love to help out.