Introduction

The history of the ACF language

With the 30+ years of experience in the computer industry, working with many different programming languages, I have defined this language from the syntax of my liking pulled from many different languages. The goal was to create something easy to read and follow, easy to program in, and have useful syntax doing tasks that I feel lacking in the regular scripting or standard calculations. You will find something from PHP, something from C, something from old Fortran or Basic, something from ADA, and something from some other proprietary programming languages. I have not implemented the curly-bracket block syntax from PHP to make the code more readable. Instead, blocks end with keywords as "end if", "end for", or "end while". FileMaker scripting also has this type of syntax.

In regular FileMaker calculations - They are superb for simple calculations, but when nesting "if" constructs, the code tends to be somewhat unreadable. It is ways to make them more readable using indentations and splitting stuff into different lines, but when the calculation ends with 5+ end parenthesis, it is often hard to get the grasp of what is going on. Here is an example from a regular FileMaker calculation I come across:


WashCharacters ( If(DeliveryAddress::l_Company_Name ≠ "" and DeliveryAddress::l_Country ≠ "" ;  LeftWords ( DeliveryAddress::l_Country ; 1) ;     If( Order::p_Country ≠ "" ; LeftWords( Order::p_Country  ;1) ; "NO"))) 

Does this example above really do what is supposed to do? - Or do we lack some logic here? One must at least stare at this for a few minutes to figure out. The short answer is. Yes, we do lack some logic here. The calculation ensures something in the result, but it is a mix of country name or two-digit country code. Having a block structure syntax would have shown this with the blink of your eye and even did not got into this bug when writing it at all.

Here is an example of the ACF function doing the same - it is somewhat longer, but clear to read what it is doing:

function delivery_country ()
    string country;
    If (DeliveryAddress::l_Company_Name ≠ "" && DeliveryAddress::l_Country ≠ "") then
        country = @LeftWords ( DeliveryAddress::l_Country ; 1)@;
    elseif ( Order::p_Country ≠ "" ) then
        country = @LeftWords( Order::p_Country  ;1)@;
    else
        country = "NO"; 
    end if
    return country; 
end     

The goal is better code quality, faster development, more portability, re-use of code, saving valuable development time.

The Product

ACF compiler is a compiler for the ACF FileMaker Plugin. The purpose is to create more advanced custom functions for use in FileMaker development. Traditionally in FileMaker, the language is the scripts and calculations. The calculations also contain some structural elements like if or case structures. However, they are one-liners. If the complexity becomes too high, the code is somewhat unreadable and hard to understand how they work. There is also no looping involved in custom functions.

The concept of Advanced Custom Functions consists of structures from a standard programming language, in a proprietary language definition defined in this project. The syntax of this language is similar to other languages, but also made more like scripting language syntax so FileMaker developers should fast come up to speed with this language definition.

Here is an example of a custom function that calculates the Annual interest rate from the loan value, the size and frequency of the payments, and the run-time for the loan. As this is impossible to calculate with a formula, it has to be simulated by test and fail technique to close in the result. Creating this in a normal custom function in FM, you will need as pr FM17, to use recursive methods.

First, we define a function to calculate the Payment Value from Present value, interest rate, and the number of payments.


    /*
       AnnuityLoanPayment: 
       Calculate the Payment amounth for an annuity loan : 
       PV = Present Value
       r  = Interest rate
       n  = number of payments
    */ 
    
    function AnnuityLoanPayment ( float PV, float r, int n)
        float P = r*PV/(1-(1+r)^(-n));  
        return P; 
    end

Then we do the other function that uses this in the simulation. We put on the print statements for debugging and testing.


    /*
       CalcAnnuityInterestRate: 
       Calculate the Interest rate for an annuity loan by simulation : 
       LoanSum = Present Value
       P  = Payment amounth
       Y  = number of years
       nY  = number of payments pr year. 
    */ 

function CalcAnnuityInterestRate ( float LoanSum, float P, int Y, int nY)

float r; 
float res; 
// We start with High Interest rate. 
float rY = 100.0;  
float step = rY/2; 
float usedrY; 
if (P*Y*nY < LoanSum) then
        throw "\nNot enough payment - Payment starts at : " + LoanSum / (nY*Y); 
else
    repeat
        usedrY = rY; 
        r = rY/100/nY; 
        res = AnnuityLoanPayment ( LoanSum, r, Y*nY); 
        print "\nInterest: " + rY + "% - Payment: " + res;
        if ((res-P)>0.0) then
            print " diff(+) " + (res-P); 
            rY = rY - step; 
        else
            print " diff(-) " + (res-P); 
            rY = rY + step; 
        end if
        step = step / 2; 
  until ((abs(res-P)<0.0001) || (step < 0.000001));
end if
return usedrY; 
end
Doing like 26 iterations in the loop, we got the result. For the call like this:

    CalcAnnuityInterestRate(100000.0, 2000.0, 5, 12); 
The result was calculated in 0,000217 seconds or 217 micro seconds on my developer Mac-Mini 2.8 GHz Intel Core i5. The Print statement commented out in this test. With the print statements, the execution time increased to 483 micro seconds.

    Execution completed in 0.000217 secs: result: 7.4201

Running this with the print statements will have this output to the console function:


    Interest: 100.0000% - Payment: 8402.305216 diff(+) 6402.305216
    Interest: 50.00000% - Payment: 4560.474166 diff(+) 2560.474166
    Interest: 25.00000% - Payment: 2935.132338 diff(+) 935.132338
    Interest: 12.50000% - Payment: 2249.793823 diff(+) 249.793823
    Interest: 6.250000% - Payment: 1944.926168 diff(-) -55.073832
    Interest: 9.375000% - Payment: 2094.082735 diff(+) 94.082735
    Interest: 7.812500% - Payment: 2018.677871 diff(+) 18.677871
    Interest: 7.031250% - Payment: 1981.594566 diff(-) -18.405434
    Interest: 7.421875% - Payment: 2000.084452 diff(+) 0.084452
    Interest: 7.226562% - Payment: 1990.826555 diff(-) -9.173445
    Interest: 7.324219% - Payment: 1995.452267 diff(-) -4.547733
    Interest: 7.373047% - Payment: 1997.767550 diff(-) -2.232450
    Interest: 7.397461% - Payment: 1998.925799 diff(-) -1.074201
    Interest: 7.409668% - Payment: 1999.505075 diff(-) -0.494925
    Interest: 7.415771% - Payment: 1999.794751 diff(-) -0.205249
    Interest: 7.418823% - Payment: 1999.939598 diff(-) -0.060402
    Interest: 7.420349% - Payment: 2000.012024 diff(+) 0.012024
    Interest: 7.419586% - Payment: 1999.975811 diff(-) -0.024189
    Interest: 7.419968% - Payment: 1999.993918 diff(-) -0.006082
    Interest: 7.420158% - Payment: 2000.002971 diff(+) 0.002971
    Interest: 7.420063% - Payment: 1999.998444 diff(-) -0.001556
    Interest: 7.420111% - Payment: 2000.000708 diff(+) 0.000708
    Interest: 7.420087% - Payment: 1999.999576 diff(-) -0.000424
    Interest: 7.420099% - Payment: 2000.000142 diff(+) 0.000142
    Interest: 7.420093% - Payment: 1999.999859 diff(-) -0.000141
    Interest: 7.420096% - Payment: 2000.000000 diff(+) 0.000000
    Execution completed in 0.000483 secs: result: 7.4201

The compiled code

The compiled code is not machine code language run directly by the processor core, but a series of instructions run by a runtime library who interprets each instruction and executes them in a loop. Instructions can be similar to those of processor core instructions, but other instructions can be library functions.

Why not use PHP or JavaScript

Those languages are great languages, but the idea here is to make this very integrated with the FileMaker environment, doing references and FileMaker calculations directly into the source. Like this,

string a = @let([v = 22; b=23]; v*b*$$ConstantValue)@;

or

$$OurPartResult = sqrt ( pi*r^2 ) + $$FileMakerVar1; 

In this way, we can blend the code into the environment that makes the development very efficient.

The other reason is that PHP or JavaScript does not run compiled - but run interpreted. Not close as fast, and from a developer point of view - the compiler will syntax check the source - So we know that the source does not give runtime errors because of syntax errors. It is also good not have the function depend on external libraries that can be OS dependent or different versions that break the functionality. And Finally, from a deployment point of view - the compiled product can be installed by a script step in FileMaker directly, - and thus not deploying the source code.

Example compiled code

This example is a bit technical - and you can safely skip to next heading - if you are not especially interested in the construction of the code.

The small function first has this compiled sequence (Shown Assembly like mnemonics, that, of course, are stored as integer representations. The runtime uses a stack to operate on arguments and a variable stack for the local variables. They are merely assigned a variable number that is relative to the start of the local variable block for the function. The instructions occupy 1, 2 or 3 locations - dependent on its parameters.

// 168: function AnnuityLoanPayment ( float PV, float r, int n) 
 841:         ENTER 38 3          // AnnuityLoanPayment
 844:         DECL 0 5          // Declare variable PV: DOUBLE
 847:         LDPARX 0          // PV
 849:         DECL 1 5          // Declare variable r: DOUBLE
 852:         LDPARX 1          // r
 854:         DECL 2 1          // Declare variable n: INTEGER
 857:         LDPARX 2          // n

// 169:     float P = r*PV/(1-(1+r)^(-n));  
 859:         DECL 3 5          // Declare variable P: DOUBLE
 862:         LDVARL 1          // r
 864:         LDVARL 0          // PV
 866:         MUL_FF            // Multiply 2 doubles
 867:         LDNUM 6           // 1
 869:         LDNUM 6           // 1
 871:         LDVARL 1          // r
 873:         ADD_IF            // Add int and double
 874:         LDVARL 2          // n
 876:         LDNUM 21          // -1
 878:         MUL_II            // Multiply 2 int's
 879:         XupY              // Power
 880:         SUB_IF            // Subtract int and double
 881:         DIV_FF            // Div 2 doubles
 882:         STOREL 3          // P

// 170:     return P; 
 884:         LDVARL 3          // P
 886:         RETURN 1 
// 171: end

The Compiler set up a table with all the literals, strings or numbers used in the calculations. The instructions only refer to the index in this table. In this way, literals are shared between all the functions in the same file.

Why not use processor core instructions directly

There are several reasons for that.

  1. First, the runtime would be more processor hardware dependent. Now the same compiled code can be run on different types of computer hardware without alterations. The compiler has then only one target code to generate and avoids the need for having different versions of the executable for mixed environments of Mac and Windows users.
  2. The second is that we have better control of the executions. The execution will be confined within the range of the program space for the functions we have made.
  3. The third reason is that we have a more straightforward library concept. Many of the instructions here are in fact library functions doing much more than processor core instructions would be able to do alone.

We could optimise this further using processor core instructions directly, but seen in the light of the example above; the speed is far faster than I dared to expect.

Compare to a FileMaker Script doing the same

I made a regular custom function to do the first function, and then a script to execute the simulation. The result with the print (used a variable to collect text instead), is seven milliseconds at average. When I removed the text logging, the result was sometimes, 4, 5 or 6 milliseconds. Anyway - far slower than our compiled code running at ~ 500 microseconds with the print statements, and only 217 microseconds without the print statements. i.e. 23 times faster without the print, and 14 times faster with the print-statements. We arrived at the same result.

This example is available in the download area - called "acf-annuity-loan speed demo"

Here is the FileMaker script I used for test

Set Variable [ $ts ; Value: Get(CurrentTimeUTCMilliseconds) ] 
Set Variable [ $LoanSum ; Value: 100000 ] 
Set Variable [ $Payment ; Value: 2000 ] 
Set Variable [ $Y ; Value: 5 ] 
Set Variable [ $nY ; Value: 12 ] 
Set Variable [ $rY ; Value: 100 ] 
Set Variable [ $step ; Value: $ry/2 ] 
Set Variable [ $$debLog ; Value: "" ] 
If [ $Payment * $Y * $nY < $LoanSum ] 
    Show Custom Dialog [ "Not enough payment - Payment starts at : " & ($LoanSum / ($nY*$Y)) ] 
Else
    Loop
        Set Variable [ $usedrY ; Value: $rY ] 
        Set Variable [ $r ; Value: $rY / 100 / $nY ] 
        Set Variable [ $res ; Value: AnnuityLoanPayment ( $LoanSum ; $r ; $Y * $nY ) ] 
        // Set Variable [ $$deblog ; Value: $$deblog & "¶" & "Interest: " & $rY & "% - gives Payment: " + $res ] 
        If [ ($res-$Payment)>0 ] 
            // Set Variable [ $$deblog ; Value: $$deblog & " diff(+) " & ($res-$Payment) ] 
            Set Variable [ $rY ; Value: $rY - $step ] 
        Else
            // Set Variable [ $$deblog ; Value: $$deblog & " diff(-) " & ($res-$Payment) ] 
            Set Variable [ $rY ; Value: $rY + $step ] 
        End If
        Set Variable [ $step ; Value: $step / 2 ] 
    Exit Loop If [ ((Abs($res-$Payment)<,0001) or ($step < ,000001)) ] 
    End Loop
End If
Set Variable [ $te ; Value: Get(CurrentTimeUTCMilliseconds) ] 
Show Custom Dialog [ "Finished" ; "We finished this in " &  ( $te - $ts ) & " millisecs, result: " & $usedrY ] 

Interaction with the FileMaker environment

What is different in ACF and many other implementations of other high-level language elements is the interaction with the FileMaker environment - the stuff that makes the ACF functions custom functions.

  1. You can use and assign values to ordinary FileMaker variables just using their name, i.e. $FileName or $$extraresult. The access is not so fast as using internal local variables but serves as an additional interface to the script performing the functions.
  2. You can perform FileMaker calculations directly from the ACF script, enclosing the calculation with "@" characters for single-liners, and double "@@" for multi-liners.
  3. You can access field content by using the "::" notation for field names, i.e. table::field directly in internal calculations. However, you cannot assign to fields, only read their values. The return value of the function can, of course, be assigned to fields using the "set field" command.

Standalone compiler or plugin compiler.

The compiler was first developed as a standalone compiler (command line) - with the plan to include it all into a FileMaker plugin along with the runtime. Now we have the plugin with both the runtime and the compiler, and many of functions are tightly bound to the FileMaker plugin environment. The standalone compiler is therefore not actual to deliver anymore. For particular need, we could provide a standalone syntax-checker for use in IDE's to syntax check your code from the editor. That will be another project.

Conclusion

This package will be able to develop faster - In a language with many similarities with traditional programming languages like PHP, C, 4D, BASIC, FORTRAN, etc. Those who have experience with any of those languages should be able to develop in this fast. The product means speedier development time (i.e. in the script editor in FileMaker, the script is not text, and you have to click up dialogues (often modal) for each script step - sometimes several dialogues for each. Cut and paste parts of code is difficult because you have to open those dialogues to copy, and cannot do that from where you are when you need to paste. In this language, the source can be edited using a plain text editor, like TextMate, or even the Xcode editor. Copy & Paste is smooth without the need to open modal dialogues for each copy-operation.

Then the compiled code runs fast so the user's experience with the solution will be better. Also, The SQL functions let you retrieve, update or insert data without references to the current layouts table occurrence.