Today I have a custom function for FileMaker for you. It allows you to query any value in your databases by referring to the record by a field and it's value.
This can avoid complex relations if you just run a little query:
// Custom Function QueryValue
// 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
// if no error, release the recordset from memory
// 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.