Using SQL Statements in Scripts on FileMaker Data, Updated | Blog | CampSoftware

Using SQL Statements in Scripts on FileMaker Data, Updated

usingsqlstatements

Have you ever had the need to find data in your database or update fields without a Layout or even a Table Occurrence? It might not seem like a big deal, but this can really reduce the need for creating new windows to collect data when all you'd really like to do is perform a window-less query or set field!

There are a few FileMaker plugins that add the ability to perform SQL Select statements in FileMaker on FileMaker data. It sounds odd, but this gives you the power to retrieve data from your FileMaker databases without going to a layout, entering find mode, performing a find, and looping thru the records. VERY powerful. These examples use the myFMButler DoSQL FileMaker Extension. The examples in the download show much more of what you can do with SQL Statements.

Download myFMButler DoSQL: http://www.fmbutler.com/index.lasso?p=425

Here's an example on how to find the contacts for the organization "CampSoftware" using the let statement to make the calling of the function easier to read:

Let ( [ $select = "SELECT FirstName, LastName FROM Contacts WHERE Company = 'CampSoftware' ORDER BY LastName, FirstName" ; $query = mFMb_DoSQL ( $select ) ; $error = mFMb_DoSQL_LastErrNum ] ; If ( $error = 0 ; $query ; $error ) )

Thanks to the help of Ernest Koe and Andy Gaunt , we can also query dates:

$select = "SELECT Title, Desc FROM Events WHERE ( EventDate BETWEEN date '2008-01-01' AND date '2009-01-01' ) ORDER BY EventDate" ;

The next step is to make the queries safe for Table and Field name changes. We created two custom functions to return the Field and Table names:

// 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 ) ] ; theTableName )
// FieldName ( 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 ) ] ; theFieldName )

With these custom functions we can now build our Let / Select statement so if the field names are changed, our let statement will be updated as well.

Let ( [ $select = "SELECT " & FieldName ( Contacts::FirstName Winking & ", " & FieldName ( Contacts::LastName ) & " FROM " & FieldTableName ( Contacts::_ID ) & " WHERE " & FieldName ( Contacts::Company ) & " = 'CampSoftware' ORDER BY " & FieldName ( Contacts::LastName Winking & ", " & FieldName ( Contacts::FirstName Winking & "" ; $query = mFMb_DoSQL ( $select ) ; $error = mFMb_DoSQL_LastErrNum ] ; If ( $error = 0 ; $query ; $error ) )

The next step is to update data using SQL. In the example below you can update the value of the FirstName field for a specific record. Alex Brophy, also with CampSoftware, added the idea of defining variables for the parts of the SQL Statement as variables to make the actual SQL Statement easier to read.

Let ( [ TableName = FieldTableName ( Contacts::_ID ) ; ChangeField = FieldName ( Contacts::FirstName ) ; ChangeValue = $NewFirstName ; QueryField = FieldName ( Contacts::_ID ) ; QueryValue = $ID ; $select = "UPDATE " & TableName & " SET " & ChangeField & "='" & ChangeValue & "' WHERE " & QueryField & "='" & QueryValue & "'" ; $query = mFMb_DoSQL ( $select ); $error = mFMb_DoSQL_LastErrNum ] ; If ( $error = 0 ; $query ; $error ) )

So as you can see you can collect or update field data as you would like without Layouts or Table Occurrences.