// Custom Function QueryValue

//

// Parameter:

// theFileName: the file name of the database. Empty for current.

// theFieldToQuery: the field name we want to query

// theFieldToMatch: The field to look for (primary field). Can be RowID for record ID.

// theValueToMatch: The value of the match field. Must match in data type

// 

 

Let ( [

// put quotes around field names unless it is RowID

theFieldToMatch = If(theFieldToMatch = "RowID"; theFieldToMatch; "\"" & theFieldToMatch & "\"");

theFieldToQuery = If(theFieldToQuery = "RowID"; theFieldToQuery; "\"" & theFieldToQuery & "\"");

 

// put quotes around the table name

theTable =  "\"" & theTable & "\"";

 

// Now run query

SQLRef = MBS( "FM.SQL.Execute"; theFilename; "SELECT " & theFieldToQuery & " FROM " & theTable & " WHERE " & theFieldToMatch & " = ?"; theValueToSearch);

 

// if no error, get first value

result = If(MBS("IsError"); SQLRef; MBS( "FM.SQL.Field"; SQLRef; 0; 0 ));

 

// if no error, release the recordset from memory

r = If(MBS("IsError"); ""; MBS( "FM.SQL.Release"; SQLRef ))

 

// return result

] ; result )

By using FM.SQL.Field, we get the field in it's original data type. So a container stays a container and a number stays a number. Converting to text would destroy them and cause problems. For example you can use this query to show a picture (container field) of a user if you know the user's login name for the query:

Set Field [Test::Picture; QueryValue(Get(FileName); "Picture"; "PersonID"; $PersonID) ] 

The advantage is that you don't need a relation and you can query it at any time anywhere.  

A special field name is RowID which allows you to query with using record ID. This is internal FileMaker number for a record which you query by Get(RecordID).

Works with current MBS Plugin and FileMaker with FileMaker 11 and newer.