What does that mean? We'll normally, to find a Contact's phone number, you would:

1. Go to the Contact Layout
2. Enter find Mode
3. Go to the Contact name fields and enter their name.
4. Perform the Find
5. Then finally, get the Contact Phone number.

With the new ExecuteSQL function you can do this in one step:

1. ExecuteSQL ( "SELECT PhoneNumber FROM Contacts WHERE Name ='Hal Gumbert'" )
   

This is perfect when you need a bit of data and don't really want to leave the layout that you are currently on. However, in the example above, if you rename the fields or table name the ExecuteSQL statement will stop working.

To fix that problem, you would pass in the field and table names in a function so FileMaker's field and table name references stay intact. We like this format to make the code easier to read. If you have any ideas to make this even better comment below.

"" &
ExecuteSQL (
"SELECT " &
FieldNameQuoted ( Contacts::PhoneNumber ) & " " &
"FROM " &
FieldTableNameQuoted ( Contacts::_ID ) & " " &
"WHERE " &
FieldNameQuoted ( Contacts::Name ) & "=?"
; "" ; "" ; "Hal Gumbert" ) &
""

There's a bit more in this than the simple ExecuteSQL statement above. First, we've changed the column and row delimiters to be XML like to make it easier to read the data back in case the data contains commas, returns or other text that might break parsing. The following example would return:

407-555-1212

That a bit excessive for this simple example, but if we returned back more data, it would be helpful:

HalGumbertCampSoftware407-555-1212

To extract the data from the XML like data, we can use another calculation. The following will return the first field from the first record. To get any other field from a record, you just have to change the RecordIndex and the FieldIndex.

Let ( [
RecordIndex = 1 ;
FieldIndex = 1 ;
Record = ParseData ( $XML ; "" ; "" ; RecordIndex ) ;
Field = ParseData ( Record ; "" ; "" ; FieldIndex )
] ;
Field
)

The calculations above use three Custom Functions, FieldNameQuoted, FieldTableNameQuoted, and ParseData, but they could be replaced by calculations. The idea is to get either the Field or Table name from a FileMaker reference name and also quote the name. We use keys that start with underscores which require quoting. Below are the custom functions:

// FieldNameQuoted ( theField )
//
// Returns the only the Field Name, dropping the text before and including the '::'. Contact::FirstName would return 'FirstName'
Let ( [
theFullName = GetFieldName ( theField ) ;
theColons = Position ( theFullName ; "::" ; 1 ; 1 ) ;
theFieldName = Middle ( theFullName ; theColons + 2 ; 99999 ) ;
theTableName = Left ( theFullName ; theColons - 1 )
] ;
Char ( 34 ) & theFieldName & Char ( 34 )
)





// FieldTableName ( theField )
//
// Returns the only the Table Name, dropping the text after and including the '::'. Contact::FirstName would return 'Contact'
Let ( [
theFullName = GetFieldName ( theField ) ;
theColons = Position ( theFullName ; "::" ; 1 ; 1 ) ;
theFieldName = Middle ( theFullName ; theColons + 2 ; 99999 ) ;
theTableName = Left ( theFullName ; theColons - 1 )
] ;
Char ( 34 ) & theTableName & Char ( 34 )
)





// ParseData ( theText; theStartTag; theEndTag; theOccurance)
//
// Extract the text between two strings.
//
// Parameters:
// theText = the text to parse
// theStartTag = the string that comes before the text to extract
// theEndTag = the string that comes after the text to extract
// theOccurance = the instance of the text to extract
//
// Return Value:
// the instance of text found in theText beween theStartTag and theEndTag based on theOccurance
//
Let ( [
theStartPos = Position ( theText ; theStartTag ; 1 ; theOccurance ) ;
theResult = Case (
// ------------------------------
// If theStartTag was not found, return an empty string.
theStartPos = 0 ; "" ;
// ------------------------------
// ------------------------------
// If theStartTag was found, get the string we are looking for.
theStartPos > 0 ;
Let ( [
theStartPos = theStartPos + Length ( theStartTag ) ;
theEndPos = Position ( theText ; theEndTag ; theStartPos ; 1 ) ;
theLengthToKeep = theEndPos - theStartPos;
theResult = Middle ( theText ; theStartPos ; theLengthToKeep )
] ;
theResult
)
// ------------------------------
) // End case
] ;
theResult
)