Why did we develop the ACF plugin

It all started after many years of developing a comprehensive system in FileMaker. I have worked with many other systems, such as 4D (Fourth Dimension), PHP, MySQL databases, C, C++, and several others. FileMaker is a very good system, especially for its flexibility in layout design and database integration.

As long as you were comfortable with forms for data entry, database, searching and sorting, reports, and printouts, you found everything you needed directly in FileMaker.

There were some things, however, that I never became entirely comfortable with. Some solutions required either creating a vastly complex relationship graph with corresponding system layouts to use this in scripting to access the data as needed for specific purposes or you could use SQL queries to access data without using the relationship graph and moving around in layouts. SQL works very fine, but working with the result set in FileMaker scripts often became heavy material, as it came out of the queries as a CSV dump of the dataset. This had to be parsed and broken apart to work with the data. The ExecuteSQL function in FileMaker supports neither INSERT nor UPDATE - only SELECT. For such functions, one then relied on plugins that offered this functionality.

Another thing is producing detailed exports to other systems; plugins were needed to work with text files. For accessing WEB-based APIs, another plugin was needed.

My conclusion was that if I wanted to solve this, we needed a plugin with its built-in programming language that could solve the SQL challenges, and at the same time have an efficient way to work with the result set. This included array data types that could hold the results from the SQL query and let us access the individual data cell in the set. The programming language had to have an efficient way to work together with the Script language in FileMaker and be capable of working with algorithms, structured programming, loops of various kinds, and more. It needed good performance and executing functions quickly. I considered PHP but eventually discarded it. I saw someone had made something with it before, but the solution required the installation of external libraries on the users' machines, and the integration with FileMaker was not as simple as I would have liked. JavaScript was also under consideration, but here too, I was not entirely satisfied. That's when I began working with the ACF Plugin and the ACF language. A compiler and a runtime system were created, which has since been developed quite tremendously.

Today, when I create new functionality with FileMaker, 90% of the programming is entirely or partly made as ACF functions and only a few simple scripts call these functions. It's fantastic, and it has impressive performance. In most cases, the functions run 10x as fast as if I had scripted everything.

I wish as many developers as possible would discover the goldmine this plugin has become. It's so easy to reuse code, work with data handling and analysis, and discover the efficiency that lies in this. Just take a look at all the programming examples that exist in the reference manual. Good luck to everyone.

The ACF language features:

• Functions written using a text editor, ability to compile and load compiled code. The functions can be called from standard FileMaker calculations
• Enhanced SQL capability with placeholder logic in SQL statements, and INTO keyword to target the result-set into arrays. Both access to native databases in FileMaker or remote MySQL databases.
• Structured language, with IF-THEN-ELSE-ENDIF and CASE-ENCASE structures.
• Loops: FOR-ENDFOR loops, REPEAT-UNTIL loops, WHILE-ENDWHILE loops.
• Arry datatypes: Access to individual cells in arrays making it easy to process data.
• Strict data types: Declaration of all variables used with simple declaration statements, for String, Float, Int, long, date, time, timestamp, Bool, XML, and JSON. This improves the execution speed. It improves error-checking. It also simplifies operations where functions work differently on different types. For example, a=a+1 increases a with 1 if it's an int or float number, but gives the next day if a is a date type.
• Direct FM environment access: Access to FileMaker script variables, fields, and ability to execute FileMaker calculations inside the ACF functions.
• File system access for reading and writing file-system files, listing or selecting files and directories. The function also works on files inside ZIP archives.
• API Access: HTTP Get and Post functions make it easy to access remote services using REST or SOAP protocols.
  
• Send E-mail function and MarkDown to HTML functions to facilitate good-looking e-mail sending.
• JSON/XML datatypes make it easy to build or parse such structures.
• Common crypto and Hash functions make it easy to do secure transactions and storage.
• Speed: Impressive speed of up to 10 times compared to similar scripted functions.
• Calling other ACF functions is as easy as writing the name of the function with parameters. This is a good practice to encapsulate common code and improve the readability.
• Easy to learn as the ACF function has many common denominators with other languages, it is easy to learn. Using the many programming examples found in the reference manual.
• Documentation is extensive and describes each function with examples and explanations.

The plugin is available for MacOS both Intel and Arm-based computers. For Windows, the last version will soon be available. An older version is available for Windows today, but it does not provide all the new functions in the ACF plugin.

Complex FM calculations in FileMaker versus The ACF plugin

One of the important features of this plugin is the code-readability when it comes to more complex calculations. As code is less readable, it also promotes bugs. Just because the developer did not catch the bugs due to the inability to see it when the function is a mess.

Here is an example from a system I did some debugging on. The purpose is to create a JSON list used as a parameter to a function that marks orders as delivered and does the invoicing.

Example FileMaker Kalkulation:

While ( [ 

~key = "1" & OrderHeader::Ordre_nr ;
~query = ExecuteSQL ( "SELECT \"Auto_nr\", \"Lagerførende\", \"Utlever_antall\" FROM OrderHeader_OrderDescriptions_visning WHERE Utlever_relasjon = " & ~key  ; "" ; "") ;
~query2 = ExecuteSQL ( "SELECT \"Auto_nr\", \"Lagerførende\", \"Utlever_antall\" FROM OrderHeader_OrderDescriptions_visning WHERE Utlever_relasjon = " & ~key & " AND \"Lagerførende\" = 'Ja' AND \"Lagerførende\" is not null" ; "" ; "") ;
~count = ValueCount ( ~query ) ;
~count2 =ValueCount ( ~query2 ) ; 
~pickCount = 0 ;
~line = "" ;
~line2 = "" ;
~calc = "" ;
~ai = 0 ;
~i = 1

] ; 

~i ≤ ~count

; [ 

~line = Substitute ( GetValue ( ~query ; ~i ) ; "," ; "¶" ) ;
~line2 = Substitute ( GetValue ( ~query2 ; ~i ) ; "," ; "¶" ) ;
~calc = JSONSetElement ( ~calc ; [ "deliver.line[" & ~ai & "]id" ; GetValue ( ~line ; 1 ) ; 2 ] ) ;
~calc = JSONSetElement ( ~calc ; [ "invoice.line[" & ~ai & "]id" ; GetValue ( ~line ; 1 ) ; 2 ] ) ;
~calc = If ( ~pickCount < ~count2 ; JSONSetElement ( ~calc ; [ "pick.line[" & ~ai & "]id" ; GetValue ( ~line2 ; 1 ) ; 2 ] ) ; ~calc) ;
~pickCount = If ( ~pickCount < ~count2 ; ~pickCount + 1 ; ~pickCount ) ;
~calc = JSONSetElement ( ~calc ; [ "deliver.count" ; ~count ; 2 ] ) ;
~calc = JSONSetElement ( ~calc ; [ "invoice.count" ; ~count ; 2 ] ) ;
~calc = JSONSetElement ( ~calc ; [ "pick.count" ; ~pickCount ; 2 ] ) ;
~calc = JSONSetElement ( ~calc ; [ "orderId" ; OrderHeader::Ordre_nr ; 2 ]  ) ;
~calc = JSONSetElement ( ~calc ; [ "total.count" ; ~count ; 2 ] ) ;
~ai = ~ai + 1 ;
~i = ~i + 1

] ; 

Case ( ~count < 1 ; JSONSetElement ( ~calc ; [ "total.count" ; ~count ; 2 ] ) ; ~calc )

)

First, after I converted this function to the ACF language - I discovered several bugs in it.

The problem is that it was two SQL queries. The ~query2 is not guaranteed to have the same number of rows as the ~query line. Therefore mixing those in the pickCount calculation is prone to errors. As we pull the "Lagerførende" field from the database, we can use that instead and only have one SQL Query. Also, the last JSON elements are moved to after the loop, as they are not needed in the loop.

Here is the corrected ACF function:

function CalcDeliverJSON ( string ordreNr )

    string res,key = "1" + ordreNr; 
    array string Lforende;
    array float UtlevertAntall; 
    array int AutoNr; 
    res = ExecuteSQL ( "SELECT \"Auto_nr\", \"Lagerførende\", \"Utlever_antall\" 
    FROM OrderHeader_OrderDescriptions_visning 
    WHERE Utlever_relasjon = :key 
    INTO :AutoNr, :Lforende, :UtlevertAntall"  ) ;
    
    int count = sizeof (AutoNr); 
    
    int pickCount=0; 
    JSON result; 
    
    for (i=1 , count ) 
        result["deliver.line[]"] = JSON ("id", AutoNr[i]); 
        result["invoice.line[]"] = JSON ("id", AutoNr[i]); 
        if ( Lforende[i] == "Ja" ) then
            result["pick.line[]"] = JSON ("id", AutoNr[i]);
            pickCount++; 
        end if 
    end for
    result["deliver.count"] = count; 
    result["invoice.count"] = count; 
    result["pick.count"] = pickCount; 
    
    result["orderId"] = ordreNr; 
    result["total.count"] = count; 
    
    return result; 
end