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  & ", " & FieldName ( Contacts::LastName ) & " FROM " & FieldTableName ( Contacts::_ID ) & " WHERE " & FieldName ( Contacts::Company ) & " = 'CampSoftware' ORDER BY " & FieldName ( Contacts::LastName  & ", " & FieldName ( Contacts::FirstName  & "" ; $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.