DoSQL 101: FileMaker Data Types

Last week, we have seen how we can query data in FileMaker Pro 12, using SQL. This time we will focus on the data that returns from the ExecuteSQL function and the plug-in.

Text only?

FileMaker knows six different kinds of data types: text, number, date, time, timestamp and container. FileMaker’s native executeSQL will only return the data as text, even if the source is a number, date, time, timestamp or container. This can be very annoying.

Let's start with a small example
.

Let's predict we have a date field “myDate” in the table “myTable”, with only one record and the field contains the current date. The calculation:

ExecuteSQL ( "Select myDate from myTable" ; "" ; "" ) = Get(/CurrentDate)

will return a 0 (or False), although the field contains the current date.

The ExecuteSQL function will return the text “2012-06-26”, instead of the date “2012-06-26”. FileMaker will compare the text “2012-06-26”, with the date returned from the Get(CurrentDate) function. So the result is false.
You will need to convert the text string first to a real date.

myFMbutler DoSQL 2 supports the native FileMaker data types. When you use the new result set function or when hen the result is 1 field (column) and 1 record (row), or in other words, when it returns only one value, DoSQL will return the native data type.
So the calculation:

mFMb_DoSQL ( "Select myDate from myTable") = Get(CurrentDate)

will return a 1 (or true).

Download the example file

Leave a Reply

You must be logged in to post a comment.