ExecuteSQL and Empty Values In FileMaker

When to Use ExecuteSQL

Something tripped me up in my FileMaker development work recently. In the attached database I have a collection of cheeses. I have marked three of them as cheeses I like: the “IlikeIt” field is set to “Yes.” The other ones I left blank.

List of Cheeses executesql

Now I wanted to gather the list of cheeses that I do not like.

A traditional approach would look like the script below. I’d loop through the records (using GetNthRecord to avoid physically jumping from record to record) and check if the value of the “iLikeIt” field is different than “Yes,” building a list of cheeses as I go.

Unfortunately, knowing that not all records have a value for the “iLikeIt” field, this approach will give me an accurate count.

# loop through the records to make a list:

Set Variable [$counter; Value:1]
Set Variable [$maxCounter; Value:Get(FoundCount)]
Loop
  Set Variable [$theCheese; Value:GetNthRecord ( cheeses::cheeseName ; $counter )]
  Set Variable [$doIlikeIt; Value:GetNthRecord ( cheeses::IlikeIt ; $counter )]
  If [$doIlikeIt <> "Yes"]
    Set Variable [$cheeses; Value:List( $cheeses ; $theCheese )]
  End If
  Set Variable [$counter; Value:$counter + 1]
  Exit Loop If [GetAsNumber( $counter ) > GetAsNumber( $maxCounter )]
End Loop
Set Variable [$cheeseCount; Value:ValueCount( $cheeses )]

ExecuteSQL: A Better Approach

Another approach with less code to write is to gather those cheese names through an ExecuteSQL function call.

Easy enough I thought; I will just use the below ExecuteSQL statement, basically asking for all cheeses where the “IlikeIt” field is different than “Yes,” mimicking the functionality of the traditional approach and taking into account that some records are not set to either “Yes” or “No.”

SELECT cheeseName
FROM cheeses
WHERE IlikeIt <> 'Yes'

To my surprise, the result came up empty. When I toggled a few cheeses to “No,” then those would show up. However, the records with no value in the “IlikeIt” field do not show up in the result.

Thinking that perhaps the “<>” comparison operator was at fault I switched to the equally acceptable “!=” operator but got the same result: only records with something in the field but different than “Yes” were included in the result.

In our minds, an empty value is different than “Yes,” so we’d expect empties to be included but they are not. Clearly, the database does not think like we do.

Building a Solution with ExecuteSQL

I have two ways to solve this:

  1. I can make sure that the “IlikeIt” field is auto-populated to “No” when the record is created so that there would always be a value. That works as long as the user does not have the option to remove the value at some point
  2. I can modify the SQL query to also check for empty values:
SELECT cheeseName
FROM cheeses
WHERE IlikeIt <> 'Yes' OR IlikeIt IS NULL

Obviously, since I cannot get my result with asking for just one thing, I would change the query to avoid the “not equal” comparison operator and make it slightly more readable by doing:

SELECT cheeseName
FROM cheeses
WHERE IlikeIt = 'No' OR IlikeIt IS NULL

Keep this in mind when you are replacing traditional “not equal” comparison approaches with their ExecuteSQL equivalents.

Want to run through this scenario yourself? Download my complimentary sample database here: ExecuteSQL_emptyValues.fmp12.zip

Custom Development Using ExecuteSQL

If you have other questions about using ExecuteSQL in FileMaker or are seeking ways to customize your solution further, please contact my team today. We’ve helped thousands of organizations drive innovation with their applications, and we’d love to determine if we’d be a good partner for you too.

10 thoughts on “ExecuteSQL and Empty Values In FileMaker”

  1. Thanks for sharing this interesting result. There are two points raised by this that I’d like to explore

    1) Why does this result occur in SQL in general?
    2) Why does this result occur in FileMaker’s implementation of SQL given that FileMaker itself doesn’t recognize the NULL concept?

    1) Why does this result occur in SQL in general?
    I ran the same test on a MySQL database to confirm that this wasn’t a result exclusive to the FileMaker implementation of SQL. The results were the same, provided that the ILikeIt column was set to NULL. I think the best way to understand why this happens is to think of NULL as equal to “unknown”. If you do this you can’t say for sure that NULL (unknown) values don’t equal ‘Yes’, only that you don’t know whether they do or not. Therefore rather than ‘Yes’ != NULL returning True it can only return unknown, hence the lack of results.

    In the MySQL test, changing the value of ILikeIt to an empty string gives the expected result of showing cheeseNames where the value is empty. We can be sure that ‘Yes’ != ”

    2) Why does this result occur in FileMaker’s implementation of SQL given that FileMaker itself doesn’t recognize the NULL concept?
    FileMaker databases don’t have a concept of NULL, only empty. So it’s interesting to see that when FileMaker has to deal with SQL, it decides that its own empty values are in fact NULL values. Once you know that it does this you can work around it as you show in the example, but it does expose a weakness in FileMaker. How can it distinguish between a true NULL value and a declared empty string? As far as I can tell it can’t. So the only way I can think to get this is to manually create the distinction. For example, use a predetermined character to identify either true NULL or the empty string and then build corresponding custom functions to do Boolean tests. That would be a fairly hefty undertaking and seriously reduce the portability of a solution.

    I’d really like to see FileMaker implement the NULL concept. This would make it more compatible with SQL sources as well as traditional database designs methods and programming languages in general.

    PS I don’t think you like cheese as much as me, but I don’t know for sure because a lot of your values appear to be NULL rather than No! Mmmm, cheese.

  2. Hey Wim –

    I’ve run across this same issue and solved it the same way. Most FileMaker developers have not been exposed to NULL values and simply don’t realize that NULL in SQL means the lack of a value in a field, which isn’t quite the same as empty. Thanks for the explanation and the help file.

  3. Thanks Alec, Don for continuiing the conversation.  Most of the distinction between Null and Empty is indeed very blurry in the FileMaker world

  4. Hey Wim! A good tip for sure. When I’m using MS SQL or MySQL with web sites, this method is the same. I often need to look for NOT something OR ‘IS NULL’ in the same query just to be sure I have the correct rows returned. So, it’s not just an FM/eSQL solution. 🙂

  5. Travis Preister

    IΓÇÖm going to play devilΓÇÖs advocate. In your example, how do you distinguish between an untested cheese and one you donΓÇÖt like?

    If your database allows for untested cheeses (which is how IΓÇÖd interpret an empty IlikeIt field in your sample database) your expected behavior of the ΓÇ£SELECT cheeseName FROM cheeses WHERE IlikeIt <> YesΓÇ¥ command doesnΓÇÖt make sense. After all, we wouldnΓÇÖt want to find untested cheeses when looking for ones we donΓÇÖt like.

    If you truly want a binary result, consider changing the definition of the IlikeIt field so that an empty value equals no and anything else equals yes. Then you could use ExecuteSQL to find cheeses you like with this:

    ΓÇ£SELECT cheeseName FROM cheeses WHERE IlikeIt IS NOT NULLΓÇ¥

    And you could also remove the ‘NOT’ from the above command to find unliked cheeses.

  6. Hi Travis,

    Thanks for the feedback.  For the purpose of demo I wanted to highlight the difference between a FM find and the ExecuteSQL() query that most FM devs would write to do the equivalent.  The assumption here was that unless there is a "Yes", it is a cheese that I don't like.

    In a real solution I would probably have set the IlikeIt field's definition to "No" as the default value, unless of course the concept of finding untested cheeses was important then it would stay blank to allow finding the untested ones and "yes" and "no" would distinguish between cheese I like and don't like.  In that scenario, your code would work elegantly.

     

  7. I just ran into this exact issue and came up with the same solution 🙂 Thanks for confirming this. One thing I’m confused about at the end of your post though. What does this mean? I’m using the != operator and it’s working fine.

    “Obviously since I cannot get my result with asking for just one thing, I would change the query to avoid the “not equal” comparison operator and make it slightly more readable by doing:”

    1. What I meant by that part is that I prefer to read:
      WHERE IlikeIt = ‘No’
      rather than
      WHERE IlikeIt ‘Yes’

      It’s a subtle difference but infinitely more readable for most people, makes for less confusion during code review and code maintenance. There is something about the negative (not) that makes most people’s brains hesitate for a second.

  8. Ran into a similar situation, but I was processing a list with “” values. Using a case statement to switch the ” = ?” to “IS NULL” and it still wasn’t working (due to FM treating NULL variables as undefined). The simple workaround was to create a calculation field where the “” was replaced with a string.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top