A FMT Preferred FileMaker Consultant - Developer
Log in
Hot & Trending on FMT

ExecuteSQL and Empty Values

Featured ExecuteSQL and Empty Values
Here is a little something that tripped me up recently.  In the attached database I have a collection of cheeses.  And 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 like the script below where I loop through the records (using GetNthRecord to avoid physically jumping from record to record) and checking if the value of the "iLikeIt" field is different than "Yes", building a list of cheeses as I go.
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)]
  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 )]
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, but 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.
Two ways of solving 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
Something to keep in mind when you are replacing traditional "not equal" comparison approaches with their ExecuteSQL equivalents.
Here is the sample database: ExecuteSQL_emptyValues.fmp12.zip
Soliant Consulting

Soliant Consulting employs the largest FileMaker development team in the world. We have a deep bench of talent starting with our CEO, Bob Bowers: he's co-authored seven books, led the team that has written six editions of the Authorized Training Series for FileMaker, Inc., spoken at more than a dozen FileMaker Developer Conferences, and has taught literally thousands of students spanning two decades of leadership in the field. Certified Expertise Soliant's team is certified in every version of FileMaker Pro and are specialists in building high-performance databases, hybrid web-FileMaker applications, integrations with a range of technologies including SQL, and conversions from prior versions. We work with your solution as is or can build from scratch. Find out how we can take your FileMaker databases to the next level.

Website: www.soliantconsulting.com/filemaker