Custom Function Parameters: Value or Reference? An Example.
Wim Decorte, Sr. Architect
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.
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:
"SELECT id FROM myTable WHERE myField = ? and 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:
More…FileMaker Custom Function Parameters: Value or Reference? An Example..