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.

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.