Recently, I was working on a project using FileMaker, connected to a MySQL database, and I encountered a tricky problem. I needed to filter records based on a “not equal” condition, which is typically done using the operators “≠” or “<>” in FileMaker searches. For example, I wanted to find all records where a specific field was not equal to a particular value. However, every time I tried to use these operators, FileMaker returned an error message saying that the search criteria were invalid.

The issue stemmed from the fact that the field I was searching was stored in a MySQL database accessed through ODBC. It seemed that FileMaker’s “not equal” operators weren’t translating correctly into MySQL queries, causing the search to fail. I tried using “!=”, which is a common SQL operator, but this still didn’t work within FileMaker’s search interface.

After several attempts, I decided to take a different approach and solve the issue with a script. Instead of relying on FileMaker’s search criteria, I created a loop that went through each record, manually omitting those that matched the value I wanted to exclude.

Here’s the structure of the script I used:

1. Perform Find: First, I performed a general find to bring up all records without applying any exclusion criteria.
2. Loop through records: The script then entered a loop to process each record individually.
3. Check for matching value: For each record, I used an If condition to check if the value in the field was equal to the value I wanted to exclude.
4. Omit matching records: If the condition was met, the script executed the Omit Record step, which removed the record from the found set.
5. Continue until all records were checked: The loop continued until all records were processed.

Here is an example of how the script looked:

Esegui Trova [Tutti i record]
Loop
   If [Tabella::Campo = "Valore da escludere"]
       Ometti Record [Corrente]
   End If
   Vai al Record Successivo [Esci dopo l'ultimo]
End Loop

This script allowed me to find all the records initially, then loop through them, omitting the ones that matched the value I didn’t want. By using this scripted approach, I was able to bypass the issue with MySQL’s handling of the “not equal” operator in FileMaker’s built-in search functions.

In the end, this method worked perfectly, and I successfully filtered the records as needed. The loop and omit approach proved to be a reliable workaround when dealing with a MySQL database in FileMaker.