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:
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.
