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! 

Working with multiple script parameters

When writing scripts in FileMaker Pro I feel that it is important to make as many reusable scripts as possible. A reusable script is not just a script that can be called from, say a list- and detail-view of the same table occurrence. According to me a fully reusable script is a script that can be called from wherever and whenever. The developer calling the script should not have to worry about what layout or context the user is in, not even which mode the current window is set to (browse, find or browse). It is also important to make it easy to understand if the script calls any GUI elements and if there is a silent mode for delicate processes or scheduled server scripts and the WPE.

This blogpost is not a lesson in writing reusable scripts, it is a guide to one of the ways to work with multiple script parameters. Being able to declare more then one script parameter in a fast and secure way and also to fetch those parameter values inside the script is a key element of writing reusable scripts. Everything inside the script that is configurable should be possible to set using script parameters.

One of the simplest ways of passing many parameters to a script is by simply using a list of values (values separated with line break). Inside the script the values is separated using getValue(). This however fails as soon as you want to pass a value that could contain line breaks.

The more complex way to do it is by defining your own separator or sending values in a XML like format or simular.  That would fix the line break issue but not the issue of storing the values inside script variables and not without using quite a lot of lines in the beginning of the script.

I wanted be able to declare several script parameters, named and ready to use, on the single textline that is available for us for passing a parameter to a script. Also I wanted to avoid making my script super long only because the need to divide string values into script variables. After thinking about it for a while I came up with two custom functions. One takes care of binding a value to a parameter name and the other takes care of defining the named parameters as script variables.

There are many ways to do this or things simluar to this and what I will show you here is just my way of doing it. It is fast, reliable and I use is all the time. It handles declaring both one- and two-dollar-variables and even repeated variables. You don´t have to worry about forbidden characters or patterns since there are none. It even works when returning values from a finish script kind of like using multiple script results.

The two custom functions looks like this

RegisterParam ( name ; value )


Let([
n = Substitute( name; ["¶";""]; [" ";""];["$";""];["-";""];[".";""];["+";""];["-";""];["*";""];["/";""];["\\";""];["\"";""];["'";""];["!";""];["#";""];["€";""];["%";""];["&";""];["(";""];[")";""];["{";""];["}";""];[",";""];["<";""];[">";""];["?";""];["´";""];["`";""];["=";""];[",";""];["§";""];["°";""]);

$$_RegisterParamController = Case(
PatternCount( "¶"&$$_RegisterParamController&"¶"; "¶"&n&"¶")<1 and Length(Value)>0; $$_RegisterParamController & n&"¶";
PatternCount( "¶"&$$_RegisterParamController&"¶"; "¶"&n&"¶")>0 and Length(Value)<1; Let($$_RegisterParamController[ValueCount(Left($$_RegisterParamController; Position("¶"&$$_RegisterParamController&"¶"; "¶"&n&"¶"; 0; 1))) + 1] ="";Replace($$_RegisterParamController; Position("¶"&$$_RegisterParamController&"¶"; "¶"&n&"¶"; 0; 1); Length(n)+1; "¶"));
$$_RegisterParamController);

r = ValueCount(Left($$_RegisterParamController; Position("¶"&$$_RegisterParamController&"¶"; "¶"&n&"¶"; 0; 1))) + 1;

$$_RegisterParamController[r] = If(r>1; value; $$_RegisterParamController[r])

];"")



RegisterParamsAsVariables ( )


Let([

n = GetValue($$_RegisterParamController; ValueCount($$_RegisterParamController));

e = If(n="";"";Evaluate("let($" & n & "=$$_RegisterParamController[ValueCount($$_RegisterParamController)+1];\"\")"));

$$_RegisterParamController[ValueCount($$_RegisterParamController)+1] = "";

$$_RegisterParamController = LeftValues ( $$_RegisterParamController; ValueCount($$_RegisterParamController)-1)

];

If(ValueCount($$_RegisterParamController)>0; RegisterParamsAsVariables;"")

)


I will not spend time explaining the syntax of the calculations. If you feel curious I´m sure you are able to reverse engineer the syntax and understand what is going on. I will however show you how to use the functions.

First of you need to create the two functions in your FileMaker file. Goto the Manage Custom Functions Dialog and add two new functions. The first one must be named RegisterParams and have two parameters, "name" and "value".
The second function must be called RegisterParamsAsVariables and it uses no parameters. You can simply copy and paste the syntax from above the the two functions.


You are now ready to go. To testdrive the two functions you can do the following. Create a button on your layout and make it perform a script. As parameter to the script pass the following example:

RegisterParam ( "test1" ; "Hello World" ) & 
RegisterParam ( "test2" ; "We love FileMaker" ) &
RegisterParam ( "test3" ; "Matt Petrowsky is a funny guy" ) &
RegisterParam ( "test4" ; "Brian Dunning got > 100 Custom Functions" )

As you might figure out this will pass four different parameters to your script (no offense ment to Matt or Brian!). Before you can use them though you must perform the second function called RegisterParamsAsVariables inside your script. How you perform it does not matter, the simplest way is to set a dummy script variable to the result of the metod. Since RegisterParamsAsVariables does not return any values the dummy variable will never exist. A simple test script looks like this

The first line is all we really need but to be able to show you in the Data Viewer what happens I also added a second script step after setting the dummy variable.
When stepping trough the script before setting the dummy variable the Data Viewer looks like this.

But after running the RegisterParamsAsVariables function (setting the dummy variable) the proper script variables has been set and are ready to use.

If you want to set a two-dollar-variable or repeated variable simple prepend your parameter name with a dollar sign or append brackets with the preferred repetition number, or both =)


RegisterParam ( "normalValue" ; "Hello World" ) & 
RegisterParam ( "$twoDollarVar" ; "We love FileMaker" ) &
RegisterParam ( "normalValueRepeated[5]" ; "Matt Petrowsky is a funny guy" ) &
RegisterParam ( "$twoDollarVarRepeated[5]" ; "Brian Dunning got > 100 Custom Functions" )


I hope you can use these functions for something good, they work well for me! Time for lunch!

Matching values in lists of values in FileMaker

This is a fairly simple concept but it´s still quite often that I come across solutions where the developer forgot about this important detail.
The problem occurs if you have a list of values, that is values separated with line break, and want to compare a string to see if it exist inside the list.  This is very common when working with the List() function, getNthRecord(), ValueListItems(), iterating records or writing recursive custom functions.

Typically you will use PatternCount( listOfValues ; findString ) to find out if the value exist inside the list, but that is not a safe method. What you need to to to make it safe is to add line breaks as padding on both parameters.

PatternCount( "¶" & listOfValues & "¶" ; "¶" & findString & "¶" )

There reason for padding the findString is to make sure that it does not match a substring (part of a value). It must begin and end with a line break to be considered a match.

Padding on listOfValues is because if the match we are looking for is at the very beginning or end of the list there is no starting or ending line break unless we add them ourself.

Example list #1

• Orange
• Banana
• Applepie
• Ice Cream

If in want to find out if the value "Apple" is a member of the list above, the calculation PatternCount( listOfValues ; "Apple" ) will return true because it finds "Apple" in "Applepie" even though the value "Apple" is not a member of the list. We know that all values are surrounded by line breaks so by simply adding line breaks around the search string no match will be found on "Applepie"

Example list #2

• Java
• Javascript
• PHP
• Python

If we want to find out if the value "Java" is a member of the list above we will use the same method as before. We add line breaks before and after the search string. This however still returns false, no match is found because there is no initial line break before the value "Java". Therefore we must use line break padding on the list of values as well.

This is a simple but effective way to make safe matching of strings in a list of values. There might be other methods to do this in another way, choose which every suits you, but please make sure not to simply look for a unpadded string in a list of values. It will return unexpected errors!

Hope this helps someone

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 =)

Modal windows in FileMaker 12

As a new major feature in FileMaker Pro 12 a setting for the script step New Window was added enabling for modal and floating windows. You can also decide if the maximize, minimize and restore buttons should be active in the window title bar and if the window is resizable. At first I was very psyched on this new feature but soon discovered that the old method for creating modal windows was the one I still preferred. I guess that this topic will split developers into two camps, those who prefers working with two-dollar-variables (persistent variables) or global fields, and those who don´t.

Personally I belong in the latter category that prefere one-dollar-variables that only reside inside the current script, mainly because I don´t need to maintain a mental retain-counter in my head for which script need what values. But now I'm "jumping the gun" a little bit, lets start from the beginning.

The Classic method
Prior to FileMaker 12 the only real method for creating a modal window was via a loop that encapsulated a pause that waited for the users input. Of course there are several ways that this could be tweaked but the standard way (according to me) required two scripts. One that contains the loop, pause and logics being performed and one very simple helper-script that simply passed along any button-clicks that the user performed.

A skeleton for the main script could look like this

And the helper looks like this

The "magic" that makes it all happen is a setting for the buttons on the layout in the modal window. All buttons call the helper-script with a string as script parameter. The parameter is passed to the main script as soon the the pause is bypassed (the script continues running). The magic setting for the button is that it should continue any already running scripts (in our case the main script) when it is done running its own steps.

When defining a button, change the valuelist "Current Script" from Pause to Resume as seen on the picture below.

The helper-script will exit and return any parameter you sent when defining the button. When the helper-script exits, the resume commands bypasses the pause in the main script and proceeds with the next script-step. As a bonus we can use the get(ScriptResult) function to get the string parameter that was passed to the helper-script (since it exited returning the script parameter as script result). Now the main script knows which button the user clicked and since we have passed the pause the main script will continue executing.

All we need to do now is to throw a couple of If-statements to decide what actions to perform.

This was a quick guide to what I would call the classic method for creating modal windows and it works just as well in earlier versions then FileMaker 12.


The FileMaker 12 way
Since FileMaker added the possibility of making modal windows natively one could argue that the need for a loop is obsolete. In a way that is correct but one must also keep in mind factors like best practice when developing. The main script might be less complex without the loop but instead we must create scripts for every button splitting our logics into more scripts.

When creating a new modal window in FileMaker 12 we simply set the window type to Modal. But that does not pause the script and await user input. Thus once the windows is opened the script should exit leaving the modal window open for the user to work in. Once the user is done working he clicks either the OK or Cancel buttons calling one of two scripts.

The script performed when clicking OK should validate the data entered by the user, save it to database or simular and then proceed to do some cleanup like closing the window and clearing global fields and two-dollar-variables (persistent variables).

The script performed when clicking Cancel should not validate or save data but still must perform the same cleanup as the OK button did.


So what to argue about?
If the "FileMaker 12 method" is a simpler and faster script to write, why not just use that? First of all I want to clarify that the following is just my own opinions and if you don´t agree thats great, please use the native modal windows and I´m sure you´ll make it work just fine! This is not criticism any way =)

There are a few problems with the native modal windows. The biggest reason that I don´t use it is because I can´t use one-dollar-variables. The reason that I can´t use them is because the script that opens the modal window is done once the window is open and thus clearing the variables. The script performed when clicking OK will not be able to use the values I stored and this makes adding stuff like related records and so on very hard. If I want my script to add a related record I need to carry the primary key value from the parent record to the secondary key field in the child table.

Now I guess that some of you are thinking "not a problem, just store it in a global field or two-dollar-variable" but thats just the thing, I do not want to do that!
Since both global fields and two-dollar-variables are persistent I must make absolutely sure to clear the values no matter what button the user clicks in the modal window. If I do not clear the values I risk a conflict between old values and new values the next time the user views the modal window. The bugs produced od that can be extremely bad and hard to find since it´s of the kind that makes you say "It works when I do it".

Of course it is not impossible to write scripts that clears variables and globals and its not even hard, but it requiers that you maintain the cleanup work in all three scripts (or more if you have mor then two buttons). If you allow for the "red X" close window button to be active you must also make sure that the cleanup work is done if the uses simply closes the window. And what happens if another developer helps out with your script. Does he know absout all the scripts he must update if all he wants to do is add another two-dollar-variable? It´s very easy to forget and thus adding a bug...

Another reason for the classic method of modal windows is that the majority of menu commands are disabled when a script is running. As long as the script is in the loop the user will not be able to access much from any menu or toolbar in FileMaker. You could do the same in the FileMaker 12 method but that would require a lot of custom menus. A modal dialog is modal because it should be impossible to exit it without your code controlling the result, when using the classic method you get that for free.

Personally I´m the type of person that prefers long scripts unless it serves a purpose to divide it into several smaller ones. The most obvious purpose being reusable code, a script that can be called as a subscript by several other scripts. A long script does not scare me as long as it is well commented and don´t nest control statements like loops and Ifs to deep. The classic method keeps ALL logics inside the main-script and thus makes it east to maintain and search for potential errors.

In some odd cases you may want to open a modal window inside a modal window. An example being if you open a modal window to add a person to a CRM but realize half way through that the company the persons works at is not yet added. You want to open the modal Add Company window without closing the already open Add Person window. When the company is added the window is closed and the user can continue to add the person uninterrupted. If you work with two-dollar-variables in this case you must be very careful when naming the variables since they are not contained inside a script. If the Add Person script set a two-dollar-variable and then the Add Company also set one with the same name you are in real truble.

Also.. it is not at all possible to open a modal window while standing in a modal window if you use the FileMaker 12 way ;-) This can only be done when using the classic way.

I am absolutely not being a critic against the new modal window feature in FileMaker 12, it´s great for lots of users. But as a professional developer and being a bit of old-school I will only use the classic method since it prevents a lot of potential errors and keeps me in control of my users actions.

Whichever method you choose to use I hope this post was helpful in some way.

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.