Working with Constants in FileMaker Pro

In most programming languages there are something called Constants which, as the name implies, are  constant values that never can change during runtime. If your not used to coding using constants I´m sure you feel a bit uncertain on why they are useful and I encourage you to keep on reading. A constant differs from a variable because a variable can change during the application runtime while a constant can not. A variable can also be set during runtime and then get unset, destroyed or released (whatever term you prefer) so that it not longer exist. Typically a constant will be declared when the application launch and live throughout the the entire runtime, unchanged. 

In object oriented languages a constant it not necessarily declared at application launch and it can be subject to object scope just as a variable can but luckily that is not something we need to bother with in FileMaker Pro.

So what is the whole point of setting a named value that can never change? One big advantage is readability and reliability in comparisons and when passing parameters. The value is readable like a string but can carry a numeric value safely used in comparisons or as parameters. 

An example is sort order. We can sort records ascending or descending using a script and a script parameter tells us which order to use. The most common practice is to send string parameter such as "asc" or "ascending" to the script which of course works just fine. However what we just did was to hardcode a value that possibly can change inside the script in the future. There is also a risk of a miss-typing sending a parameter value to the script the will not work. 

If you instead declare constants for sortorders such as SORT_ASC and SORT_DESC you will not need to pass them as strings and FileMaker will instantly tell you of you typed a letter incorrect. You will also be using the same constants for this action throughout you entire solution and consistency is a very important aspect when programmering!

The two constants SORT_ASC and SORT_DESC can represent simple values as 1 and 2.

Inside the script you will use the constants again. Most lightly you will have a IF-statement checking which sortorder was passed to the script. The value passed is just a simple 1 or 2 but because you want to sustain readability you will us your constants again. Look at the two screenshots below, the one on the left is the parameters passed to the script and the one on the right is the comparison inside the script. Please note that there are no quotes surrounding the parameters, the are NOT strings and thus NOT hardcoded!  

The advantage of coding with constants as shown above is that it is both readable, safer, adds consistency and theoretically is better in a performance point of view.

In the FileMaker community very few developers make use of the easy way given to us to declare constants. Simply create a custom function named to whatever your constants name should be. Then simply make it return a simple value, preferably a numeric value. In my example SORT_ASC return 1 and SORT_DESC returns 2.

For some reason constants are often written in capital case letters which is a good practice to use in FileMaker as well.

To give you one more example when working with constants is a good ide we can look at ExecuteSQL. When using ExecuteSQL we are required to specify a separator for records and a separator for fields. To use simple one character separators like linebreak and semicolon is not very safe since the probability that these characters exist inside the returned fields is high. Instead try to use a more complex separator is is unlikely to exist inside any field, something like #%@@%# .

This is a great time to use constants. Declare one constant as a record separator and one as row separator. 

Example:

COL_SEPARATOR = #%@@%#

ROW_SEPARATOR = #%@!@%#

This way things get much more readable and you can be confidant you will always use the same separators in every query. This also makes it easier to safely extract values from the query result.

So, don´t be afraid to use custom functions as constants. It can be a great tool when developing with FileMaker Pro! 

Writing recursive Custom Functions

Custom Functions has been around since versions 7 of FileMaker. For specific problems a custom function can be a real life saver. The most common area of use is string handling and manipulation. Before we hade GT, GTE, LT and LTE operators in relations one very common use was generating intervalls of dates or numbers for showing a range of records in portals or related valuelists.

Today, with current version FileMaker 12, the use of custom functions hade changed somewhat. I would say that the most common use today is data gathering or parsing using either ExecuteSQL or getNthRecord. The need to make subqueries for data without bothering about the current context seem bigger then ever.

In many cases one or more aspects of a task for a Custom Function is dynamic. For example a list of values can be sent as parameter and your functions task is to sort the values or perhaps filter unique values. In this case you more or less must apply recursion to your Custom Functions.

The method for recursion available to us is called tail recursion. As the name implies it means calling the function from within itself, usually in the end of the calculation (at its tail). It is not very hard to write a recursive custom function. The one important thing one must keep in mind is some sort of controller that keeps track of when the Custom Function is done executing. If we just keep calling the function from within itself it will continue to run forever, or rather until the callstack is full.
A controller can be anything from a numeric counter that should reach a predetermined value, to a list of values that should be parsed and one value removed until the list is empty. Anything we can catch using a If-statement can act as a controller.

The very simplest recursive custom function can look like this

There is one parameter to this function and that is the counter that keeps track of how many times the function shall call itself. In this case the If-statement will be true when the counter is greater then 10. When that happens it will return the value of the counter, else it will call itself with the value of counter plus 1 as parameter.
If you call this function in a calculation and set the counter parameter to 1 the result will be 11 because the function will call itself 10 times. 1 + 10 equals eleven.

Now to what I really wanted to blog about. The method above works just fine but there is, according to me, a better way to pass values between the recursive calls. Instead of using parameters I prefer to store my values in two-dollar-variables (persistant variables). That does not make things much more complex but it do require that we use the Let-function (or Let-statement if you prefer to call it that). The Let-statement allows us to set and unset variables with three types of scope.

• Local variable - Only accessible inside the current Let-statement
• One-dollar-variable - Accessible in the current script during runtime, not really useful in this case
• Two-dollar-variable - Accessible in the entire database until we unset it

The benefit of using two-dollar-variable in this case is that we only need to define parameters that the developer must worry about setting. If we want to pass more then one value between recursions the function is in risk of being difficult to use because some parameters are required and some are just data carriers between recursions. A good rule of thumb is to mimic FileMaker Incs way of naming functions and parameters. Make the developers that will use you function feel right at home.

FileMaker uses upper camel case for function names  and lower camel case for parameters.

Example (notice the lower case in the first char in lower camel case)

• UpperCamelCase
• lowerCamelCase

FileMaker does not use underscore or other special characters so neither should you.

Of course you can argue that you can reuse a parameter by concatenating strings and then divide them again in the following function call but I would not recommend this since it tends to make your calculation harder to read and force it to do a lot of unnecessary work.

Now to the fun part, how to store the values in two-dollar-variables. We need two let-statements for this. One that contains the entire calculation and one that simply clears the two-dollar-variables when done. First, here is a screenshot of a recursive custom function that filters out unique values from a list of values.

Lets break that down into parts


// Initiate the Let statement
Let([

// Set a two-dollar-var that acts as value counter, when this 
// is equal or greater to the amount of values in the list
// the function will no longer call itself but instead return
// a list of unique values. On the first call this value will
// not be set so we make sure it is set to 1
$$counter = If( $$counter < 1; 1; $$counter + 1 );


// Set a two-dollar-var that stores the current value that we
// are testning. We use the counter to get the next value from 
// the list
$$currentValue = GetValue(ListOfValues; $$counter);

// Set a two-dollar-var that holds the list of values to 
// return when everything is done. This list holds the 
// unique values found and is added to when a new value is
// encountered. The If-statement checks if the value already
// exist in the returnstack
$$returnStack = $$returnStack & 

    If( PatternCount( "¶" & $$returnStack & "¶"; "¶" & $$currentValue & "¶" ) = 0 and Length( $$currentValue ) > 0; 

        // If true
        $$currentValue & "¶"; 

        // If false
        "" 
    );

// Set a local variable to hold the list of values to return
// in case the current recursion is the last. We do not need to 
// worry about clearing this var, that is done automatic. We 
// need to store this locally so that we can clear $$returnStack 
// if needed  
return = $$returnStack

// End the declaration part of the Let-statement 
];

// Check if the value of counter is greater then the count
// of values passed to the function as parameter listOfvalues when 
// calling in. If the value of counter is less we call the function
// again to make one more recursion. If the value of counter is 
// greater then we are done looping and shall return values. 
// We return values using a Let-statement to clear two-dollar-vars 
If( ValueCount( ListOfValues ) > $$counter; 
    
    // If true
    FilterUniqeValues( ListOfValues );
    
    // If false
    Let([
        // Clear two-dollar-vars because we are done looping
        $$counter = "";
        $$currentValue = "";
        $$returnStack = ""
    ]; 
        // Return the list of unique values 
        Left( return; Length( return ) - 1 )))
)



Of course this is a bit excessive commenting even for me but I hope it clarifies what the calculation does. The point is that the function carries three values ($$counter, $$currentValue and $$returnstack) as two-dollar-variables between recursions and because the are two-dollar-variables we do not need to pass the values as function parameters between recursions. The syntax is very simple to read, executes quickly and the developer using the function only needs to see the one parameter that he actually is using.

When we return values the Let-statement on the end clears the two-dollar-variables and thus they are gone the second the function is done looping.

There is however one safety factor to think about. Since two-dollar-variables are scoped to the entire database is is wise to user fairly long variable names. This is to ensure that no existing two-dollar-variables gets replaced by the ones used by the function. As example the name $$counter is a very poor choice. It is likely that the very same name could be used in a script where this function is used and in that case the name would colide.

In my working version of the function above I use the following names to make sure that wont happen

• $$counter is called $$RQCF__FilterUniqeValues_Counter
• $$currentValue is called $$RQCF__FilterUniqeValues_CurrentValue 
• $$returnStack is called $$RQCF__FilterUniqeValues_ReturnStack 

making the complete function look like this

The longer names on the two-dollar-variables make the syntax a little bit harder to read but they also make sure we won´t run into name conflicts later on.

I hope this post helped you realize the benefits of storing values between recursions in two-dollar-variables instead of complex parameter handling. Have a nice day =)

Dynamic field names when using ExecuteSQL in FileMaker

One of the coolest new features in FileMaker 12 is the ExecuteSQL calculation function. There are several plug-in that makes FileMakers entire SQL-engine available to the developer but now there is also a native FileMaker function that makes Select-queries possible.
This post will not try to explain how to use ExecuteSQL but it will show one way to solve a common problem occurring when hardcoding names for tables or fields within a query.

The syntax for ExecuteSQL is as follows

ExecuteSQL ( query ; field separator ; row separator { ; arguments… } )

The Query itself is defined as a string passed to ExecuteSQL as the first parameter. A simple query can look like this

SELECT p.Givenname, p.Surname FROM Person p WHERE p.person_id = 1

Since the query is a string (pure text) it will not automatically update changes in fieldnames and tablenames and thus it will stop working if a name is changed. For example if someone changed the name of the field Givenname to Firstname in the Define Database dialog, the query above would instantly stop working. This can cause very serious issues that can take a long time to find and fix.

The solution is very simple but important to implement from the start. Create a new Custom Function and name it GetFieldNameSQL.

Give it one parameter that is named Field.

The calculation syntax for the function is

Let([

f = GetFieldName(Field);
l = Length(f);
p = Position( f; "::"; 0; 1) + 1

];

"\"" & Right(f; l-p) & "\""

)

What this does is to get the name of the field passed as parameter and stripping it of it´s tablename and then returning it with escaped quotes. This have two advantages.

Firstly it makes the fieldname in your query dynamic, meaning that it allows you to change fieldnames in your database without breaking the query.

Secondly it allows you to use reserved words as fieldname and even use fieldnames with spaces in it or special characters like the swedish characters å, ä and ö (I´m from sweden so that seems like a good example). You can do this without worrying about escaping characters or quotations.

When using the custom function in a query it looks like this.

"SELECT p."& GetFieldNameSQL( Person::Givenname ) &", p."& GetFieldNameSQL( Person::Surname ) &" FROM Person p WHERE p.person_id = 1"

The entire ExecuteSQL could look like this

ExecuteSQL ( "SELECT p."& GetFieldNameSQL( Person::Givenname ) &", p."& GetFieldNameSQL( Person::Surname ) &" FROM Person p WHERE p.person_id = ?"; "; "; "¶"; 1)

I strongly encourage you to use this or a simliar method to make fieldnames and even tablenames dynamic when working with ExecuteSQL in FileMaker. I am confident it will save you from tedious error searching later on.