Log in
Hot & Trending on FMT

FileMaker Custom Function Parameters: Value or Reference? An Example.

Featured FileMaker Custom Function Parameters: Value or Reference? An Example.
Last week I blogged here about an interesting behavior that happens when you pass a field as a parameter to a custom function. It turns out that you have access to the field's value but also the field reference itself. How can we take advantage of that behavior? It allowed me to simplify some custom functions that use ExecuteSQL.
[yee_row ex_class=""][yee_column width="1/1" ex_class=""][yee_text_block css_animation="no" ex_class="" widget_padding="eyJwYWRkaW5nLXRvcCI6IjAiLCJwYWRkaW5nLXJpZ2h0IjoiMCIsInBhZGRpbmctYm90dG9tIjoiMCIsInBhZGRpbmctbGVmdCI6IjAifQ==" widget_margin="eyJtYXJnaW4tdG9wIjoiMCIsIm1hcmdpbi1yaWdodCI6IjAiLCJtYXJnaW4tYm90dG9tIjoiMzAiLCJtYXJnaW4tbGVmdCI6IjAifQ=="]

 

Here is how: in a sample file we have a list of last names and we know that there are duplicates in the list.  For any chosen name we want to find out how many other records there are.  As you can see in the screenshot below, there are 7 records for the name "Lloyd":
 
 
The record that we are on has ID 564 so we want to know what the IDs are for the other 6 records.
 
A SQL query like this will give us that result:
 
ExecuteSQL(

"SELECT id FROM myTable WHERE myField = ? and id <> ?" ;

"" ;

"" ;

myTable::myField ;

myTable::ID

)
 
The values of myField and ID for the current record will be inserted into that function call.  But this syntax has one big drawback: the names of the myField and ID fields, and the name of the Table Occurrence are hard coded into the SQL syntax.  If you ever decide to change the name of the TO or a name of one of the fields, then the ExecuteSQL function will fail.   To protect against that you can extract the TO and field name from a field reference.  So to make the SQL query work AND make it change-safe, we need to know the field's value and also its field reference.  And that is exactly what we can get from a custom function.  The custom function below takes two parameters: the field to search on and the ID field.  From each of those parameters the custom function can extract both the value and the field's reference.  For the field named "myField", the value is "Lloyd" and the field reference is "myTable::myField".  From the field reference it is easy to parse the TO name and the field name. 
 
 
 
We call this custom function from a script by just pointing it to the two fields of the current record:
 
 
 
When you run the script the dialog tells you how many other records match the "myField" value of the current record and what the IDs of those other records are:
 
 
Because the custom function knows the field name of the field you are giving it, it will work for any field in any table.  All thanks to the behavior of a field passed as parameter to a custom function gives us both the value and the reference.

 

[/yee_text_block][/yee_column][/yee_row]
Soliant Consulting

Soliant Consulting employs the largest FileMaker development team in the world. We have a deep bench of talent starting with our CEO, Bob Bowers: he's co-authored seven books, led the team that has written six editions of the Authorized Training Series for FileMaker, Inc., spoken at more than a dozen FileMaker Developer Conferences, and has taught literally thousands of students spanning two decades of leadership in the field. Certified Expertise Soliant's team is certified in every version of FileMaker Pro and are specialists in building high-performance databases, hybrid web-FileMaker applications, integrations with a range of technologies including SQL, and conversions from prior versions. We work with your solution as is or can build from scratch. Find out how we can take your FileMaker databases to the next level.

Website: www.soliantconsulting.com/filemaker