Solving the difficult problem of fuzzy matching using artificial intelligence
Starting with the release of Claris FileMaker 21, FileMaker users have two types of search available to them. There’s the standard “exact match” find, and now we have a Semantic Find, powered by the use of Large Language Models and Artificial Intelligence. The standard find in FileMaker is what most people would find familiar – enter a search term and FileMaker returns records that exactly match the query. Semantic Find operates differently, in that it returns records that match the meaning of the query. The use case most commonly associated with it is searching large text fields for semantic meaning. However, semantic search can also be used to solve a very specific type of database search challenge – Entity Matching, or “fuzzy search”. This type of search problem is common (and difficult) enough that using Semantic Find to solve it can bring real benefits to FileMaker users. There are technical challenges and some tradeoffs, but the results I’ve had are compelling. In this article, I’ll explain a little bit about how FileMaker searches work, and show how FileMaker’s new Semantic Find tools can help solve the Entity Matching problem.
Traditional “Exact Match” Find
An ordinary FileMaker search looks for an exact match, usually with data that has been indexed. It’s the most common type of search, it’s very fast, and it’s useful for finding records that have very specific criteria:
- Find all records where FirstName = “Hannah” and LastName = “Beuler”
- Find all records where Date = “4/5/2025”
- Find all records where Size = “3.45cm” and Material = “Aluminum”
There are plenty of ways to drill down and refine this kind of search using the standard FileMaker tools (logical operators, numeric ranges, wildcards, etc), but at its core, this type of search basically relies on the data you’re looking for to be relatively clean and discrete. If FirstName does not equal exactly “Hannah” and LastName does not equal exactly “Beuler”, or if the date is not exactly “4/5/2025”, the record will not be found.
Semantic Find
Semantic Find, on the other hand, doesn’t rely on tidy and discrete data. This type of search can interpret the meaning of your data and return the most relevant matching records. The typical use case for semantic search is to tease out meaning from unstructured (often messy) text data:
- Find all the Sales Notes that refer to ‘cost being too high for customers’
- Find all Products in the catalog that are useful in warm weather
- Find non-fiction books about maritime history from Japan and China
Semantic Find will return records that contain data related to the meaning outlined in the search query. As opposed to an exact match, these results may vary in their contents, but will still be relevant to the request, since the semantic find interprets the meaning behind the query and matches it to the contents of the records being searched.
What is Entity Matching (and why is it difficult)?
While semantic find looks for relatedness in meaning, Entity Matching, sometimes known as “Fuzzy Matching”, refers to the task of determining if two entity descriptions refer to the same real-world entity. This type of search attempts to answer questions like:
- Is this new incoming data the same as data that’s already in the database?
- Do these two (or more) records actually refer to the same thing?
- Do we have any duplicates in our database?
While this type of search is easy if data is very clean and discrete, it becomes much more difficult whenever “messy data” is introduced to your data set. We often encounter messy data where, for example, two or more databases have been merged from disparate data sets, or when much of the data comes from an outside source (such as user submissions, data feeds, crowdsourcing, web scraping, mailing list imports, etc). Incoming data may contain abbreviations, nicknames, alternate spellings, value transpositions, or typos. And of course, good old human error contributes significantly to this as well.
Take the following:
Record 1 — Hanna C Bueler, born on 03/12/1990
Record 2 — Hannah Beuler, born on 12/03/1990
In this example, the first name and last name are both spelled slightly differently, one record is missing the middle initial, and the month and day have been switched in the birth date. For a human comparing these two records, it may be fairly easy to spot the errors and recognize this as a duplicate. But for a computer program, it’s not so simple.
The problem, then, is how to more accurately determine, programmatically, whether these two records represent the same person. FileMaker’s traditional deterministic find is ill-suited to solving this problem (without a significant amount of scripting), simply because of its very nature. It excels at finding an exact match, but we’re looking for something that is almost an exact match. However, using FileMaker’s semantic find features, and an AI model tuned especially for Semantic Similarity, we can find this match with a fairly high certainty that these two records refer to the same person. In this particular case, my tests with FileMaker’s Semantic Find and Google’s Gemini text embeddings were able to determine that these were 87% likely to represent the same person!
Entity Matching Use Cases
Entity Matching is required in a wide variety of data environments, but can frequently be found in healthcare, finance, CRM systems, and E-commerce, where deduplication of person, organization, or product data is essential. Any data used for identifying an entity that may vary slightly (alternate spellings, typos, abbreviations, etc.) can make it really hard to deduplicate your data. Common examples of the kind of data that can be “messy”:
Names
- Kristine vs. Christine
- Fernández vs. Fernandes
- Bob vs. Robert
- William vs. Willy vs. Bill
Addresses
- First Avenue vs. 1st Ave
- Southeast Main Street vs. SE Main
- Greenlake Road vs. Green Lake Rd
Dates of Birth
- 1995 vs. 1959
- 10/2/2003 vs. 2/10/2003
- 1/21/1980 vs. 1/12/1980
Organization Names
- First National Bank Corp.
- 1st National Bank
- F.N.B. Corporation
- First National
Product Descriptions
- Black Adhesive Tape, Roll – 2 in.
- Roll Tape – 2-inch Black
- Adhesive Roll Tape 2 in. blk.
- 2” Roll Tape – Black
Basically, entity matching is needed anywhere that messy data makes it more difficult to determine if two or more records are the same. So, how can FileMaker’s new semantic find features help us solve this problem?
How Semantic Find Can Help
Using the semantic search tools, messy data in a FileMaker system can be prepared for this kind of search, making it easier to identify when two records are likely to represent the same entity. This requires a one-time encoding of the data into “vectors” using AI, and the semantic find feature then uses these vector embeddings to perform the search. Once we’ve prepared our data to be searched in this way, we can do things like bring up a list of possible matching person records before entering a new person, even when the name is spelled slightly differently, avoiding duplicate entries. We can use the same approach for organization names, addresses, or when adding something like a new stock item (do we already have an item like this in stock, or should we add this as new one?). Semantic Find can be used to build a deduplication module that runs every evening, automatically merging newly-created duplicates if the match percentage is higher than a certain amount. Or we could create an alert for users when they are about to add a new record that may already exist – “This looks like a record you already entered. Are you sure you’d like to add this new one?”
Entity matching is a crucial process for keeping your data clean and accurate. Until now, it’s been a difficult problem to solve using FileMaker’s native tools. But Semantic Find gives us a new toolkit to easily bring this functionality within reach.