ACF PLUGIN Function Reference

TABLE OF CONTENT

Introduction

Basic Operations

FileMaker Plugin Calls

Array functions

FileSystem Functions

MarkDown - HTML

Programming Examples

Demo


Introduction

Advanced Custom Functions Plugin for FileMaker

Updated: 15.09.2023 - Plugin version 1.6.3.0

ACF_Functions introduces a new custom functions language designed to enhance the efficiency of custom function development within FileMaker. This new language is a high-level language, akin to PHP or PASCAL, meticulously integrated into the FileMaker environment. It grants direct access to FileMaker variables, fields, and other built-in functions, all defined within text files external to FileMaker. These files can be compiled or loaded into the FileMaker environment using the plugin's functions. The compileed libraries are encoded in Base64 format, so they can be loaded directly from FileMaker Text fields.

Compared to regular Custom Functions, which are essentially parameterized calculations, much like the calculations you can utilize in a Set Variable statement within a script, ACF functions provide enhanced capabilities. While standard Custom Functions offer the possibility of employing inline "if" and "Case" statements for conditional calculations and are well-suited for simple tasks, they can become challenging for the programmer when nested Ifs and Cases proliferate, making it difficult to decipher the logical flow within the calculation. Consequently, they present challenges in terms of maintenance and debugging. In contrast, the ACF language is intentionally structured in a procedural manner, similar to scripts. ACF functions share similarities with custom functions, featuring parameters and return values, but they also grant direct access to the file system, SQL queries, FileMaker variables and fields. Moreover, ACF functions execute rapidly since they are compiled. Having used the Plugin we developed for several years, I can attest to its stability. Numerous customers rely on solutions we've created, many of which involve implementing complex logic through ACF functions.

For detailed information, please consult the reference manual. You can click on any topic in the left sidebar for reference. Additionally, you'll find programming examples under the "Examples" section to help you get started.

The ACF Plugin

Deployment-Friendly Solution

In a traditional FileMaker Solution, updating a deployed solution at a customer site typically involves changing scripts, layouts, and database definitions. This process can be tedious and time-consuming. Alternatively, you might choose to migrate all customer data into a new solution and re-implement custom code—a similarly laborious task.

ACF simplifies the update process significantly. Updating code within ACF functions is straightforward. You can either load the new compiled functions into the solution using simple cut-and-paste or a small script. Alternatively, you can utilize the document service to load binaries from a web service to which you've deployed the code. This web service supports encryption to ensure the code's integrity has not been compromised.

With just a click of a button, users can reload ACF binaries themselves, immediately gaining access to the updated functionality.

Code Portability: A Vital Consideration

One crucial aspect of development projects is the use of portable code segments—code that can be reused wherever needed. In a FileMaker script, achieving code portability can be challenging because script logic is often tied to its position in the relationship graph. This means that all field references used in the script must match the layout's position in the relationship graph.

For example, consider a scenario where you have a "Documents" table related to both "Orders" and "Invoices." You may have documents related to both or individually, all stored in the same table. In this case, the relationship from the invoice table is named "invoice_documents," while the relationship from the orders table is named "orders_documents." This leads to the necessity of maintaining two duplicate sets of nearly identical scripts—one for orders and another for invoices. These scripts are designed to work with field references specific to either orders or invoices, making them far from portable.

ACF addresses this challenge by enabling the creation of portable code. Advanced Custom Functions (ACF) do more than just offer custom functions; they often replace entire scripts or groups of scripts. Portable code created with ACF can save you significant development time because:

Creating genuinely portable code does require some planning, and a few factors to consider include:

How This Manual Is Created

This manual consists of a collection of static HTML pages. The source text is initially authored using "Lightpaper" in Markdown format, which facilitates the inclusion of code samples and enables formatting for the manual pages.

Next, the Markdown source is integrated into a small FileMaker 17 application that incorporates functions written in the new language designed for Advanced Custom Functions. You can explore these functions in the example section of this manual or download the application from our website, see the "demo" section of this manual.

These functions serve to convert the Markdown-formatted manual pages into HTML format. Subsequently, the HTML files are dissected and organized into a structured layout featuring a left navigation bar, a top navigation bar, and a list of pages within the left navigation bar.

The final step involves cross-referencing the HTML text with other titles in the manual to generate links to the corresponding manual pages. If a phrase within the text matches the title of another document, it becomes a clickable link leading to that specific page.

All these components are then merged and saved to create the HTML files once more.

When new pages are added, we systematically loop through all the document records in the database, generate HTML files, and upload them to the web. This process ensures that all left navigation menus are updated across all documents. Remarkably, the entire operation of generating approximately 50 documents takes less than one second.

We wish you an enjoyable reading experience.

Ole K Hornnes
HORNEKS ANS


Back to top

When is the ACF plugin Useful

FileMaker is an excellent tool for creating custom database applications with well-designed layouts backed by a relational database. It offers a scripting language for data processing and implementing business logic. So, how does the ACF plugin come into play in this context, especially when FileMaker already has Custom Functions?

The scripting feature in FileMaker serves the purpose of automating steps that would otherwise be executed manually. For instance, if you need to create a new record in a table, a script will navigate to the relevant layout, add a new record, set the required fields, and then return to the original layout. These scripts also support looping, conditional operations via the "IF..." construct, resembling a natural language to some extent. However, one drawback is that these scripts often involve interacting with modal windows to set parameters for each script step.

This has both positive and negative sides:

When it comes to implementing algorithms, such as extracting statistics from a solution or adding a modulo-10 control digit to an invoice number, parsing data files (e.g., OCR or Cremul files), generating EDIFACT or EHF format files, or even inserting records in related tables, the ACF language provided by the ACF plugin proves to be faster and more straightforward. You'll find numerous examples in the "Programming Examples" chapter of this manual. Some of these examples are lengthy, but you don't need to understand every detail to use them. They provide syntax and some ready-to-use examples, all drawn from real-world solutions.

In the ACF plugin, tasks like inserting records in related tables become simpler, as you don't need to switch to related table layouts for insertion. You can achieve this with a straightforward SQL query. FileMaker's built-in SQL script (i.e., ExecuteSQL) only supports SELECT, while the plugin's SQL capabilities extend to INSERT, UPDATE, DELETE, in addition to SELECT.

Feel free to explore the "Programming Examples" section, which showcases various real-world scenarios. You'll discover that you can use many of these examples directly or adapt them to your needs.

Happy reading! Also, check out the demo chapter for information on how to access the plugin. Until October 31, 2023, a free license is available. Afterward, you can request a demo license tailored to your organization or inquire about pricing, which is quite reasonable.


Back to top

Introduction

A Brief History of the ACF Language

With over 30 years of experience in the computer industry, and having worked extensively with various programming languages, I have developed the ACF language by drawing inspiration from the syntax of multiple programming languages. The primary objective was to create a language that is both easy to read and write, while also being efficient in performing tasks that I found lacking in conventional scripting or standard calculations. In ACF, you will find elements inspired by PHP, C, old Fortran, Basic, ADA, and other proprietary programming languages.

To enhance code readability, I chose not to implement the curly-bracket block syntax seen in PHP. Instead, in ACF, code blocks are concluded with keywords such as "end if," "end for," or "end while." This approach aligns with the syntax used in FileMaker scripting, making the code more structured and comprehensible.

In regular FileMaker calculations, although they excel at handling simple calculations, nesting "if" constructs can often result in code that is challenging to decipher. While indentation and breaking code into multiple lines can improve readability to some extent, it becomes problematic when calculations end with a multitude of closing parentheses. Let's examine an example from a typical FileMaker calculation:


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

Is it immediately clear whether this example achieves its intended purpose? Or does it lack clarity and logic? A quick analysis reveals that there might be a logic issue in this calculation. Deciphering its purpose can take several minutes. In contrast, ACF's block structure syntax would have made this issue apparent at a glance, potentially preventing such bugs during development.

Consider the following ACF equivalent of the same calculation. While it may be somewhat longer, its clarity is evident:

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 ACF language aims to elevate code quality, expedite development, enhance code portability, promote code reuse, and ultimately save valuable development time. It prioritizes clear and structured code, which aids developers in writing robust and maintainable software.

The Product

The ACF compiler serves as a compiler designed for use with the ACF FileMaker Plugin, aimed at facilitating the creation of advanced custom functions for FileMaker development. Traditionally, FileMaker primarily relies on scripts and calculations as its core language components. While calculations do include some structural elements such as "if" or "case" structures, they tend to be concise one-liners. However, as complexity increases, the code can become challenging to read and understand. Additionally, FileMaker calculations do not inherently support looping.

The concept of Advanced Custom Functions (ACFs) introduces programming language structures into the realm of FileMaker, creating a proprietary language definition within this project. The syntax of this language draws inspiration from other programming languages while adopting a scripting language-like syntax to expedite FileMaker developers' familiarity with it.

Here's an example of a custom function that calculates the annual interest rate based on the loan value, payment size, payment frequency, and loan duration. This calculation is not achievable through a simple formula and necessitates an iterative "test and fail" approach to converge on the result. When attempting to create such a function within the standard FileMaker custom function framework, recursive methods are typically required starting from FileMaker 17.

To begin, let's define a function that calculates the Payment Value based on the 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

Next, we'll create another function that utilizes this calculation for simulation purposes, and we'll include print statements to aid in 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

Performance and Debugging Insights

During the execution of approximately 26 iterations within the loop, we successfully obtained the desired result. For example, consider the following function call: 


    CalcAnnuityInterestRate(100000.0, 2000.0, 5, 12);
The result was computed in a mere 0.000217 seconds or 217 microseconds on my developer Mac Mini, featuring a 2.8 GHz Intel Core i5 processor. It's important to note that the print statements were commented out during this test. However, when the print statements were included, the execution time increased slightly to 483 microseconds. Here's an example of the output with the print statements active:


    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

It's important to note that the compiled code is not machine code executed directly by the processor core. Instead, it comprises a series of instructions interpreted by a runtime library. These instructions can include both processor-like instructions and library functions.

Why not use PHP or JavaScript

While languages like PHP and JavaScript are undoubtedly powerful, our aim with ACF is to provide tight integration with the FileMaker environment. ACF allows you to reference FileMaker calculations and variables directly within your source code. For example:

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

or

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

This approach streamlines development by seamlessly blending code with the FileMaker environment, making it highly efficient.

Another reason for not using PHP or JavaScript is that they are typically interpreted languages, which are not as fast as compiled languages. ACF, being a compiled language, offers performance benefits. Additionally, ACF's compiler checks the source code for syntax errors, ensuring that your code is free from runtime errors due to syntax issues. It also reduces dependency on external libraries, which can be OS-dependent or subject to compatibility issues. Furthermore, ACF's compiled product can be easily installed in FileMaker using a script step, providing a secure way to deploy your solution without exposing the source code.

Example of Compiled Code

This example delves into technical details. If you are not particularly interested in the code's construction, feel free to skip to the next section.

First, let's examine the compiled sequence of the small function. The following mnemonics are presented to represent the assembly-like instructions, which are, in reality, stored as integer representations. Within the runtime environment, a stack is employed to handle arguments, and a variable stack is used for local variables. These variables are assigned a numerical identifier relative to the beginning of the local variable block for the function. The instructions themselves occupy either 1, 2, or 3 memory locations, depending on their 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 establishes a table containing all the literals, which can be either strings or numbers utilized in the calculations. Instructions within the code only reference the index associated with these literals in the table. This approach allows literals to be shared among all the functions within the same file, optimizing memory usage and enhancing efficiency.

Why Not Use Processor Core Instructions Directly?

There are several compelling reasons for avoiding direct utilization of processor core instructions:

  1. Hardware Independence: If we were to directly employ processor core instructions, the runtime would become highly dependent on specific hardware configurations. By using the compiled code approach, we achieve hardware independence. The same code can seamlessly run on different types of computer hardware without requiring any alterations. This simplifies the compiler's task, as it only needs to generate a single target code. Additionally, it eliminates the need for maintaining multiple versions of the executable to accommodate diverse Mac and Windows user environments.

  2. Control and Containment: Another significant advantage lies in the degree of control we exert over executions. By encapsulating execution within the program space for the functions we create, we maintain a level of isolation and control that isn't achievable through direct processor core instruction execution.

  3. Enhanced Library Concept: The use of a higher-level language like ACF allows us to employ a more straightforward library concept. Many of the instructions within ACF are, in fact, library functions that perform complex operations. These functions often encompass more functionality than what processor core instructions alone could achieve.

While it's conceivable to further optimize performance by using processor core instructions directly, the example illustrated above demonstrates that the current approach yields remarkably fast execution speeds—beyond initial expectations.

Compare to a FileMaker Script Doing the Same

I created a regular custom function to replicate the first function, followed by a script to execute the simulation. With the inclusion of print statements (using a variable to collect text), the average execution time was approximately seven milliseconds. When I removed the text logging, the execution time occasionally improved to 4, 5, or 6 milliseconds. However, this remains significantly slower than our compiled code, which runs at approximately 500 microseconds with print statements and only 217 microseconds without them. In other words, our compiled code is approximately 23 times faster without print statements and 14 times faster with them. Despite these variations, both approaches ultimately yield the same result.

You can explore this example in the download area under the name "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 sets ACF apart from many other high-level language implementations is its seamless interaction with the FileMaker environment, a key aspect that makes ACF functions custom functions.

  1. You can utilize and assign values to standard FileMaker variables by simply referencing their names, such as $FileName or $$extraresult. While accessing these variables may not be as fast as using internal local variables, it provides an additional interface to the script executing the functions.

  2. ACF allows you to perform FileMaker calculations directly from within the ACF script. Enclose single-line calculations with "@" characters or use double "@@" for multi-line calculations.

  3. You can access field content by employing the "::" notation for field names, such as table::field, directly within internal calculations. However, it's important to note that you cannot assign values to fields directly. Instead, you can return a value from the function and use the "set field" command to update field values.

Standalone Compiler or Plugin Compiler

Initially, the compiler was developed as a standalone compiler (command-line tool) with the intention of eventually incorporating it into a FileMaker plugin, along with the runtime. Currently, the plugin includes both the runtime and the compiler, and many functions are tightly integrated into the FileMaker plugin environment. Consequently, the standalone compiler is no longer actively maintained. However, if there is a specific need, we can provide a standalone syntax checker for use in integrated development environments (IDEs) to help you syntax-check your code directly from the editor. This would be a separate project.

Conclusion

This package empowers developers to work more efficiently in a language that shares many similarities with traditional programming languages like PHP, C, 4D, BASIC, and FORTRAN. Those with experience in any of these languages should find it easy to adapt to ACF. The product promises faster development, eliminating the need to navigate through multiple modal dialogues when writing scripts in FileMaker's script editor. ACF's source code can be edited using a plain text editor such as TextMate or even Xcode, enabling smooth copy and paste operations without the interruption of modal dialogues.

Furthermore, the compiled code runs swiftly, enhancing the end user's experience with the solution. Additionally, the SQL functions in ACF allow for data retrieval, updates, or inserts without the need to reference the current layout's table occurrence.


Back to top

Syntax Differences Between ACF and Standard Custom Functions

In this chapter, we will explore some key syntax differences that are important to be aware of when coding in the ACF language.

The Use of Commas and Semicolons

In standard custom functions, semicolons are typically used to separate parameters in function calls. However, in ACF, we utilize commas for this purpose. Semicolons, on the other hand, serve as statement terminators. This approach enhances code readability, distinguishing between parameter separators and statement terminators. It's worth noting that either commas or periods can be used as decimal separators for numbers. SQL queries in ACF use periods as decimal separators.

In ACF, each statement must be terminated by an semicolon. Exceptions to this is flow control statments or terminators. Like if, else, elseif, endif, for, endfor, While, end while, repeat, until, Function and end. Look at some of the programming examples to be more familiar with the syntax.

Calculation Inliners

Unlike standard custom functions, ACF does not support inline if and case constructs. This design choice aims to improve the legibility of the source code. Instead, we recommend using if-then-elseif-else-endif structures, which can be nested to any level. These structures can be combined with loop constructs (for-endfor, while-endwhile, repeat-until). For FileMaker calculations in ACF, you can employ the "@" symbol on each side of the expression. The syntax within these constructs remains consistent with FileMaker calculations.

Declaration of Variables and Parameters

In standard custom functions and calculations, variables are always auto-declared. An undeclared variable is treated as non-existent, resulting in a return of a blank or zero value if accessed. However, in ACF, both parameters for function definitions and global or local variables must be explicitly declared. Declarations can be integrated into assignments or declared separately before usage. Importantly, declarations should not be placed inside "if-endif" or loop constructs; they must be declared at the function level. Function return values are typically auto-declared based on the type of the returned expression. In some cases, particularly with recursive functions, explicit declaration of the return type is necessary. This is achieved by declaring the function name as if it were a variable. Undeclared variables will trigger a compiler error, while assignments can auto-declare variables but prompt a warning from the compiler.

In FileMaker, data types include Text, Numbers, Dates, TimeStamps, Time, and Containers (field only). ACF introduces String as the text type, and it offers int, long, float, and double as number types. Date, TimeStamp, and Time data types remain consistent with FileMaker. However, Containers are not available in this version of the plugin. Select the data type that aligns with your specific use case: use int or long for whole numbers, and opt for float or double for numbers with decimal values. Strings in ACF can be up to 2GB in length.

In FileMaker, global variables are declared when used with double $ signs (e.g., $$MyVariable). In ACF, global variables are declared like local variables but are positioned outside functions. Global variables are not initialized with any values, so they must be assigned a value before use. They can be accessed by any other function within the current source file only. Unlike FileMaker, ACF does not have a global level shared across all ACF libraries; for such a purpose, you can use "$$" variables.

Single "$" variables in FileMaker are local to the currently executing script, and they lose their value when the script ends. In ACF, local variables are declared within functions, and they lose their value when the function returns.

Boolean Operators and Data Types

In standard FileMaker, there isn't an explicit boolean data type. Instead, any data type can be treated as a boolean. This means that a non-zero number or a non-empty text is considered true, while a zero value or an empty string is considered false. In ACF, we introduce a dedicated variable type known as bool. This type can only hold values of true or false. You cannot use any other data type as a boolean. For instance, to check if an integer variable i is true or false in ACF, you would write i != 0.

In standard FileMaker, the equality operator is represented as =, whereas in ACF, we use ==. In standard FileMaker, the "not equal to" operator is ≠, while in ACF, we support both ≠ and !=, similar to languages like C, PHP, or JavaScript. In ACF, a single = is used as an assignment operator, distinct from comparison.

Standard FileMaker utilizes the and and or operators in boolean expressions. In ACF, we employ && and || for the same purpose.

The strict datatype in ACF serves to enhance the compiler's ability to rigorously check expressions. When errors in an expression lead to type mismatches, it is more beneficial to receive an error message than to potentially overlook a flawed expression.

Function Return Values

In standard custom functions, the value of the calculation is automatically returned. In ACF, it is imperative to explicitly specify what to return using the "return" statement. A function can have multiple "return" statements, indicating that no code beyond the "return" statement will be executed within that function. If no "return" statement is present, an empty value will be returned. When using multiple "return" statements, the type of expression should be consistent across all "return" statements. For example, if one "return" statement returns a string, all other "return" statements in that function should also return strings.

The Array Type

Standard custom functions in FileMaker lack built-in support for arrays. Repeated fields and variables are the closest alternatives for managing multiple values. In contrast, ACF introduces a comprehensive array type. All data types in ACF can be declared as arrays, utilizing square brackets to specify the array index. If the left side of an assignment includes empty square brackets, a new index is added to the array.

FileMaker supports multiline variables, where each line in the text corresponds to a numbered value. ACF preserves this functionality with the "getValue" function. ACF arrays are effectively employed with functions like "explode" and "implode," facilitating the conversion of CSV-formatted data into arrays and vice versa. This proves particularly useful when handling SQL results and import/export operations.

Managing Multiple Functions in the Same File

In FileMaker, custom functions can be managed under "Manage/Custom Functions," with each function having its separate editor. ACF adopts a different approach. A "Function" statement marks the start of a function, followed by a list of parameters enclosed in parentheses. Each parameter must be prefixed with its data type. The function concludes with the "end" statement.

The ACF file begins with a "Package" statement that specifies the package name. This step is necessary when working with multiple packages in the plugin to determine which package is replaced when reloaded or recompiled. Although ACF supports functions with the same name across several packages, it's generally not recommended. Having multiple packages implement a function with the same name can make it challenging to identify which version of the function is called from FileMaker.

Calling functions convention

In Standard custom functions, other functions can be called just by their name. This is also true for ACF function called from inside the functions. From Filemaker calculations, there is two ways to call ACF functions. Either by ACF_Run("FunctionName"; Parameter1;Parameter2;....), or if the ACF function is given a unique FunctionID, using the FunctionID statement, they can be called using ACFU_FunctionName(Parameter1; parameter2; ....). The Prefix ACFU_ is assigned in order to avoid naming conflicts with other standard functions. From the ACF functions, FileMaker standard custom functions can be called using the @-sign on each side of the function call. Alternatively, using the eval function. For multiline FileMaker calculations, you can use double @ signs on each side.

We hope this clears up some of the differences between standard custom functions and ACF.


Back to top

ACF Source Package Names

The Package Name plays a pivotal role in identifying a package within ACF. You have the flexibility to install multiple packages, but it's imperative that each package possesses a unique name. Should you attempt to compile a package with a name identical to one that's already installed, the new package will overwrite the existing one.

In the plugin function "ACF_GetAllPrototypes," you gain access to a comprehensive list of all installed packages and the prototypes for functions contained within them. The Package name and its description are displayed before listing the functions contained in each package.

It's crucial to note that a package must have a designated name; otherwise, you'll receive a compilation warning, and the Package name will default to "STD." Consequently, several nameless sources cannot coexist within the plugin.

The Package name is the first command in the source file and follows this format:

package <name of package> "<a short description>"; 

function blabla....

Example:

package MarkDownFunctions "MarkDown funksjoner for dok prosjektet..."; 

function post_processing (string htmlfile)

    print "post processing\n";
    ..
    ..
    ..

In this example, the package name "MarkDownFunctions" is provided along with a brief description of its purpose.


Back to top

Mac and Windows Compatibility

The ACF Plugin is compatible with both Mac and Windows operating systems. It offers the advantage of running the same compiled code seamlessly on both platforms without the need for any alterations. This cross-compatibility extends to the source code as well, allowing it to be compiled on one platform and executed on the other. However, it's essential to be aware of some platform-specific considerations.


Back to top

Future Development Ideas

Here are some exciting ideas for future development of the ACF Plugin:

Other Ideas

Here are additional ideas that could enhance the ACF Plugin's functionality and development experience:

These innovative ideas hold the potential to further enhance the functionality and developer experience within the ACF Plugin.


Back to top

Known Issues

Updated: 15.09.2023 - Plugin version 1.6.3.0

This section provides a list of known issues that are currently being addressed. As these issues are resolved, they will be removed from the list. Please refer to the following issue:

Missing commands:

string dformat; // Global variable declared outside of any function

function SQL_to_timestamp ( string s )
    timestamp ts;
    if (dformat == "") then
    // Autodetect date format used in FileMaker.
        dformat = @date(11;12;2023)@;
        dformat = substitute ( dformat, "11", "%m");
        dformat = substitute ( dformat, "12", "%d");
        dformat = substitute ( dformat, "2023", "%Y");
    end if

    date d = date ( left(s,10), "%Y-%m-%d"); 
    s = string ( d, dformat) + substring ( s, 10); 
    ts = s; 
    return ts  ; 
end

Stay tuned for updates and improvements as we continue to enhance the ACF Plugin.


Back to top

Basic Operations

Programming Basics

In this chapter, you will find the basic constructs and some basics for programming in the ACF-language.

Structure

The structure in the source file is as follows:

Package <package name> "Description - can be several lines"

<global declarations within the package>

function firstfunction ( <declaration of parameters, comma-separated> ) 
    <Declarations and statements>
end

function nextfunction ( <declaration of parameters, comma-separated> ) 
    <Declarations and statements>
end

...
...

function lastfunction ( <declaration of parameters, comma-separated> ) 
    <Declarations and statements>
end

Function Declaration

The function declaration consists of the keyword "function" followed by a parameter declaration list enclosed in parentheses. All the parameters must be declared by type.

function abc ( int a, float b )

It is usually not necessary to declare the return parameter since the return statement in the function auto-declares the return value. However, if needed, you can have a standard declaration of the function name itself below the initial function declaration.

To register the function as a plugin function, you can use the FunctionID command inside the function, generally near the top.

Variable Declarations

Declarations are the same for local and global, with one exception. Global declarations cannot have an initial value. All program execution takes place inside functions, and thus, they have to initialize the globals they use. The types can be found in the Datatypes document.

Variable names are not case-sensitive, start with a letter between 'a' and 'z,' can contain numbers and underscores. Variables cannot start with a '$'-sign, as those are FileMaker variables, and the FileMaker evaluation engine is used to evaluate them. It's faster to use local or global variables where you can, as the plugin evaluates them, and we do not need the extra time to call the FM evaluation engine to get or set their value.

Global variables:

<type> global1, global2, global3;

Local variables:

<type> local1, local2, local3;

Or local variables with initializers (can be mixed with non-initializers) - integer variables in this example:

int local1 = 1, local2 = 33 * 4, local3 = 200, local4;

Strings with initializers. The variable svar2 will contain 80 dashes as a string multiplied by a number that repeats that string the given number of times.

string svar = "abc", svar2 = "-" * 80, svar3, svar4;

Local variables that do not have any initializers will be set to zero. Variables can be declared anywhere before they are used. However, they cannot be declared inside if- or loop constructs. Move the declaration outside such constructs to ensure that they are initialized before use.

Parameter Declarations and Local Declarations

Declaration of a parameter-list (cannot have initializers) are locals. You can write to them, but this will not affect the value of the variable in the calling program. Parameters are then read-only but can be modified locally in the function. Local variables are gone when you come to the end statement. The scope of locals is inside the function where they are declared, after the declaration.

Auto Declarations

In assignment or loop control variables, they can be auto-declared. The type of the variable will be that of the calculation result. The compiler will issue a warning about auto declarations. The purpose of this warning is to alert about potentially misspelled variables.

Example of misspelled variable:

int default_size;
defalt_size = 23;
WARNING Auto declared 'defalt_size' as INTEGER at Line 254, pos 22.

Here you see that the assignment statement warns about the auto declaration, where the variable was misspelled. Variables on the right side of "=" must be declared or previously auto-declared. Undeclared identifiers used here will give fatal errors (no produced code).

Loops

There are three types of loops.

  1. For loops
  2. While loops (while)
  3. Repeat until loops.

For loops

for (<loop control identifier> = <start value>, <end value>, <optional step value>)
    <statements>
end for

The "end for" can also be spelled "endfor."

Example:

int i;
for (i = 1; i <= 10)
    a = b * c + i;
    c++;
end for

Here, the loop runs ten times, with 'i' running from 1 to 10, and 'a' is always calculated.

While loops

While loops are much like the FileMaker script step "loop - end loop."

while (<while expression>)
    <statements>
end while

Example 1:

bool finished = false;
while (!finished)
    <some statements>
    ...
end while

Example 2:

int a, b = 3, c = 4;
while (a < c)
    a = b * c;
    c = 4 * a;
end while

Repeat...Until loops

Repeat until loops differ from while loops in having the condition at the end instead of the beginning, meaning that the variables of the condition do not need to have a value before the loop.

repeat
    ...
    <some statements>
    ...
until <exit expression>;

Currently, there is no equivalent to the "exit loop if" statement in FileMaker scripts. Instead, use if-constructs to avoid execution of the remainder of the loop if you find you are finished somewhere in the middle. Some languages have a "break" statement feature, which is planned for a later release of the plugin.

Conditionals

IF...THEN...ELSE...END IF

The IF structure is pretty standard and found in most programming languages. Note the "then" at the end of the lines of "if" and "elseif" statements.

Syntax:

if ( <boolean expression> ) then
    <statements>
    ...
elseif ( <boolean expression> ) then
    <statements>
    ...
elseif ( <boolean expression> ) then
    <statements>
    ...
...
else
    <statements>
    ...
end if

The elseif and the else parts are, of course, optional. You can have as many elseif statements as needed. You can nest "if" constructs to any desired number of levels, along with other structural parts.

CASE / SWITCH Statements

At this point, the implementation of case structures would be just a variant of the "IF" structure and seems not to be needed. Maybe in a later revision of the product. We could probably gain some readability in the code, but not much more.

Calling Other ACF Functions

Other ACF functions in the same package can be called by using their name and parameters directly. The function needs to be defined before it is used (above in the package). For recursive calls, it might be necessary to declare the type of return value explicitly. We have planned to also allow this calling convention for functions in other packages too, but for now, the @ACF_run(....)@ method must be used for functions in other packages. The

type and number of parameters must match the declaration of the function.

All the ACF functions are functions. They return values using the return statement and must be called as functions. There is no procedural call convention where the return value is not stored or used. Even if you don't intend to use the return value, it must be assigned to a variable.

Example:

function abc ( int a ) 
   ...
   ...
  return a * ....; 
end

function def ( ... )
   int z, f; 
   f = 33; 
   z = abc ( f ); // This is OK
   z = 3 * abc ( f ) - 32; // This is also OK
   // But this procedural method will fail
   abc ( f);
   ...
   return ...; 
end

There is very little overhead in calling other ACF functions in the same package, and it can be a good programming practice to put repeating segments of code into separate functions. As they appear in the same source file, it is easy to follow the program logic where the use and the definition are close to each other.

Such internal functions will also appear in the ACF_GetAllPrototypes and thus can be called from FileMaker calculations directly. If this is not desired, we encourage you to use the output from this function in your package documentation, and edit this list to remove the entries that you don't want to make available from FileMaker Calculations.

Functions and the Return Value

The function returns its value with the return statement.

return myresultvariable;

The return statement must be inserted before the END, but you can have as many as you like. However, the type of result is essential and should be the same for all return statements in a single function.

When one function invokes another function as part of a calculation, the compiler needs to know the result type to create the correct code. The return statement implicitly declares the return type of the function.

For example:

int a = 2, b = 4; 
return a / b;

As a divide operation always creates a DOUBLE result, this function will have DOUBLE as its return type.

int a = 2, b = 4; 
return a * b;

This one returns an INTEGER result, so the return type is INTEGER.

Return Value and Recursion

If you create a recursive function, the compiler does not know the return type before it comes to the recursive call. To solve this, you can declare the return type of the function explicitly.

The standard custom functions in FileMaker do not have loops, so loop techniques were created using recursion instead. Each iteration in the loop was a new recursive call. This technique is not necessary here, as we have loops. Loops are far more efficient and easier for the human eye to follow.

However, recursion used wisely can have its advantages in some special cases, like backtracking algorithms or dealing with hierarchical data structures, and so on. It is often used in conjunction with loops.

function calcthisandthat (int bb, float cc, string abc) 
    float calcthisandthat; 
    ...
    cc = calcthisandthat ( bb + 1, cc - 1, "next rec.."); 
    ...
    return bb * cc; 
end

Error Handling

There are two main methods for aborting a function due to invalid parameters or other error conditions. In the middle of some function, you find that there is an error, and further execution of the function is impossible.

One way to do it is to use the RETURN function with an error code/string.

However, that only returns the current function. If this is the function called from FileMaker directly, it's, of course, good enough. If you have a call hierarchy: Let's say you have function 'a', calling 'b', that then calls 'c'; the function 'c' discovers an error and wants to call off the whole thing. Instead of having 'b' check the result of 'c', and then 'a' check the result of 'b', you could use the "throw" statement, which discards the whole call hierarchy and returns the error to the FileMaker ACF_run function.

Example:

function c (...) 
    ...
    ...
    if ( abc > 10000 ) then
        throw "ERROR: Too great value of abc"; 
    end if
    ...
    ...
end

function b ( ...)
    ..
    ..
    c ( ... ); 
    ..
    ..
end

function a ( ...)
    ...
    ...
    b ( ... ); 
    ...
    ...
end 

// From the FileMaker Script: 
Set Variable ( $xx ; ACF_run ( "a"; .....))
if ( LeftWords ( $xx; 1) = "ERROR" )
    Show Custom Dialog ( ....)
    Halt Script
end if


Back to top

Literals

There are four kinds of literals in ACF:

Literals with Special Meaning:

Some literals have special meaning and trigger the FileMaker evaluation engine to retrieve their content. These literals are in addition to the "eval" function, which takes a string expression as an argument. The literals with special meaning are:

Data Types

In ACF, various data types are used to represent different kinds of values. Here is a list of ACF data types along with their corresponding C++ types and descriptions:

Type Name C++ Type Description
int int Integers, hardware-dependent 32-bit or 64-bit integer values. The actual size can be obtained with the size(a int variable) function in bytes.
long long Long integers. The actual size can be obtained with the size(a long variable) function in bytes.
float double Floats (these are actually represented as doubles).
double double Double-precision floating-point numbers.
string std::string String datatype, with maximum length depending on the implementation (can be very large).
date time_t Timestamp with only the date part.
time time_t Timestamp with only the time part.
timestamp time_t Full timestamp including date and time.
bool int Boolean values represented as 0 (false) or 1 (true).

Operands

Operands are values or variables operated on by operators to produce a result. Here are the operators available in ACF, along with their left and right operand types and the resulting data type:

Operator Left Type Right Type Result Type Description
+, -, * Numeric Types Numeric Types Float, Long, Int Basic arithmetic operations.
/ Int, Long, Float Int, Long, Float Float Division always results in a float.
++ Numeric Types - Same Increment left operand by one.
-- Numeric Types - Same Decrement left operand by one.
+ String All String Concatenate strings or convert non-string values to strings and concatenate.
+ Date Numeric Date Add days to a date.
+ Time Numeric Time Add seconds to a time.
* String Int, Long String Repeat the left string right times.
^ Int, Long, Float Int, Long, Float Float Exponentiation (power).
&& Bool Bool Bool Logical AND.
|| Bool Bool Bool Logical OR.
== All All Bool Equal comparison.
< All All Bool Less than comparison or string alphabetical order.
> All All Bool Greater than comparison or string alphabetical order.
<= All All Bool Less than or equal comparison.
>= All All Bool Greater than or equal comparison.
!= All All Bool Not equal comparison.
≠ All All Bool Not equal comparison (alternative syntax).

Priority of Operands

The priority of operands defines the order of evaluation in expressions. Higher-priority operands are evaluated first. To change the priority, you can use parentheses in expressions. A factor can be negated with a "-" symbol in front of it, or "!" for boolean types, affecting only the next factor in the evaluation. For negating an entire expression, you must use parentheses.

Example:

a = !a || b;

This negates "a" but not the entire expression. To negate the whole expression:

a = !(a || b);

Here's the priority list of operands, from highest to lowest:

  1. Power (^)
  2. Multiplication (*) and Division (/)
  3. Addition (+) and Subtraction (-)
  4. Comparison operators (<, >, <=, >=, ==, !=, ≠)
  5. Logical AND (&&)
  6. Logical OR (||)


Back to top

Function: Standard Functions

The standard function repertoire includes various mathematical, date-related, and utility functions. These functions are calculated by the plugin itself, eliminating the need to rely on the FileMaker calculation engine. While these functions can be part of a formula, they are calculated within the plugin.

It's worth noting that the FileMaker calculation engine provides additional functions, which can be used alongside ACF functions by using the "@...@" notation, including calls to other plugin functions. Additionally, type names can be used as functions to convert between different data types. You can also work with date and timestamp formats by using format strings with the date and timestamp functions. For more complex operations, the string functions can be used to convert between date, timestamp, and string representations.

Here's the general prototype for these functions:

x = <function name>(<argument expression>);

Examples:

float x = sqrt(44); 
// x will be: 6.6332495807108

// Calculate the length of the hypotenuse using the Pythagorean theorem:
float sidea = 14.0, sideb = 10.0; // These are in inches. We want sidec in cm.
float sidec = 2.54 * sqrt(sidea^2 + sideb^2); 
// sidec will be 43.6998123565767 cm.

// If retrieving a field value in the calculation, type conversion is necessary as all variables and fields from FileMaker are treated as strings.
float x = 2.54 * sqrt(float(preferences::sidea)^2 + float(preferences::sideb)^2);

Functions

Function Name Argument Type Result Type Number of Parameters Description
sqrt Numeric Types Double 1 Calculate the square root.
sin Numeric Types Double 1 Calculate the sine.
cos Numeric Types Double 1 Calculate the cosine.
tan Numeric Types Double 1 Calculate the tangent.
asin Numeric Types Double 1 Calculate the arcsine.
acos Numeric Types Double 1 Calculate the arccosine.
atan Numeric Types Double 1 Calculate the arctangent.
fact Numeric Types Double 1 Calculate the factorial.
open FileName, AccessMode Int 2 Open a file with the specified access mode.
close Int File Number - 1 Close a previously opened file.
read Int File Number String 1 Read the content of an opened file.
readline Int File Number String 1 Read the next line from an opened file.
write Int File Number, String Double 2 Write to an opened file.
mod Numeric Types Double 1 Calculate the remainder (modulo).
floor Numeric Types Long 1 Remove decimals and convert to a long integer.
length String Int 1 Calculate the length of a string.
left String, Int n String 2 Retrieve the left part of a string with n characters.
right String, Int n String 2 Retrieve the right part of a string with n characters.
mid String, Int start, Int n String 3 Retrieve a middle part of a string, starting at a specified position and including n characters.
pos String Source, String Substr Int 2 Get the position of a substring in the source string.
between String Source, String From-String, String To-String String 3 Return the string between two specified substrings.
substring Numeric Types Double 1
substitute String Src, String A, String B String 3 Substitute occurrences of A with B in the source string.
round Numeric Types, Int Dec Double 2 Round a number to a specified number of decimal places.
abs Numeric Types Double 1 Calculate the absolute value.
eval String String 1 Evaluate a FileMaker expression.
format String Format, ... String n Create a string with mixed content using the format function.
ValueCount String Int 1 Return the number of lines in a string.
GetValue String, Int n String 2 Retrieve line number n from a string.
PatternCount String Src, String Pattern Int 2 Count the number of occurrences of a pattern in a string.
sizeof Array Types Int 1 Return the size of an array (number of indexes).
file_exists String Path Bool 1 Check if a file at the specified path exists (true or false).
directory_exists String Path Bool 1 Check if a directory at the specified path exists (true or false).
delete_file String Path String 1 Delete a file at the specified path and return "OK" on success.
create_directory Path String 1 Create one or more directories recursively and return "OK" on success.
list_files String Path String 1 List files in a directory at the specified path.
delete_directory String Path String 1 Delete a directory at the specified path and return "OK" on success.
temporary_directory - String 0 Return the path to the temporary directory.
desktop_directory - String 0 Return the path to the user's desktop directory.
documents_directory - String 0 Return the path to the user's Documents directory.
home_directory - String 0 Return the path to the user's home directory.
applicationsupport_directory - String 0 Return the path to the user's application support directory.
file_size Numeric Types String Path 1 Return the number of bytes in a file at the specified path.
move_file String From, String To String 2 Move or rename a file from one location to another and return "OK" on success.
copy_file String From, String To String 2 Copy a file from one location to another and return "OK" on success.
select_file String Prompt, String Start-Path String 2 Present a file selection dialog and return the selected file's path, or an empty string if canceled.
select_directory String Prompt, String Start Directory String 3 Present a directory selection dialog and return the selected directory's path, or an empty string if canceled.
savefiledialogue See savefiledialogue String 3 Present a Save file dialog and return the selected file's path without creating the file.
lower String String 1 Convert the argument to lowercase letters.
upper String String 1 Convert the argument to uppercase letters.
from_utf String String 1 Convert from UTF8 to other character set encodings.
to_utf String String 1 Convert from other character set encodings to UTF8.
now - Timestamp 0 Get the current timestamp in UTC.
usec - Long 0 Get the number of microseconds since ACF_run started.
char Int String 1 Return a string containing one character with the specified ASCII code.
ascii String Int 1 Return the ASCII code of the first character in the argument.
explode String Delimiter, String Src Array String 2 Split a string into an array using the specified delimiter.
implode String Delimiter, Array String String 2 Combine an array of strings into a delimited string using the specified delimiter.
markdown2html String, String, String Double 3 See Markdown functions.
mdsnippet - - - See Markdown functions.
regex_match - - - See Regular Expressions.
regex_search - - - See Regular Expressions.
regex_extract - - - See Regular Expressions.
regex_replace - - - See Regular Expressions.
sql_query - - - See SQL functions.
sql_getrow - - - See SQL functions.
executesql - - - See SQL functions.
sql_getrowcount - - - See SQL functions.
sql_getcolumncount - - - See SQL functions.
sql_close - - - See SQL functions.


Back to top

Global Variables

Global variables are variables declared outside of functions and are accessible throughout the entire package. While they have specific use cases, it's generally discouraged to overuse them. Global variables can lead to issues when multiple processes access them simultaneously, potentially causing bugs in your application.

One common problem arises when a field calculation uses an ACF function that modifies global variables, and another function references this field directly or through a SQL function. In this scenario, the field calculation triggers while still within your ACF function, leading to changes in the global variables.

Here are some important considerations regarding global variables:

Alternatives to Using Globals

One alternative to global variables is to use FileMaker's $$ variables. These variables can be set and read by script steps directly and can also be viewed in the Data Viewer. However, accessing $$ variables from ACF functions may be slower than using built-in globals, as it involves the FileMaker calculation engine to set and get values. Therefore, using $$ variables in loops with a high loop count is discouraged. While $$ variables are shared between packages, they are local to the FMP file. FileMaker variables persist even after the ACF package is reloaded, while ACF globals do not.

ACF globals can be used to transfer global FileMaker variables from one FileMaker file to another running in the same process. This can be achieved by having setter and getter functions in ACF called from each file. Alternatively, you can traditionally share globals between files using global fields in tables.

Example:

package test_globals    "Here we test the globals";

// Some global declarations. 
string g_test1, g_test2; 

// Functions using them. 
function aa (string a)
    // And some local variables inside the function: 
    string cc2 = "TestString local"; 
    g_test1 = a; 
    g_test2 = a*2 + cc2; 
    return g_test2; 
end

function bb ()
    // g_test1 and g_test2 are available here, but cc2 is not. 
    print g_test1+"\n"+g_test2; 
    return "OK"; 
end

// Transfer globals between files
// ----------------------------------------------
string UserID, UserName, Initials; 

// Setter: 
function SetUserInfo ()
    UserID = $$sys_user_id; 
    UserName = $$sys_user_name; 
    Initials = $$sys_initials; 
    return "OK"; 
end

// Getter - called from the other file. 
function GetUserInfo ()
    $$sys_user_id = UserID; 
    $$sys_user_name = UserName;  
    $$sys_initials = Initials; 
    return "OK"; 
end

This example demonstrates the use of global variables within an ACF package, including their declaration, usage within functions, and a mechanism for transferring global FileMaker variables between files running in the same process.

This section explains the concept of global variables, their use cases, and considerations when working with them. It also provides an example of using global variables within an ACF package.


Back to top

Function: FileMaker Calculations

The core idea behind creating the ACF language is to seamlessly integrate FileMaker calculations into the language. This integration includes the following aspects:

Examples:

// Get value from a FileMaker variable:
string fileName = $$FileName;

// Set a value back to a FileMaker variable:
$$FileName = fileName;

// Get Value from a field:
string rootPath = preferences::docRootPath;

// Get value from a field by constructing the table occurrence:
string PreferencesTable = "Orders_preferences";
rootPath = eval(PreferencesTable + "::docRootPath");

// Perform a FileMaker Calculation
string tempfolder = @Get(TemporaryPath)@;
// Alternatively, we have our own function.
tempfolder = temporary_directory();  // tempfolder already declared above....

// Performing a multi-line FileMaker calculation; if the calculation contains the @-sign, 
// one must use this for One-liners as well.
string SQLresult = @@ExecuteSQL ( "Select blabla.....
WHERE blabla....
ORDER BY bla bla...."; "||"; "|*|")@@;

// Or - we could simply use the plugin's SQL-function directly without using the FM-engine:
string SQLresult = ExecuteSQL ( "Select blabla.....
WHERE blabla....
ORDER BY bla bla....", "||", "|*|");

// If you need to construct the calculation, you must use the "eval" function.
// as the @-notation expects a constant string.
int day = 14, month = 11, year = 2018;
string result = eval ( "Date(" + month + ";" + day + ";" + year + ")");

// or easier using the format function.
result = eval ( format ( "Date(%d;%d;%d)", month, day, year) ) ;

When working with FileMaker calculations, it's advisable to use the data viewer to construct the calculation. You can then paste it into your ACF code. This allows you to test and verify the expected results before integrating it into your code.

Regarding the @-notation or @@-notation, the content between the @ symbols is treated as a string literal and evaluated as a calculation by FileMaker when used in ACF functions. You cannot manipulate the content at runtime as it is executed immediately. To construct something for evaluation or include parameters, you can use the eval function, which evaluates a string expression.

One method to deal with variable content in these notations is to set FileMaker variables with the content you want and then use those variables in the calculation.

Error Handling:

FileMaker evaluations sometimes return a "?" if something goes wrong. In such cases, ACF will throw a runtime exception, aborting the function call, and returning a proper error message. This message includes the function it attempted to execute in plain text. These errors can sometimes be easy to overlook in regular scripting until you notice that something isn't working. It's essential to catch and handle these errors appropriately in your code.

Example:

ERROR: The FileMaker Evaluation returned an error (?) for the expression: Date ( 2018; 12; 9)

This section provides guidance on integrating FileMaker calculations seamlessly into your ACF code, highlighting important considerations and error handling practices.


Back to top

Function: Value Converters

ACF includes a set of common value converters that perform inline calculations. These converters are used just like functions, allowing you to easily convert values between different units and scales. Here are some examples of how to use value converters:

Example

// Calculate the distance to the moon - assuming it's 30 light seconds away
function moon_distanceKm()
    return lighty2m(1 / (365 * 24 * 60 * 2)) / 1000;  // 30 seconds
end

// Show temperature in three different units
function displayTemp(float temp)
    return format("Temp Celcius: %.1f, Farenheit: %.1f, Kelvin: %.1f", temp, c2f(temp), c2k(temp));
end

Value Converters

Here is a list of commonly used value converters in the ACF language:

Distance

Function Name Type of Argument Type of Result Formula Description
in2cm numeric double arg*2.54 Inches to Centimeters
cm2in numeric double arg/2.54 Centimeter to Inches
chain2m numeric double arg*20.1168 Chain to Meters
fathom2m numeric double arg*1.8288 Fathom to Meters
furlong2m numeric double arg*201.168 Furlong to Meters
leage2m numeric double arg*4828.032 Leage to Meters
lighty2m numeric double arg*9460730472581000.0 Light year to Meters
m2chain numeric double arg/20.1168 Meters to Chain
m2fathom numeric double arg/1.8288 Meters to Fathom
m2furlong numeric double arg/201.168 Meters to Furlong
m2leage numeric double arg/4828.032 Meters to Leage
m2lighty numeric double arg/9460730472581000.0 Meters to Light year
angstrom2m numeric double arg*1.0E-10 Angstrom to Meters
m2angstrom numeric double arg/1.0E-10 Meters to Angstrom
au2m numeric double arg*149597870700.0 Astronomical unit to Meters
m2au numeric double arg/149597870700.0 Meters to Astronomical unit

Volume

Function Name Type of Argument Type of Result Formula Description
fldr2ml numeric double arg*3.696691195313 Fluid dram (fl dr) to milliliter (ml)
floz2l numeric double arg*0.0295735295625 Fluid ounce (fl oz) to liter (l)
UKfloz2l numeric double arg*0.0284130625 Fluid ounce (UK)(fl oz) to liter (l)
gal2l numeric double arg*3.785411784 Gallon (gal) to liter (l)
UKgal2l numeric double arg*4.54609 Gallon (UK)(gal) to liter (l)
gill2l numeric double arg*0.11829411825 Gill to liter (l)
USdryPk2l numeric double arg*8.80976754172 Peck (US dry) (pk) to liter (l)
pt2l numeric double arg*0.473176473 Pint (liquid) (pt) to liter (l)
UKpt2l numeric double arg*0.56826125 Pint (UK) (pt) to liter (l)
USdryPt2l numeric double arg*0.5506104713575 Pint (US dry) (pt) to liter (l)
qt2l numeric double arg*0.946352946 Quart (liquid) (qt) to liter (l)
UKqt2l numeric double arg*1.1365225 Quart (UK) (qt) to liter (l)
USdryQt2l numeric double arg*1.101220942715 Quart (US dry) (qt) to liter (l)
ml2fldr numeric double arg/3.696691195313 Milliliter (ml) to fluid dram (fl dr)
l2floz numeric double arg/0.0295735295625 Liter (l) to fluid ounce (fl oz)
l2UKfloz numeric double arg/0.0284130625 Liter (l) to fluid ounce (UK)(fl oz)
l2gal numeric double arg/3.785411784 Liter (l) to gallon (gal)
l2UKgal numeric double arg/4.54609 Liter (l) to gallon (UK)(gal)
l2gill numeric double arg/0.11829411825 Liter (l) to gill
l2USdryPk numeric double arg/8.80976754172 Liter (l) to peck (US dry) (pk)
l2pt numeric double arg/0.473176473 Liter (l) to pint (liquid) (pt)
l2UKpt numeric double arg/0.56826125 Liter (l) to pint (UK) (pt)
l2USdryPt numeric double arg/0.5506104713575 Liter (l) to pint (US dry) (pt)
l2qt numeric double arg/0.946352946 Liter (l) to quart (liquid) (qt)
l2UKqt numeric double arg/1.1365225 Liter (l) to quart (UK) (qt)
l2USdryQt numeric double arg/1.101220942715 Liter (l) to quart (US dry) (qt)

Temperature

Function Name Type of Argument Type of Result Formula Description
c2f numeric double arg*9/5+32 Celsius to Fahrenheit
f2c numeric double (arg-32)*5/9 Fahrenheit to Celsius
c2k numeric double arg+273.15 Celsius to Kelvin
f2k numeric double (arg+459.67)*5.0/9.0 Fahrenheit to Kelvin
k2c numeric double arg-273.15 Kelvin to Celsius
k2f numeric double arg*9.0/5.0-459.67 Kelvin to Fahrenheit

Weight

Function Name Type of Argument Type of Result Formula Description
amu2g numeric double arg*1.6605402E-24 Atomic mass unit (amu) to gram (g)
carat2g numeric double arg/5.0 Carat (metric) to gram (g)
cental2kg numeric double arg*45.359237 Cental to kilogram (kg)
dram2g numeric double arg*1.771845195312 Dram (dr) to gram (g)
grain2g numeric double arg*0.06479891 Grain (gr) to gram (g)
hundredwt2kg numeric double arg*50.80234544 Hundredweight (UK) to kilogram (kg)
N2kg numeric double arg*0.1019716212978 Newton (earth) to kilogram (kg)
oz2g numeric double arg*28.349523125 Ounce (oz) to gram (g)
dwt2g numeric double arg*1.55517384 Pennyweight (dwt) to gram (g)
lb2kg numeric double arg*0.45359237 Pound (lb) to kilogram (kg)
quarter2kg numeric double arg*12.70058636 Quarter to kilogram (kg)
stone2kg numeric double arg*6.35029318 Stone to kilogram (kg)
troyOz2g numeric double arg*31.1034768 Troy ounce to gram (g)
g2amu numeric double arg/1.6605402E-24 Gram (g) to atomic mass unit (amu)
g2carat numeric double arg*5.0 Gram (g) to carat (metric)
kg2cental numeric double arg/45.359237 Kilogram (kg) to cental
g2dram numeric double arg/1.771845195312 Gram (g) to dram (dr)
g2grain numeric double arg/0.06479891 Gram (g) to grain (gr)
kg2hundredwt numeric double arg/50.80234544 Kilogram (kg) to hundredweight (UK)
kg2N numeric double arg/0.1019716212978 Kilogram (kg) to newton (earth)
g2oz numeric double arg/28.349523125 Gram (g) to ounce (oz)
g2dwt numeric double arg/1.55517384 Gram (g) to pennyweight (dwt)
kg2lb numeric double arg/0.45359237 Kilogram (kg) to pound (lb)
kg2quarter numeric double arg/12.70058636 Kilogram (kg) to quarter
kg2stone numeric double arg/6.35029318 Kilogram (kg) to stone
g2troyoz numeric double arg/31.1034768 Gram (g) to troy ounce

These value converters allow you to easily perform unit conversions within your ACF scripts, making it convenient to work with different units and scales.


Back to top

Function: The Format Function

The FORMAT function is used to create strings with mixed content. It takes a format string and a list of parameters to fill in the format specifiers. This function is similar to the C and C++ "sprintf" function and uses the same format specifiers. You can refer to the reference for sprintf for detailed documentation about the format specifiers.

Prototype:

<string> = format(<format string>, var1, var2, ...var-n);

Parameters:

Parameter Name Type Description
format string string A string literal or variable containing the format string.
mixed list any A list of variables or constants separated by commas. The number of parameters must match the number of format specifiers in the format string.

Return Value: Type String: A formatted string according to the format specifiers and the variables.

Commonly Used Format Specifiers:

Format Specifier Data Type Options and Description
%s string The length of the string can be specified and padded using a number between the % and s.
%d int The length of the string can be specified and padded using a number between the % and d. %10d means space-padded right-justified field of ten positions. %010d means the same but zero-padded. %-10d means space-padded left-justified string of 10 positions.
%ld long Similar to %d, but for long integers.
%f float or double %10.2f means a float with ten positions and two decimal places, right-justified. %10f means no specified length but with two decimal places.

Typical Use Cases:

The FORMAT function is commonly used for constructing return values, building SQL statements, creating FileMaker calculations, generating messages, and forming strings for data exports.

Example:

string s1 = "one", s2 = "two";
print format("This is a fixed string with three other strings in it 1:%s, 2:%s, 3:%s", s1, s2, "last is a constant string");

// Output to the console:
// This is a fixed string with three other strings in it 1:one, 2:two, 3:last is a constant string

// Format with dates in d.m.y format.
date today = now();
string formatted = format("Today is %s and tomorrow is %s", string(today, "%d.%m.%Y"), string(today + 1, "%d.%m.%Y"));
print formatted;

// Output to the console:
// Today is 12.11.2018 and tomorrow is 13.11.2018

// Converting SQL dates and using them in the format.
string sqlDate = "2018-11-12";
date myDate = date(sqlDate, "%Y-%m-%d");
print format("The SQL date is: %s, or in US format %s.", string(myDate, "%d.%m.%Y"), string(myDate, "%m/%d/%Y"));

// Output to the console:
// The SQL date is: 12.11.2018, or in US format 11/12/2018.

The FORMAT function allows you to create formatted strings with various data types and is useful for a wide range of applications, including working with dates, generating SQL statements, and formatting messages.


Back to top

Function: SQL Functions

The SQL functions in ACF come in two flavors. The first one is ExecuteSQL, which is similar to FileMaker's ExecuteSQL. While ACF does not use placeholders like "?," you can use the format function to create SQL statements with placeholders. ExecuteSQL returns a text result with column and row separators, similar to the FileMaker version. You can specify these separators as optional parameters (2nd and 3rd), with the default being a tab character for column separation and a carriage return (CR) for row separation.

One important difference: The ExecuteSQL function supports SELECT, UPDATE, INSERT, and DELETE operations. It's ideal for cases where you want to select a single value or perform UPDATE, INSERT, or DELETE operations. If you need to work with multiple values or result sets containing more than one record, consider using the following four functions.

The second flavor consists of four functions:

A handle is an integer that identifies the SQL result set. It's important to understand that it's the integer value that identifies the result set, not the handle variable itself. You can return handles to FileMaker and use them in other functions or script steps. Always remember to close the handle when you're finished with it to avoid exceptions.

Prototypes:

string resultset = ExecuteSQL (string sql {, string column_sep {, string row_sep}});
int handle = SQL_query(string sql);
int number_of_rows = SQL_getRowCount(int handle);
array string row = SQL_getRow(int handle, row_number);
SQL_close(handle);

Definition of Parameter Types:

Parameter name Type Description
sql string The SQL string to be executed.
handle int The identifier of the result set.
row_number int The row number, starting from 0.

Example:

package SQLtest1 "Test SQL Functions"

/*
    In this test, we query the Contacts table for all rows, then loop through the recordset to 
    update the "x" column with the loop counter. 
    Then, we print to the console the ID, FirstName, LastName, the update statement, 
    and the result from the update statement. 
*/
function test_listitems()

    // SQL_query executes the SQL and provides us with a handle to the result.
    // This handle must be closed at the end.
    int x = SQL_query("SELECT ID, FirstName, LastName FROM Contacts");

    int num_rows = SQL_getRowCount(x);

    print format("Number of Contacts: %d\n", num_rows);

    int i;
    string res, upd;
    array string row;
    // We avoid declaring variables inside the loop.

    for (i = 1, num_rows, 1)

        // SQL_getRow returns an array, allowing us to access each column in the recordset individually.
        // The first parameter is the handle, and the second is the row number, starting with "0".
        row = SQL_getRow(x, i - 1);

        // Create the update SQL statement with placeholders using the format function.
        upd = format("UPDATE Contacts SET x=%d WHERE ID=%s", i, row[1]);

        res = ExecuteSQL(upd); // Execute the UPDATE; no need to close with this function.
        print format("ID: %s, First name: %s, Last name: %s - SQL='%s',  Result: %s\n", row[1], row[2], row[3], upd, res);

    end for

    // Close the handle.
    SQL_close(x);

    return "OK";
end

/* Test Result:
Number of Contacts: 2032
ID: 1000, First name: Ole Kristian Ek, Last name: Hornnes - SQL='UPDATE Contacts SET x=1 WHERE ID=1000',  Result:
ID: 1001, First name: Roffe, Last name: Hansen - SQL='UPDATE Contacts SET x=2 WHERE ID=1001',  Result:
ID: 10001, First name: Trond Harald, Last name: Hansen - SQL='UPDATE Contacts SET x=3 WHERE ID=10001',  Result:
...
...
*/

NOTE: When updating large datasets in a loop, the process can be time-consuming, and FileMaker may appear unresponsive until finished. To improve user experience, consider using a progress bar function before starting this function. This allows you to display a progress indicator during the operation. Typically, it's better to run the progress bar in a separate script step outside the ACF function. Starting the progress bar outside of ACF allows the script engine to control its display properly.

Getting the Insert-ID of Newly Inserted Records

To retrieve the insert-ID of a newly inserted record (useful when inserting related records into another table), consider the following methods:

The first method may have concurrency issues if two processes insert into the same table simultaneously, so consider using the second or third method to avoid such problems.

References:


Back to top

Function: mySQL Functions

Available in Mac version 1.6.3.0 and up. Not yet available on Windows.

The mySQL functions is similar to the SQL functions, but connect to an external mySQL database instead of the internal FileMaker database. Those functions also come in two flavors. The first one is ExecuteMySQL, which is similar to the ExecuteSQL. The same parameters as ExecuteSQL, exctept an additional first parameter that is the connection handle returned from "mysql_connect". The other flavour is the the four functions used for working on SQL result set described below.

Both flavours has full support for SELECT, INSERT, UPDATE and DELETE along with other mySQL commands. It uses this commands for connection:

The second flavor consists of four functions:

A DB-handle is an integer to identify the connection itself, and neccesary for the query. It is also the first parameter of the "ExecuteMySQL" function. The other handle that is named only "handle" is an integer that identifies the SQL result set. It's important to understand that it's the integer value that identifies the result set, not the handle variable itself. You can return handles to FileMaker and use them in other functions or script steps. Always remember to close the handles when you're finished with it to avoid exceptions.

The mySQL_getRow function differ a bit from the corresponding SQL_GetRow used for FileMaker database queries: It has no record number as second parameter, as the records are fetched from start to end through a series of calls. For this command you only supply the result-set variable.

Prototypes:

int DB_Handle = mysql_connect (string host, string port, string user, string password, string database_name );
string resultset = ExecuteMySQL (int DB_handle, string sql {, string column_sep {, string row_sep}});
int handle = mySQL_query(int DB_handle, string sql);
int number_of_rows = MySQL_getRowCount(int handle);
array string row = MySQL_getRow(int handle);
MySQL_close(DB_handle);

Definition of Parameter Types:

Parameter name Type Description
sql string The SQL string to be executed.
handle int The identifier of the result set.
DB_handle int The identifier of the Database connection.
row_number int The row number, starting from 0.

Example:


// One function to do the connection, so we dont have to scatter database credentials all over the source code. 

function connect_support_db ()
    int db = mysql_connect ("<host>", "3306", "<user>", "<password>", "<database name>"); 
    return db; 
end

// Testing just print to console some content from a Support DB. 

package MySQLtest1 "Test MySQL Functions"

function print_support_emails ()
    int db; 
    db = connect_support_db (); // The function at the top 
    
    int x = mysql_query ( db, "SELECT * FROM c1support.ost_email;"); 
    int i, y = mysql_getrowcount ( x );
    print "row count: " + y + ", res-set id: " + x; 
    array string row; 
    for ( i=1, y)
        row = mysql_getrow ( x ); 
        print  row['email'] + "\n"; 
    end for
    mysql_close ( db ); 
    return "OK";    
end

References:


Back to top

Command: FunctionID

The FunctionID command allows you to register a function as a plugin function, making it accessible using just ACFU_function_name instead of ACF_run("function_name"). This is particularly useful for frequently used functions as it provides the prototype as an autocomplete option when used in FileMaker. Functions used internally as helper functions within another ACF function do not require FunctionID; it's mainly for functions intended to be used directly in FileMaker calculations.

When functions are used within another ACF function, no prefixing is necessary. The prefixing only applies when calling functions from FileMaker calculations.

Parameters:

Parameter name Type Description
ID Number constant A unique constant between 200 and 32767 that identifies the function.

It's essential to manage these function IDs systematically, maintaining a record of them in a database or spreadsheet. Avoid sharing the same function ID among different functions. Changing the function ID can break script steps that use it, so stick with the function ID once it's assigned.

When you receive ACF sources from another developer, check that their function IDs don't conflict with yours. Renumber functions from external sources if needed to prevent overlap. Avoid accepting binary packages from other developers, as it can make maintenance challenging. Future plugin releases might require you to recompile sources. Binary packages are suitable for deployment in end-user applications.

We automatically prefix all function names with ACFU_ when they are used in FileMaker script steps. This serves several purposes:

Screenshots:

ACFU Autocomplete

ACFU Prototype

Return value:

No return value

Example:

function abc ( int a, int b ) 
    functionID 10232; 
    
    return format ( "The value of a is %d, and b is %d", a, b); 
end

Then later, the function can be called using:

Set Variable ($x; ACFU_abc (2, 3) )

Pros and Cons:

There are pros and cons to registering functions with a "FunctionID." The most obvious benefit is easy access to functions in the FileMaker development environment, along with convenient access to function prototypes to fill in parameters. However, you need to ensure that your code is loaded in the startup script so that it's available during development.

The cons include the need to have functions loaded continuously while developing. If you edit a script without loading your ACF code and save it, the script will display "," and once saved, it becomes inoperative. When using ACF_run, such issues result in a runtime error only when running the script.

The ACF_GetAllPrototypes function provides a handy list of loaded prototypes, allowing you to copy and paste them into an accessible document for reference.

References:

This section explains how to use the FunctionID command to register functions as plugin functions, the benefits of doing so, and considerations when working with function IDs. It also provides examples and discusses the pros and cons of this approach.


Back to top

Function: Regular Expressions

There are four regex functions:

Prototypes:

bool = regex_match ( <regex string>, <subject> );
int = regex_search ( <regex string>, <subject> );
array string = regex_extract ( <regex string>, <subject>{, <group delimiter>} );
string = regex_replace ( <regex string>, <subject>, <replace> );

Parameters:

Parameter Name Type Description
regex string string A string containing a regular expression pattern.
subject string The text to search in.
replace string Only for regex_replace: The new string to replace, which can contain references to the groups.
group delimiter string When using regex_extract, the return value is an array with matches. If the pattern contains groups, the groups are delimited with this delimiter. This parameter is optional, and the default value is "\n" (line-feed character).

Return values:

Example:

// Replace a path with a standardized new path, keeping the filename.
string fn = "/users/ole/Documents/mytestfile.txt";
string newfn = regex_replace("^(.+)/(.+)\..+$", fn, "/users/ole/result/\2.restype");

// The newfn now contains: /users/ole/result/mytestfile.restype

Example 2: Check Email Address (RFC 5322 Official Standard):

function check_email(string email)
    string regex = "(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*|\"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*\")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])";
    return regex_match(regex, email);
end

Normalize Name Using Regex_Replace:

In this example, we normalize a name. If it's written as last name, first name(s), we reverse it and remove any commas. If there is no comma, we don't modify it.

The regex contains two capture groups: the first word and the remaining words. However, there must be a comma in between for the regex to match. If matched, we replace the whole thing with \2 (meaning the second group) and \1 (meaning the first).

function NormaliseName(string Name)
    Name = regex_replace("(\w+),\s?(.+)", Name, "\2 \1");
    return Name;
end

// Hornnes, Ole Kristian => Ole Kristian Hornnes
// Hornnes Ole Kristian => Hornnes Ole Kristian

Example RegEx_Extract:

In this example, we extract some emails and names from a list. Each entry in the resulting array has a delimited list of groups. The first is the whole match, followed by the groups. We have chosen "::" as the group delimiter in this example, but it can be anything. If you are looping through the array, you can use the explode function to create a new array for each iteration in the loop. This way, you can work on each match and put the data where it belongs.

Usually, a two-dimensional array would fit here, but that is not yet available in ACF.

Sample text:

Hornnes, Ole Kristian ole@example.no
Bjarne hansen bjarne@b.com
Borre Josefson borre@j.com

Extract Function:

Function testExtract(string source)
    source = substitute(source, "\r", "\n");
    string regex = "([A-Za-z, ]+)\s([a-z]+@[a-z]+\.[a-z]{2,3})";
    array string results = regex_extract(regex, source, "::");
    int l = sizeof(results);
    int i;
    for (i = 1, l)
        print "Line " + i + ":" + results[i];
        print "\n";
    end for
    return implode("||", results);
end

Console output:

Line 1:Hornnes, Ole Kristian ole@example.no::Hornnes, Ole Kristian::ole@example.no
Line 2:Bjarne hansen bjarne@b.com::Bjarne hansen::bjarne@b.com
Line 3:Borre Josefson borre@j.com::Borre Josefson::borre@j.com

New to Regular Expressions?

Regular expressions, often abbreviated as regex, are powerful tools for text manipulation and error checking. While they can be challenging to read for those unfamiliar with them, gaining knowledge about regular expressions can save you hours of work and improve code quality.

A regex pattern is a set of rules for interpreting string data, with each character in the pattern having a specific meaning. For those looking to learn more about regular expressions, consider the following resources:

  1. Lynda.com offers a comprehensive regex course that is well worth a few hours of your time.
  2. regex101.com provides an online regex editor and testing tool, complete with excellent documentation and explanations for each character in your regex pattern.

regex-101

References:


Back to top

Debugging Your Code

Debugging in ACF may not offer a traditional step-through debugging experience, but there are two essential features designed to help you debug your code effectively:

  1. Print Statements: You can insert "print" statements within your code to output information to the console. These print statements don't affect the function's return value but are invaluable for debugging purposes.

  2. Plugin Function ACF_GetConsoleOutput: This function allows you to retrieve the printed output and store it in a text field, variable, or any suitable storage location. The console output can also be monitored using the Data Viewer.

In addition to these features, you can assign values to global FileMaker variables to monitor plugin execution without relying on the console function. It's important to note that each call to ACF_run will reset the console.

Example:

string a = "this is a string";
int b = 22; 
float c = 100.36; 
print format ( "Variables a, b, c: %s, %d, %10.2f \n", a, b, c );

// Using the plugin function ACF_GetConsoleOutput lets you retrieve this text: 
Variables a, b, c: this is a string, 22,     100.36

In this example, "print" statements are used to display variable values in the console. You can then use ACF_GetConsoleOutput to retrieve this printed information, aiding you in debugging your ACF code.

This section explains the debugging capabilities within ACF, including how to use print statements and retrieve console output, which can be invaluable for debugging your code effectively.


Back to top

FileMaker Plugin Calls

Plugin Function: ACF_RegisterPlugin

Updated: 15.09.2023 - Plugin version 1.6.3.0

ACF_RegisterPlugin is the licensing function of the plugin. After purchasing the plugin, you will receive a license code that needs to be used with this function to unlock full permanent functionality. Without registering the license, the plugin operates fully for 30 minutes. After that period, only the ACF_RegisterPlugin function remains active. You can also obtain a free demo license that is valid for 14 days. After the demo period, the plugin will operate in demo mode for 30 minutes, and all plugin calls will return an error message for an expired license. There are two types of licenses available:

  1. Developer License: This license grants access to all plugin functions, including the ability to compile ACF code.

  2. Runtime License: The runtime license allows you to use all plugin features except for compiling ACF code. It can load and execute compiled code. Typically, you would use a developer license for your development work and runtime licenses for your clients.

When you run the ACF_RegisterPlugin function for the first time on a computer, the plugin checks the license's validity with our license server. After this initial validation, the plugin no longer requires communication with the license server. This check is primarily in place to prevent the publication of licenses on the internet. We send only the license's serial number and a SHA512 fingerprint to create an untraceable ID for the computer. No additional data is transmitted. If a license is used excessively, we may suspect it has been shared publicly, and it will be invalidated. The rightful owner will receive a new valid license that must be applied.

Mac and Windows Compatibility

The license code is valid for both Mac and Windows. The distribution package contains plugins for each platform, allowing them to run the same compiled ACF code.

Prototype:


<status> = ACF_RegisterPlugin ( <Licence_name>; <LicenceKey )

Parameters:

Parameter name  Type  Description
Licence Name  TEXT Registered name of the license
Licence Key  TEXT Unique license key provided by HORNEKS ANS

Return value: - Type TEXT: "OK" for a valid license; any other error indicates an expired or incorrect license code.

Example:


Set Variable [$Res; ACF_RegisterPlugin ( "DEMO"; "ACF_Plugin DEV1 - DEMO 1001
========LICENCE BEGIN======
QB25ZD76dmuP/xJsxlfh1LqPponjja1mQ6K3lia1uc7Otjy9BoxR6BqI0OgialQKo9Q4ZWcv
S96V56bDBY3us2EEUrLpFTd+35ebK9rXFvQXb/vL1fN+A8SypseN0zoIs78uDajP5xfi+mnu
1lC0wA
======= LICENCE END =======" )]


Back to top

Function: ACF_run

The ACF_run function allows you to execute any of your ACF functions. Once your functions are compiled or loaded, you are ready to execute them. In your code, other ACF functions can be called directly using their names; there's no need to use ACF_run from inside an ACF function to execute another one. ACF functions can call each other directly. However, if you want to call a function in a different package, you can use the ACF_run syntax enclosed in "@" signs to achieve this. In a future release of the plugin, this feature may be available directly.

The ACF_GetAllPrototypes function returns a text containing all your loaded ACF functions, along with their parameters and types, as they can be called from a FileMaker Script Step, Calculation, or directly from a button.

It's important to ensure that the number of parameters matches when calling ACF functions. If you add parameters to an ACF function, you must also update any places where you call that function from FileMaker to match the new parameter count. This behavior is consistent with regular custom functions in FileMaker.

Functions can have arrays as parameters, which work smoothly when ACF functions call each other. However, since FileMaker does not have an array data type, you cannot supply such an argument from FileMaker using the ACF_run command. In the future, there are plans to introduce a feature that allows a variable number of parameters, where the last argument to a function can be declared as an array to collect additional parameters as array cells. Currently, this feature is not available.

Prototype:

ACF_run ( <Function name>; <List of parameters according to the function declaration, separated by semicolons>)

Parameters:

Parameter name  Type  Description
Function name  string  The name of the function. You can use ACF_GetAllPrototypes to copy and paste functions directly.
Parameters  As declared  Parameters according to the declaration. The number of parameters must match. There are no default values for parameters.

Return value:

The return type and value depend on the return value and type specified in your ACF function.

Example:

// For an ACF function declared like this: 

Function CreateCID ( int InvoiceNumber, int CustomerNumber, int CIDlength)
...
...
END

// And from a FileMaker script step: 

Set Field ( Invoices::CID number; ACF_run  ("CreateCID"; Invoices::Invoice_number; Invoices::CustomerNumber, 16))

References:


Back to top

Certainly, here's the improved version:

Function: ACF_Compile

Compile a text with ACF Source Code and generate a binary Base64 encoded package that can be later installed using ACF_Install_base64Text or, if saved to a file, use ACF_InstallFile.

Upon successful compilation, the compiled package loads automatically, making the functions within it ready for execution.

Parameters:

Parameter name Type Description
Source string The ACF Source code to compile
Debug level number Debug levels from 0 to 5. 0 = only list source code compiled, 1 to 5 correspond to different levels of debug information from the compiler.

Return value:

Type TEXT : If the compilation is successful (no compile errors), the return is a base64 encoded binary that can be installed using ACF_Install_base64Text. If there are compilation errors, the return value will be a summary of those errors that need correction. You can use "PatternCount ( $res ; "=== START ===" )" to check if the compilation was successful, and then store the result only if the compilation was successful.

Example:

# ------------------------------------------
# Created by: Ole Kristian Ek Hornnes 
# Created date: 15.10.2018
# Purpose: To compile the ACF package and produce binary
# ------------------------------------------
Set Variable [ $x; Value:ACF_StartDurationTimer ]
Set Variable [ $res; Value:ACF_Compile( Preferences::Source; 0 ) ] 
Set Variable [ $$compt; Value:ACF_GetDuration_uSec ]
If [ PatternCount ( $res ; "=== START ===" )> 0 ]
      Set Field [ Preferences::ACF_Pack4; $res ]
      Set Field [ Preferences::CompilationErrors; 0 ] 
Else
      Set Field [ Preferences::CompilationErrors; 1 ] 
End If
Set Field [ Preferences::Consolle; ACF_GetConsolleOutput ] 
Set Field [ Preferences::Prototypes; ACF_GetAllPrototypes ]
Commit Records/Requests[ No dialog ]
GotoField[ ][ Select/perform ]

You can use the "compilation errors" field to display or hide two icons. One icon is displayed when the error count is 0, and the other when it's 1. Placing these icons on top of each other in the layout can indicate the result of the compilation.

Example of output on successful compile:

Advanced Custom Function Binary Package
==================================================
=== Package name: markdownfunctions
=== Description: 
MarkDown functions for the dok prosjekt...
==================================================
=== Do not change anything below this line =======
=== START ===
AAABDBYAAAAAAAAAc2VyaWFsaXphdGlvbjo6YXJjaGl2ZRAABAgECAEAAAAAAAAAAAxY/ABk
AAAAAAAAAG1hcmtkb3duZnVuY3Rpb25zAAAAEKWTXv9/AAAQpZNe/38AABClk17/fwAAEKWT
Xv9/AAAQpZNe/38AABClk17/fwAAGJaTXgMAAAAQpZNe/38AABClk17/fwAAEKWTXv9/AAA8
BQAAxgPnBAAAQgAAADkAWQAAACilk17/fyilk14opZNe/38AAEEAAAAAAAAAKKWTXv9/AABQ
ZXNzaW5nCgIAAAAAAAAAT0sPAAAAAAAAAGNvbnZlcnRtYXJrZG93bgAAAAAAAAAADAAAAAAA
....
....
....
AAABAAAAAAAAADEWAAAAAAAAAHNlcmlhbGl6YXRpb246OmFyY2hpdmUQAAQIBAgBAAAAAAAA
AAAAAAAAAAAAAAAAAAAWAAAAAAAAAHNlcmlhbGl6YXRpb246OmFyY2hpdmUQAAQIBAgBAAAA
KQAAAAAAAABNYXJrRG93biBmdW5rc2pvbmVyIGZvciBkb2sgcHJvc2pla3RldC4uLg
=== END ===

Example of output on compilation errors:

These errors are also logged in the console and can be obtained using ACF_getConsoleOutput.

Compilation errors: 1
Debug level is: 0

// 1: package MarkDownFunctions "MarkDown funksjoner for dok prosjektet..."; 
Compiling package markdownfunctions
MarkDown funksjoner for dok prosjektet...

// 2: 
// 3: 
// 4: function post_processing (string htmlfile)
// 5:   int a = fido*3; 

FATAL ERROR: Undeclared identifier fido
>At Line number 5 Position: 9

// 6:   print "post processing\n";
// 7:   int x = open ( htmlfile, "r"  ); 
...
...
..


Back to top

Function: ACF_CompileFile

Compile a text with ACF Source Code and generate a binary Base64 encoded package that can be later installed using ACF_Install_base64Text or, if saved to a file, use ACF_InstallFile.

This function is similar to ACF_Compile, but it takes input from a disk file and produces output on another file. The name of the compiled disk file will be the same as the source, but with the ".dsComp" extension.

Upon successful compilation, the compiled package loads automatically, making the functions within it ready for execution.

Prototype:


ACF_CompileFile( SourceFile {;Verbose Level} )

Parameters:

Parameter name Type Description
SourceFile string The path to the source file containing the ACF package to compile
Verbose Level int Debug levels from 0 to 5. 0 = only list source code compiled, 1 to 5 correspond to different levels of debug information from the compiler.

Return value: Type TEXT : The path to the compiled package. It is placed in the same folder as the source file, with the extension ".dsComp".

Example:


Set Variable [ $res; Value:ACF_CompileFile( Preferences::SourcePath; 0 ) ]

References: No references


Back to top

Function: ACF_GetAllPrototypes

The ACF_GetAllPrototypes function is designed to retrieve the prototype text for all installed package functions. In this context, each parameter in the prototypes includes a type prefix, using FileMaker terminology.

The primary purpose of this function is to streamline the development process and facilitate the creation of internal documentation by providing access to the prototypes of developed functions.

Have the output from this handy when you develop FileMaker scripts, so you can cut and paste into your calculations.

Prototype:

ACF_GetAllPrototypes

Parameters: No parameters

Return value: Type TEXT : The Prototype text

Example of output:

Set Field [ Preferences::Prototypes; ACF_GetAllPrototypes ]

After running the above script, the field "Preferences::Prototypes" will contain the following text, which displays the prototypes for various packages:

=========================================================
Package: bootstrap
Functions to facilitate load and save source and binary files

ACF_run("LoadFile"; text_filename)
   ==> Return type = text

ACF_run("SaveFile"; text_filename; text_content)
   ==> Return type = num

ACF_run("SelectFile"; text_startPath; text_Prompt)
   ==> Return type = text

ACF_run("SelectFolder"; text_Prompt)
   ==> Return type = text

ACF_run("SelectSaveAsFile"; text_prompt; text_defname; text_defdir)
   ==> Return type = text


=========================================================
Package: markdownfunctions
Markdown functions for the doc project

ACF_run("post_processing"; text_htmlfile)
   ==> Return type = text

ACF_run("ConvertMarkdown"; text_sourcefile; text_htmlfile; text_html_root; text_style; text_codestyle)
   ==> Return type = text

ACF_run("createHTML_Filename"; text_file; text_html_path)
   ==> Return type = text

ACF_run("openList")
   ==> Return type = num

ACF_run("readnextline"; num_fileid)
   ==> Return type = text

ACF_run("closeList"; num_fileID)
   ==> Return type = undef


=========================================================

This text provides an overview of the packages and their associated functions, making it easier to understand the available prototypes.


Back to top

The Console Function

A console function primarily serves as a debugging tool in the ACF plugin. The ACF plugin includes a function to retrieve the console, which is particularly useful during the compilation process. When compiling using the compile functions, you also receive errors and debug output in the console. The debug level used in those commands determines the amount of data retrieved.

Inserting print statements in your code can greatly aid in debugging and quality checks for ACF functions.

The console function also has the ability to reset the console. This means that you cannot retrieve the same console output twice. You can leverage this function to reset the console before executing a series of ACF function calls, effectively clearing previous console output.

Plugin Function: ACF_GetConsoleOutput

Parameters: There are no parameters required for this function.

Return Value: The return value of this function is the text added to the console, which may include errors and print statements generated by the functions.

Example:

Here's an example of how the console function can be used in a script for compiling plugin functions. The console output is placed into a field on the layout for easy monitoring of the compilation process.


# ------------------------------------------
# Opprettet av: Ole Kristian Ek Hornnes 
# Opprettet dato : 15.10.2018
# Formål:
# ------------------------------------------
Set Variable [ $x; Value:ACF_StartDurationTimer ]
Set Variable [ $res; Value:ACF_Compile( Preferences::Source; 0 ) ] 
Set Variable [ $$compt; Value:ACF_GetDuration_uSec ]
If [ PatternCount ( $res ; "=== START ===" )> 0 ]
      Set Field [ Preferences::ACF_Pack4; $res ]
      Set Field [ Preferences::CompilationErrors; 0 ] 
Else
      Set Field [ Preferences::CompilationErrors; 1 ] 
End If
Set Field [ Preferences::Consolle; ACF_GetConsolleOutput ] 
Set Field [ Preferences::Prototypes; ACF_GetAllPrototypes ]
Commit Records/Requests[ No dialog ]
GotoField[ ][ Select/perform ]


Back to top

Function: ACF_InstallFile

ACF_InstallFile allows you to install a compiled source stored in a filesystem file. However, there is an important note regarding runtime versions of the FileMaker application.

In the case of the file being located in the extensions folder of the solution and supplying the path to it via this function in a runtime environment, it appears that the runtime may unload the plugin. It seems like FileMaker actively prevents any references to the folder structure of the runtime when fed as parameters to the plugin. This behavior is specific to runtime versions.

For this reason, it is recommended to use the ACF_Install_base64Text function to load packages when working with runtime solutions.

Prototype:

ACF_InstallFile( base64_Binary_File )

Parameters:

Parameter name  Type  Description
base64_Binary_File  string  Path to the file

Return value:

Type TEXT : An empty string is returned on success, while an error message is returned on failure.

Example:

Set Variable [ $res; Value:ACF_InstallFile( "~/Projects/ACFbinaries/myACFfunctions.dsComp" ) ]

References:


Back to top

Running the Plugin on the Server

When running the plugin on the server, there are some logical limitations and considerations to keep in mind:

  1. Dialog Functions: Functions that present dialogues are not functional when running on the server. These functions are designed for user interaction and are not suitable for server-side execution.

  2. Calculation Limitations: All FileMaker calculations performed from inside ACF functions follow the same limitations as ordinary calculations. This means you should be aware of the server's calculation capabilities and avoid complex or resource-intensive operations.

To run the plugin on the server, you need to register the plugin and load the ACF binaries of your compiled ACF functions. This registration process should be performed only once and not with every Perform Script on Server (PSoS) operation. Since multiple PSoS processes can run on the server concurrently, reloading the ACF binaries during an ongoing process can lead to issues or interruptions in functionality.

To avoid unnecessary reloading of ACF binaries, you can use a simple method:

  1. Create a basic ACF function like this:
function AreWeLoaded()
    return "Yes";
end
  1. At the beginning of your PSoS script, check if the ACF binaries are already loaded using the AreWeLoaded function:
If [ ACF_run("AreWeLoaded") ≠ "Yes" ]
    Set Variable [ $reg ; Value: ACF_RegisterPlugin( "Company"; "License data..." ] 
    Go to Layout [ “Preferences” (Preferences) ; Animation: None ]
    Set Variable [ $$ACF ; Value: Let( [ v = If (not IsEmpty ( Preferences::ACF_Pack1) ; ACF_Install_base64Text( Preferences::ACF_Pack1 ); "") ..........
End If

This method ensures that the ACF binaries are loaded only when necessary, preventing unnecessary reloads and potential issues with concurrent PSoS processes.

Access Mode for the Plugin on the Server:

Follow FileMaker's instructions for installing the plugin on the server, and make sure to set the access mode correctly to allow loading and execution:

# Open terminal
cd /path/to/Server/extensions/directory
chmod g+wrx <filepath>

Note: On Windows servers, ensure you use the 64-bit version of the plugin, which is named ACF_plugin.fmx64 and is included in the download package.


Back to top

Function: Compiling using literal source code in the calculation

In FileMaker, multi-line text literals do not contain line separators; they consist of just a single space character. This behavior can make it challenging to include source code or base64 binary directly within a calculation. It's recommended to store source code or base64 binaries in FileMaker text fields. However, if you must include source code in a calculation, you can prepare it by performing some substitutions in a text editor.

Follow these steps to prepare source code for inclusion in a calculation:

  1. Replace all line separators with the characters "¶" and carriage return (¶\n).
  2. Replace double quotes with single quotes.
  3. Replace all backslash characters with double hash signs (##).

Then, when you call the ACF_Compile function, reverse these substitutions.

Example

ACF_Compile( Substitute ( "package bootstrap 'Functions to facilitate load and save source and binary files¶
¶
...
...
...
"; ["¶";Char(13)]; ["'";"\""]; ["##"; "\\"]) ;0 )

In this example, we demonstrate how to prepare source code for the "bootstrap" package using the ACF_Compile function. We perform the necessary substitutions to handle multi-line text literals correctly within a calculation.

In the demo files, you can find a similar statement in the startup script. This is especially helpful when many buttons and scripts rely on the "bootstrap" package. Having it in place from the start simplifies the implementation in a target application.

Startup Script Example

If [ ACF_run("bootstrap_AreWeLoaded") ≠ "Yes" ] 
    # Because string constants in FM do not have proper newlines, we perform some substitutions on this prepared source. 
    Set Variable [ $res ; Value: ACF_Compile( Substitute ( "package bootstrap ...."; ["¶";Char(13)]; ["'";"\""]; ["##"; "\\"]) ;0 )
End If

By following this approach, you can include source code directly in your calculations while ensuring proper handling of line separators, quotes, and backslashes.

This method allows you to incorporate source code into your calculations while maintaining proper formatting and escaping.


Back to top

Array functions

About Array Functions

Array functions were introduced in ACF to simplify programming with fields and datasets. In this version of ACF, we support one-dimensional arrays exclusively. Arrays can significantly streamline certain programming tasks.

Declaration:

array string myArr;     // Declared with zero elements. 

// or an array with five elements: 
array string myArr = {"ell1", "ell2", "ell3", "ell4", "ell5"};

Getting the value count for an array:

array string myArr = {"ell1", "ell2", "ell3", "ell4", "ell5"};
int x = sizeof(myArr);  // should be 5

Adding elements to an array: 

array string myArr = {"ell1", "ell2", "ell3", "ell4", "ell5"};
myArr[] = "ell6";

Accessing individual cells in an array:

array string myArr = {"ell1", "ell2", "ell3", "ell4", "ell5"};
print myArr[3];  // Should print "ell3" on the console.

You cannot set individual cells outside the currently counted cells; otherwise, you will encounter an "array index..." error.

Merging two arrays:

array string myArr = {"ell1", "ell2", "ell3", "ell4", "ell5"};
array string myArr2 = {"ell6", "ell7", "ell8", "ell9", "ell10"};

array string myArr3 = {myArr, myArr2}; // All ten elements. The list may contain constant strings or single variables too, merged into myArr3.

Also, consider exploring the Explode and Implode functions for more array manipulation options. The markdown example contains numerous array-related illustrations as well.


Back to top

Function: Explode

The explode function is used to create an array from a delimited string. It splits the input string into individual elements separated by a specified delimiter. This function is particularly useful when dealing with output from SQL queries or other scenarios where data is structured as a delimited string.

Parameters:

Return value:

Type array string: An array where each cell contains one part of the original string.

Example:

string source = "Element1,Element2,Element3"; 
array string parts = explode  ( ",", source); 
int s = sizeof ( parts ); 
int i; 
for (i = 1, s)
    print "Part " + i + ": " + parts[i]+"\n"; 
end for

In this example, the explode function splits the source string using a comma (,) as the delimiter. The resulting array, parts, contains individual elements from the source string. The for loop then iterates through the array and prints each part to the console.

The output in the console will be:

Part 1: Element1
Part 2: Element2
Part 3: Element3

See also:

This function is handy for parsing and processing delimited data within your FileMaker solution.


Back to top

Function: implode

The implode function creates a delimited string from an array. You specify both the delimiter and the array, and the function returns a string with the content of the array separated by the specified delimiter. This function is often used in conjunction with "explode," which performs the reverse operation. Typical use cases include building a value list for an SQL query or creating a CSV line for export.

When working with SQL queries in FileMaker, you often receive results as a delimited list. Using the explode function followed by implode allows you to process this data efficiently, making it easier to access individual fields within the SQL result.

Parameters:

Parameter name  Type  Description
delimiter  string  The character or string that joins the elements of the array.
array  array  The array to be processed.

Return value: Type String: The resulting delimited array.

Example:

array string headers = {"ID", "Name", "Address", "Zip", "Town"};
print implode(", ", headers);

// This will output to the console:
ID, Name, Address, Zip, Town

In this example, an array named headers is joined into a delimited string using implode, with a comma and a space as the delimiter. The resulting string is printed to the console.

References:

This section explains the usage of the implode function in ACF, demonstrating how to create delimited strings from arrays, making it useful for various tasks such as building value lists or processing SQL query results.


Back to top

Function: SQL Queries and Array Functions

When working with SQL queries in FileMaker, especially in the absence of native array functionality, SQL query results are typically returned as delimited lists. Handling these results requires parsing to access individual record fields. This process involves:

This approach can be somewhat tedious and error-prone.

The array concept in ACF simplifies this process significantly. You no longer need to worry about line breaks in the result set as long as you use delimiters that don't exist in your retrieved data. A common practice is to use "||" for field separation and "|*|" for record separation.

With ACF, you can use array functions like explode to convert the result set into arrays effortlessly.

Example:

string resultSet = ExecuteSQL("........"; "||"; "|*|");
array string fields, recs = explode("|*|", resultSet); 
int i, noRecs = sizeof(recs); 

for (i = 1, noRecs)
    fields = explode("||", recs[i]); 
    // Now you can access each field in the record individually using fields[1], fields[2], ... fields[n]...
end for

This method makes it much easier to work with SQL query results, eliminating the need for complex parsing and simplifying your code. For more examples of SQL functions in the plugin, refer to the article about SQL functions.


Back to top

FileSystem Functions

An Overview of the File-System Functions

File Input/Output (File I/O) is a fundamental aspect of computing, and while FileMaker has some built-in capabilities, they may not always meet all your needs. Therefore, we've included robust file-system functions in ACF to empower your FileMaker solutions with versatile file operations.

In addition to the basic file operations like opening, closing, reading, and writing to files, ACF offers a comprehensive suite of file-system functions:

The "Create File" function is not necessary because when you open a file for writing, and it doesn't exist, it will be created automatically. This approach simplifies file creation.

It's worth noting that ACF provides an efficient method for handling file operations. When you open a file using ACF, you receive a file ID, which you can assign to an integer variable. This file ID allows you to interact with the file in multiple function calls, such as reading and writing. You don't need to close the file immediately after opening it; you can continue to work with it and close it explicitly when you're finished.

ACF uses POSIX paths (Unix paths) for file operations, simplifying path handling. There's no need to deal with various path syntaxes like "file:///" or "filemac://"; you can work with standard POSIX paths directly.

In summary, ACF's file-system functions provide a powerful toolkit for managing files and directories within your FileMaker solutions efficiently. Whether you need to read, write, copy, move, or delete files, or interact with directories, ACF simplifies the process and makes file operations seamless.


Back to top

Function: File_Exists

Check if a file with given path exists in the file-system. To avoid errors one can check if a file exists before trying to open it, to example.

Prototype:


bool z = file_exists ( "/path/to/file.txt");

Parameters:

Parameter name  Type  Description
path  string  full path or relative path to the acutal file

Return value: Type Boolean : True if file exists, otherwise false.

Example:


if ( file_exists ( "datafile.txt")) then
    // read the datafile
else
    // Create the datafile
end if

See also:


Back to top

Function: directory_exists

Check if a directory in the file-system exists or not. Can be used to conditionaly create directories before putting files in them, to example.

Prototype:


bool z = directory_exists ( string path );

Parameters:

Parameter name  Type  Description
path  string path to directory, without a trailing slash (/)

Return value: Type Bool : True if the directory exists, otherwise false.

Example:

string dir = "/users/ole/Desktop/MyLoggFiles", res; 
if ( ! direcotry_exists ( dir)) then 
    res = create_directory ( dir ); 
end if
// Put some log-file in it. 
string loggfile = dir + "/loggfile.txt"
int x = open ( loggfile, "wa" ); 
write ( x, format ("%s Some logg text...", string(now(), "yyyy-mm-dd" )));
close ( x );

See also:


Back to top

Function: Create_Directory

Create a directory if it does not exists. This function also creates intermediate directories if neccessary.

Prototype:


string result = create_directory ( string path);

Parameters:

Parameter name  Type  Description
Path  string path to the target directory

Return value: Type string : Returns "OK" if the direcotry exists or is created. Otherwise an error message.

Example:


string res = create_directory("/Users/ole/Desktop/loggfiles/a/b/c/d/e/f");

Suppose "loggfiles" does not exits from before then the following happens.

Otherwise, if some of the directories in this path does not exists, they are created as needed.

See also:


Back to top

Function: open

The open function allows you to open a filesystem file for read, write, or write-append access.

Parameters:

Parameter Name Type Description
Filename string Full path or relative path of the file. Use ~ to refer to the user's home folder.
Access Mode string Either "r" (read), "w" (write), or "wa" (write append) mode.

Return Value:

Type Int: An integer number representing the open file, which can be used as a parameter in subsequent function calls to write more bytes to the same file.

Example:

int x = open("myFile.txt", "w");
write(x, "This is a line of text to the file");
write(x, format("\nHere are some data, %s, %d, %s, %d", "data1", 22, "data2", 33));
close(x);

Related:


Back to top

Function: read

The read function reads the entire content of a file into a string variable. It takes the open file ID obtained from the "open" function as a parameter. If you want to read a file line by line, consider using the readline function. After the content has been read, you must close the file using the close command.

Parameters:

Parameter Name Type Description
FileID int File ID obtained from the "open" function.

Return Value:

Type TEXT: The content of the file.

Example:

int x = open("myfile.txt", "r");
string content = read(x);
close(x);

See Also:


Back to top

Function: readline

The readline function is similar to read, but it reads only one line at a time. It is designed for use when sequentially processing lines in a file, such as during an import operation.

Parameters:

Parameter Name Type Description
FileID int FileID obtained from the open function.

Return Value:

Type string: The next line in the file. When the end of the file is reached, it returns "EOF" (End of File).

Example:

int x = open("myFile.txt", "r");
string line;
repeat
    line = readline(x);
    // .. do something with the line
until (line == "EOF");

The readline function is useful for reading and processing files line by line, making it suitable for various tasks like data imports.


Back to top

Command: write

The "write" command is used to write the contents of a string variable to a file. You can perform multiple writes to the same file, appending each write to the existing content, as long as the file remains open. If the file is opened in "wa" mode, the write operation will append the content to the end of the file.

Parameters:

Parameter Name Type Description
FileID int The FileID returned from the "open" function.
text string The text to write, which can span multiple lines.

Return Value:

The "write" command does not return any value.

Example:

int x = open("myfile.txt"); 
write(x, "This is the first line\n"); 
write(x, "This is the second line\n"); 
close(x);

In this example, we open the file "myfile.txt," write two lines of text to it, and then close the file.

Keeping Files Open Across Several ACF Function Calls

It is possible to maintain a file open for writing across multiple ACF function calls by utilizing a global variable to store the FileID (x in the example above). One function can open the file, write some content to it, and leave it open. Then, another function can continue to write to the same file, and a third function can eventually close it. This approach allows for the coordination of file operations across multiple ACF functions within a FileMaker Script. Alternatively, the function responsible for opening the file can return the FileID back to FileMaker, which can then pass it as a parameter to subsequent ACF functions for write operations and, finally, close the file. This latter method ensures better concurrency control, as separate processes do not share the same FileID, enhancing the reliability of file handling.

References:


Back to top

Function: close

The "close" function is used to close a previously opened filesystem file. When you've finished working with a file, it's essential to close it properly to free up system resources and ensure data integrity.

Function Prototype

close(<File ID>);

Parameters

Return Value

Type: None - There is no return value for the "close" function.

Example

int x = open("myFile.txt", "r");
// Perform operations on the file
close(x);

In this example, the "open" function is used to open the file "myFile.txt" for reading. After performing operations on the file, the "close" function is called to properly close it.

The "close" function is an essential part of working with files in ACF and ensures that files are handled correctly and efficiently.

References

The "close" function allows you to gracefully conclude your file operations in ACF, promoting proper resource management and data integrity.


Back to top

Function: save_file_dialogue

The save_file_dialogue function opens a file dialog for the user to select a new or existing filename to be used in a later script step. This function does not create the file or write any content to it. If the selected file already exists, the user is prompted to confirm whether they want to overwrite it.

Parameters:

Parameter name Type Description
prompt string Text displayed in the title bar of the save file dialog.
InDir string Path pre-selected in the dialog.
filename string A proposed filename that the user can edit.

Return value: Type string: The full path of the selected file. If empty, it means the user clicked the "Cancel" button.

Example:

string root = "~/Documents/";
string selectedFile = save_file_dialogue("Save file as?", root, "test.txt");

Use this function to allow users to specify a file location and name for saving files in your FileMaker solution.

References:


Back to top

Function: select_directory

The select_directory function opens a dialog that allows the user to select a directory (folder).

Parameters:

Parameter name Type Description
prompt string Text displayed in the dialog's title bar.

Return value:

Type string: The full path of the directory selected by the user. If the user cancels the dialog, an empty string is returned.

Example:

string selectedDirectory = select_directory("Choose a directory for HTML");
if (selectedDirectory == "") then
    // The user canceled the selection
end if

Use this function when you need users to specify a directory location within your FileMaker solution.

Directory Image

References:


Back to top

Function: Copy_file and Move_file

The copy_file and move_file functions allow you to perform file operations in ACF. You can copy or move files from a source path to a destination path. However, please note that the source file must exist, and the destination directory must also exist before performing these operations.

About move_file

When using the move_file function, the behavior depends on whether the source and destination are on different disks or the same drive:

To rename a file, you can simply move it to the desired location with a different name.

Prototype:

string copy_file ( <Source file path>, <Destination path>); 
string move_file ( <Source file path>, <Destination path>);

Parameters:

Return value:

Type string: "OK" if the operation was successful, otherwise an error message.

Example: Copy File

function testCopyFile ()

    string file1 = select_file ("Choose a file to copy?", desktop_directory()) ; 
    if ( file1 == "" ) then
        return "";  // User hit cancel
    end if
    string wohin =  select_directory("Destination directory?");
    if ( wohin == "" ) then
        return "";  // User hit cancel
    end if
    string newfn = regex_replace("^(.+)/(.+\..+)$", file1, wohin + "/\2"); 
    print format ("Chosen file: %s\nDest.dir: %s\nNew Path: %s\n", file1, wohin, newfn); 
    if ( file1 == newfn ) then
        throw "No match in regex"; 
    end if
    return copy_file ( file1, newfn ) ;  

END

Example: Move File

function testMoveFile ()

    string file1 = select_file ("Choose a file to move?", desktop_directory()) ; 
    if ( file1 == "" ) then
        return "";  // User hit cancel
    end if
    string wohin =  select_directory("Destination directory?");
    if ( wohin == "" ) then
        return "";  // User hit cancel
    end if
    string newfn = regex_replace("^(.+)/(.+\..+)$", file1, wohin + "/\2"); 
    print format ("Chosen file: %s\nDest.dir: %s\nNew Path: %s\n", file1, wohin, newfn); 
    if ( file1 == newfn ) then
        throw "No match in regex"; 
    end if
    return move_file ( file1, newfn ) ;  

END

In these examples, a file selection dialogue allows you to choose a file, followed by a directory selection dialogue for specifying the destination directory. A regular expression (regex_replace) is used to create a new path for the destination. The console output displays the chosen file, destination directory, and the new path.

Moving or Copying Directories

Both move_file and copy_file functions can also work on directories. If the source path points to a directory, the entire directory will be moved or copied to the destination. In the example provided, we've adjusted the regular expression to accommodate directory names.

Example: Copy Directory

function testCopyDirectory ()

    string dir1 = select_directory ("Choose a directory to copy?", desktop_directory()) ; 
    if ( dir1 == "" ) then
        return "";  // User hit cancel
    end if
    string wohin =  select_directory("Destination directory?");
    if ( wohin == "" ) then
        return "";  // User hit cancel
    end if
    string newdir = regex_replace("^(.+)/([^/]+)$", dir1, wohin + "/\2"); 
    
    print format ("Chosen directory: %s\nDest dir: %s\nNew Path: %s\n", dir1, wohin, newdir); 
    if ( dir1 == newdir ) then
        throw "No match in regex"; 
    end if
    return copy_file ( dir1, newdir ) ;  

END

In this example, you can choose a directory for copying to another location. The regular expression creates the new path for the destination directory.

These functions provide convenient ways to copy or move files and directories within your FileMaker solution.


Back to top

Character Set Conversion

FileMaker uses the UTF-8 character set internally, but there may be situations where you need to read from or write to files in different character sets. To facilitate character set conversion, ACF provides two essential functions: "to_utf" and "from_utf." These functions allow you to convert strings between UTF-8 and other character sets, making it easier to handle data in various encodings.

from_utf Function

The "from_utf" function converts a UTF-8 string to a character set specified in the second parameter. This function is particularly useful when you need to convert strings before writing them to files in a specific character set.

to_utf Function

Conversely, the "to_utf" function converts a string from a character set specified in the second parameter to UTF-8. You can employ this function to convert strings after reading them from files encoded in various character sets.

Supported Character Sets

ACF supports a wide range of character sets for conversion. Here are the valid character set specifications:

Function Prototypes

from_utf Function Prototype

string converted = from_utf(string SourceUTF8String, ToCharacterSet);

to_utf Function Prototype

string destUTF8string = to_utf(string SourceString, FromCharacterSet);

Platform-Specific Implementation

It's important to note that while most conversion codes should work on both Mac and Windows platforms, there may be some differences in implementation. The Mac version of the plugin utilizes the "iconv" library for conversions, while the Windows version uses the boost::locale library. If you encounter specific conversion needs or issues, you can contact the developer for further exploration or refer to the respective libraries for more information.

Example:

string utftext = to_utf("My ISO8859-1 text to be converted", "ISO-8859-1");
string isotext = from_utf(utftext, "ISO-8859-1");

In this example, the "to_utf" function converts a string from ISO-8859-1 to UTF-8, and the "from_utf" function converts it back to ISO-8859-1.

The "from_utf" and "t_utf" functions in ACF provide a convenient way to handle character set conversions when working with different encodings in your FileMaker solutions.


Back to top

MarkDown - HTML

Introduction: Markdown to HTML Conversion Functions

A set of functions is employed to convert Markdown text into HTML format. The output can either be a string containing HTML code or a file saved on disk.

Setup

To make these functions work, you need to specify a destination HTML directory where the HTML documents will be stored. This directory serves as the root directory for the website hosting the HTML files. It's crucial to ensure that this directory contains the "Themes" folder that comes with the plugin. Simply copy the "Themes" folder into your designated HTML directory to set it up.

The command set_markdown_html_root is used to define this directory before initiating the conversion process.

Additional CSS: You have the option to specify an additional CSS file that will be included in the generated HTML code. This CSS file allows you to customize or modify the styling of the resulting documents as needed.

The command set_markdown_CSS is used to specify this additional CSS file (optional).

Styling: The "Themes" folder within the plugin contains named themes and code styles for use, particularly in pre-code boxes. The second parameter of the markdown2html function call, named "style," accepts a comma-separated list of style and code style names.

Example:

package myTestPackage "This is a small test to convert Markdown";

function ConvertMarkdown ()
        string root = select_directory("Select the root directory for HTML");
        set_markdown_html_root ( root ) ;
        string sf = select_file ("Select a Markdown File?");
        if (sf != "") then
            string df = save_file_dialogue ("Save file as?", "test.html", root);
            if (df != "") then
                string res = markdown2html (sf, "solarized-dark,monokai", df);
                print res;
            end if
        end if
        return "OK";
end

Compile the script using the "ACF_CompileText" plugin function provided by the ACF_Functions plugin. If there are no compilation errors, the product is already loaded and can be executed.

In a FileMaker Script step, use the following:

Set Variable ( $x; ACF_run ( "ConvertMarkdown")  )

References:


Back to top

Command: set_markdown_html_root

The set_markdown_html_root command is used to set the root path for the HTML directory where subsequent calls to convert Markdown documents to HTML will be stored. This directory must contain a "Themes" folder with the CSS styles and themes used for the conversion. Failing to use this command before calling Markdown to HTML conversion functions may cause those functions to fail due to the inability to locate the themes folder.

Parameters:

Parameter name Type Description
path string The full path of the HTML folder.

Return value:

No return value.

Example:

string root = select_directory("Choose the root directory for HTML");
if ( root == "" ) then
    return "Canceled"; 
end if
set_markdown_html_root(root);

Use this command when you want to specify the root directory for storing HTML files generated from Markdown.

References:


Back to top

Function: markdown2html

Convert a document in Markdown format to HTML using themes. You select themes to be used for styling the HTML to be a good looking HTML representation of the Markdown document. The function uses the html root path specified in the "set_markdown_html_path" command to locate the themes folder with the CSS for selected theme.

Parameters:

Parameter name  Type  Description
SourceFile  string  Full path to the source Markdown file
Style  string  <style>, <codestyle> Pick one style and one codestyle from the table below separat by comma. See example.
Output file  string  resulting html full path

Return value: Type TEXT : Status of the operation. If Output file is not specified, the returnvalue contains the HTML tekst.

Example:


string root = select_directory("Velg root katalog for HTML");
set_markdown_html_root ( root ) ;
string res = markdown2html (sf, "solarized-dark,monokai", df);

Markdown styles:

Style Name  Type  Description
black  style  
foghorn  style  
github  style  
solarized-light  style  
solarized-dark  style  
dirt  style  
ghostwriter  style  
witex  style  
agate codestyle
dark codestyle
monokai_sublime codestyle
androidstudio codestyle
darkula codestyle
obsidian codestyle
arta codestyle
default codestyle
paraiso codestyle
ascetic codestyle
docco codestyle
pojoaque codestyle
atelier-cave codestyle
far codestyle
railscasts codestyle
atelier-dune codestyle
foundation codestyle
rainbow codestyle
atelier-estuary codestyle
github codestyle
school_book codestyle
atelier-forest codestyle
github-gist codestyle
solarized_dark codestyle
atelier-heath codestyle
googlecode codestyle
solarized_light codestyle
atelier-lakeside codestyle
grayscale codestyle
sunburst codestyle
atelier-plateau codestyle
hopscotch codestyle
tomorrow codestyle
atelier-savanna codestyle
hybrid codestyle
tomorrow-night codestyle
atelier-seaside codestyle
idea codestyle
tomorrow-night-blue codestyle
atelier-sulphurpool codestyle
ir_black codestyle
tomorrow-night-bright codestyle
brown_paper codestyle
kimbie codestyle
tomorrow-night-eighties codestyle
brown_papersq codestyle
magula codestyle
vs codestyle
codepen-embed codestyle
mono-blue codestyle
xcode codestyle
color-brewer codestyle
monokai codestyle
zenburn codestyle


Back to top

Programming Examples

Example: Luhn Algorithm - Generate Mod10 KID Control Digit

This example demonstrates how to add a modulo-10 (Mod10) control digit to a string of numbers using the Luhn algorithm. The Luhn algorithm is widely used for validating credit card numbers and other identification numbers.

For more details, you can refer to Wikipedia's article about the Luhn Algorithm.

Example Function:

function AddMod10(string number)

    array int digits;
    int l = length(number);
    int i;

    // Check if the input contains only digits.
    if (regex_match("\d+", number)) then

        // Build an array of integers.
        for (i = 1, l)
            digits[] = ascii(substring(number, i - 1, 1)) - 48;
        end for

        // Back traverse every second digit and double it. If > 9, subtract 9.
        for (i = l, 1, -2)
            digits[i] = digits[i] * 2;
            if (digits[i] > 9) then
                digits[i] = digits[i] - 9;
            end if
        end for

        // Calculate the sum of all digits.
        int sum;
        for (i = 1, l)
            sum += digits[i];
        end for

        // Calculate the control digit.
        int digx = mod(sum * 9, 10);

        return number + digx;
    else
        return "ERROR: Expects only digits as parameter: " + number;
    end if
end

Result Test:

// For the example from the Wikipedia article mentioned above:
// 7992739871x
Set Variable ($KID; ACF_run("AddMod10"; "7992739871"))

// The return value will be: 79927398713, where the control digit is 3.

References:

Comparison with Standard Custom Functions:

In this example, we've implemented the Luhn algorithm to generate a Mod10 control digit for a given string of numbers. We also perform error checking to ensure that the source string contains only digits. This approach is designed to catch any unexpected characters in the input.

For comparison, there are standard custom functions available on Brian Dunning's website that provide similar functionality:

However, it's worth noting that these functions may not perform the same level of error checking as the example provided here. Using regular expressions to validate the input can help prevent errors and ensure accurate results.

This section provides a detailed example of how to implement the Luhn algorithm to generate a Mod10 control digit for a string of numbers in ACF, including error checking to ensure the input consists of digits only.


Back to top

Here's the revised text for the example of a standard library:

An Example of a Standard Library

Many developers create a standard library for functions they use in every development project. This library can be initialized in the startup script where the binary code loads from a text field in a preference table or a similar location. For this purpose, you can create fields named ACFPack1, ACFPack2, ACFPack3, and ACFPack4. You can easily add more fields if you need more than four packages.

Here's an example calculation you can use in a "Set Variable" script step to load these packages:

Let( [
   v = If (not IsEmpty ( Preferences::ACF_Pack1) ; ACF_Install_base64Text( Preferences::ACF_Pack1 ); "") ;
   v = v & If (not IsEmpty ( Preferences::ACF_Pack2) ; ACF_Install_base64Text( Preferences::ACF_Pack2 ); "") ;
   v = v & If (not IsEmpty ( Preferences::ACF_Pack3) ; ACF_Install_base64Text( Preferences::ACF_Pack3 ); "") ;
   v = v & If (not IsEmpty ( Preferences::ACF_Pack4) ; ACF_Install_base64Text( Preferences::ACF_Pack4 ); "") 
];
v
)

This calculation checks if there's content in each of the ACFPack fields and uses the ACFInstall_base64Text function to load the packages. You can add your code to one of the empty slots in these fields. Once you're finished, you can move your code to another package that contains the complete collection of functions.

Example:

This example demonstrates how you can use this approach to create a reusable library of functions that you can easily incorporate into your FileMaker solutions. It simplifies the process of managing and sharing common functionality across multiple projects.


package bootstrap "Functions to facilitate load and save source and binary files";

// Load some content from a file. 

function LoadFile (string filename)
    FunctionID 200;
    string content; 
    int x; 
    x = open (filename, "r"); 
    content = read (x); 
    close ( x ) ; 
    return content; 
end

// Save some content on a file. 

function SaveFile (string filename, string content )
    FunctionID 201;
    int x; 
    x = open (filename, "w"); 
    write (x, content); 
    close ( x ); 
    return 1; 
end

// Select a file and return its content. 

function SelectFile (string startPath, string Prompt)
    FunctionID 202;
    string cc = "";
    string filename = select_file (Prompt, startPath); 
    if (filename != "") then
         cc = LoadFile(filename); 
    end if
    // Make also the filename available from FileMaker after the content is read. 
    $$FileName = filename; 
    return cc; 
end

// Select a folder on the file system

function SelectFolder (string Prompt)
    FunctionID 203;
    string cc = "";
    string folder = select_directory(Prompt); 
    return folder; 
end

// Ask for a FileName to save to. 

function SelectSaveAsFile (string prompt, string defname, string defdir)
    FunctionID 204;

    string filename = save_file_dialogue (prompt,  defname, defdir) ; 
    return filename;

end
/*
   Function to make 000 grouping with space, and decimal "comma" that is used in Norway. 
*/
function BelFormat ( float bel ) 
    
    bool negative; 
    if (bel < 0.0) then
        negative = true; 
        bel = -bel; 
    end if

    long reminder, f; 
    int ore; 
    string result = ""; 
    reminder = bel; 
    ore = round ( (bel - reminder ) *100, 0); 
    // print "\n:" + reminder; 
    while ( reminder > long(0) ) 
        // print "\n" + reminder; 
        f = mod ( reminder, 1000); 
        reminder = reminder / 1000; 
        if (reminder > long(0)) then
            result = format ( " %03ld", f) + result; 
        else
            result = string ( f ) + result; 
        end if

    end while

    if (negative) then
        result = "-" + result; 
    end if
    print "Time Used: " + uSec(); 
    return format ( "%s,%02d", result, ore); 
end

The last function "BelFormat" used to format number with space as 1000 separator and comma. Another variant of this would be to use a reg-ex function instead - dramatically reducing the complexity of the function.

This regex was found on: regexr.com

Like this: 


function BelFormat  ( float bel ) 
    functionID 230; 
    string sbel = substitute ( format ( "%.2f", bel ), ".", "," ) ;  
    return  regex_replace ( "\d{1,3}(?=(\d{3})+(?!\d))", sbel, "$& ") ;  
end

Using this function, the following numbers get translated:

Input to the function  Result
1234 1 234,00
10469236 10 469 236,00
400,49 400,49
4000,228 4 000,23
40000,228 40 000,23
4369825,66 4 369 825,66
-200000 -200 000,00
123456789 123 456 789,00


Back to top

Updating Currencies from a Web Service

In this example, we demonstrate how to use ACF to fetch data from a web service and update a local currency table with this information. To accomplish this, we pull currency names from a local table and construct requests to the web service. For this example, we use an external plugin called Troi URL, which will later be integrated into the ACF plugin itself.

Table Definition

Here is the definition of the currency table:

Currency Table

Example

package Currencies "Functions to Update Currency Table";

/*
 - Create a request URL to Norges Bank WEB-service for currency information.
*/
function request_url(string kurskode)
    string url = format("https://data.norges-bank.no/api/data/EXR/B.%s.NOK.SP?startPeriod=%s&EndPeriod=%s&format=csv-:-comma-false-y&lastNObservations=1",
        kurskode, string(date(now()) - 7, "%Y-%m-%d"), string(date(now()), "%Y-%m-%d"));
    return url;
end

/*
 - Fetch all the tracked currency codes from db_Currency, get the exchange rate (spot), and update the table.
*/
function UpdateCurrencyTable_NorgesBank()
    string avail = "AUD, BDT, BGN, BRL, BYN, BYR, CAD, CHF, CNY, CZK, DKK, EUR, GBP, HKD, HRK, HUF, IDR, ILS, INR, ISK, JPY, KRW, LTL, MMK, MXN, MYR, NZD, PHP, PKR, PLN, RON, RUB, SEK, SGD, THB, TRY, TWD, USD, ZAR";

    string kurser = ExecuteSQL("SELECT Valutakode, Valutanavn FROM db_Currency"; "||"; "|*|");
    array string a_kurs = explode("|*|", kurser), a_line, resLines, resFields;
    int ant = sizeof(a_kurs);
    int i;
    string url, result, dato, val, kurskode, sql;

    for (i = 1, ant)
        a_line = explode("||", a_kurs[i]);
        kurskode = a_line[1];

        if (kurskode != "NOK") then
            $url = request_url(a_line[1]);
            result = @TURL_Get("-Encoding=UTF8"; $url)@;

            print "-" * 80 + "\n" + result + "\n";

            resLines = explode("\n", result); // We always get two lines: header + data line
            resFields = explode(",", substitute(resLines[2], "\"", ""));
            dato = resFields[1];
            val = resFields[2];

            sql = format("UPDATE db_Currency SET Valutadato = DATE '%s', ValutaKurs = %s WHERE Valutakode = '%s'", dato, val, kurskode);
            result = ExecuteSQL(sql);
        end if
    end for

    return "OK";
end

In this example, we first create a request URL to a web service provided by Norges Bank to obtain currency information. We then fetch all the tracked currency codes from the local db_Currency table, retrieve the exchange rate (spot) from the web service, and update the table with the obtained data.

This example demonstrates how ACF can be used to interact with web services and update local databases with external data.


Back to top

Test Arrays as Parameters

In this section, we'll demonstrate the use of arrays as parameters by running the "arrtest2" function, which showcases how arrays are passed and modified within functions.

Example:

package ArrayParTest "Test Array as Parameter";

function arrtest1(array string bb)
    int k = sizeof(bb);
    print k + "\n";
    bb[] = "Test second last row";
    bb[] = "Test last row";

    return bb;
end

function arrtest2()
    array string xx = {"Test1", "test2", "test3"};

    array string b2 = arrtest1(xx);
    b2[] = "b2 altered"; // Demonstrating that xx and b2 are different arrays.

    int i;
    int x = sizeof(b2);
    for (i = 1, x)
        print "\n" + b2[i];
    end for

    print "\n" + "-" * 80;

    // Arrays as parameters are transferred by reference, so altering it in the "arrtest1" function
    // should be effective in the source as well.
    x = sizeof(xx);
    for (i = 1, x)
        print "\n" + xx[i];
    end for

    return 1;
end

Console Output After Running arrtest2():

3

Test1
test2
test3
Test second last row
Test last row
b2 altered
--------------------------------------------------------------------------------
Test1
test2
test3
Test second last row
Test last row

In this example, we define two functions: arrtest1 and arrtest2. arrtest2 demonstrates how arrays are passed as parameters and how changes made to the array within arrtest1 affect the source array in arrtest2. The console output showcases the behavior of these arrays and their modifications.


Back to top

Example: Dynamic Value List in FileMaker

Many individuals, including myself, have encountered challenges when dealing with the concept of dynamic Value Lists in FileMaker. Consider a scenario where you need to scan a folder and populate a Value List with the files from that folder. This allows users to select a file from a popup or dropdown list in a layout. While FileMaker is incredibly flexible when it comes to creating Value Lists based on relational structures, it lacks a straightforward method for creating Value Lists from a text source. Even using a field with an unstored calculation doesn't always work as expected.

Some have attempted to create a relationship to a second instance of a table with an "X" relationship, which, despite being considered unconventional, has sometimes worked. However, relying on such an approach can be risky, as it may not be supported in future FileMaker versions.

Fortunately, there's a more reliable solution. We can create a ValueList table with fields like Primary Key, ListName, and ListValue. Optionally, you can include a user-ID field if you wish to customize lists for different users. I've developed an ACF function that populates the ValueList table with items from a text-based list. It creates a record in the table for each list item, associating each record with the appropriate ValueList name. When you need to update the list later, you can remove unwanted records and add missing ones.

The function I'm about to introduce accomplishes precisely that. To use it, you'll need a stored calculation field that contains the list name, ensuring it's indexed as it won't change. Create a second instance of the ValueList table in the relationship graph, let's call it "ValueList_MyList1" as an example. Establish a relationship (=) to this instance from your calculated field. When defining the Manage ValueLists dialog, select only the records that follow this relationship, and you're all set.

Now, whenever you need to update a Value List from a script, run our ACF function with the items you want in a variable or a text field. The selection in your Value List will be instantly updated, making it versatile for various selection tasks with the right relationship and necessary values in your table.

In this specific example, we have a folder containing files, and we want to create a Value List that mirrors the files in that folder. We're using the Troi_File plugin to retrieve a list of files from the folder, returning a text variable with one line per file. These files serve as templates for a document system, and we want users to select a template from a dropdown list.

Here's the ACF code used for this purpose:

// Use the Troi File plugin to pull a list of files.
function ListTemplateFiles()
    return @TrFile_ListFolder("-Files"; "Macintosh HD" & substitute(Preferences::TemplateDir; "/"; ":") & ":")@;
end

// Update the ValueList table to stay in sync with our File List
function UpdateValueList(string listName, string listItems)

    // Create an SQL recordset of all the currently present values.
    string sql = format("SELECT ValueText, PrimaryKey FROM ValueLists WHERE ListName = '%s'", listName);
    int rs = SQL_Query(sql);

    // Get the row count
    int rc = SQL_GetRowCount(rs);
    array string row;

    // Get an array of the items we want.
    array string NewList = explode("\r", listItems);
    string item, pk, res;
    int i, j, nlc = sizeof(NewList);
    bool found;

    // Loop through all list items we have
    for (i = 1, rc)
        row = SQL_GetRow(rs, i - 1); // First row is number 0.
        item = row[1];
        found = false;
        
        // Check if we have it in our new list.
        for (j = 1, nlc)
            if (NewList[j] == item)
                found = true;
                NewList[j] = "";  // If found, clear the item to avoid duplication.
            end if
        end for
        
        if (!found)
            // If not found, delete the item from the ValueList table.
            res = ExecuteSQL(format("DELETE FROM ValueLists WHERE PrimaryKey='%s'", row[2]));
        end if
    end for
    
    // We have removed unwanted list items; now, create those that are missing.
    for (i = 1, nlc)
        if (NewList[i] != "")
            // Adjust the SQL statement if you want to include user or function fields.
            sql = format("INSERT INTO ValueLists (ListName, ValueText) VALUES('%s', '%s')", listName, NewList[i]);
            res = ExecuteSQL(sql);
            if (res != "")
                print "\n" + sql + "\n" + res; // Print errors to the console if any.
            end if
        end if
    end for

    // Close the result-set from the first Query.
    SQL_Close(rs);
    return "OK";
end

// Call this function from FileMaker using ACF_Run("UpdateTemplatesList") in a Set Variable script step.
function UpdateTemplatesList()

    string fileList = ListTemplateFiles();
    string res = UpdateValueList("Templates", fileList);
    return res;
end

With this approach, you can easily update the ValueList table whenever necessary, modifying only the entries that require changes.


Back to top

An XML Export Example

Occasionally, you may need to export data from a table in XML format. This example demonstrates how to achieve this using ACF.

Example Code:

Package XMLDump "Dump SQL result to an XML file";

function XMLtag (string tag, string value, int tabs) 
    string result; 
    value = substitute ( value, "&amp;", "&amp;amp;");
    string t = "\t"*tabs; 
    if (value == "") then
        result = t+"<"+tag+"/>\n";
    else
         result = t+"<" + tag + ">" + value + "</" + tag + ">\n"; 
    end if
    
    return result; 
end

function XMLtag2 (string tag, string value, int tabs)  

    string result; 
    string t = "\t"*tabs; 
    if (value == "") then
        result = t+"<"+tag+"/>\n";
    else
         result = t+"<" + tag + ">\n" + value + "\n"+t+"</" + tag + ">\n"; 
    end if
    
    return result; 
end

function XMLdumpDataset (string filename, string MasterKey, string DetailRecordKey, string DetailKeys, string SQLresult, string FieldSeparator, string RecordSeparator)

    array string Records = explode ( RecordSeparator, SQLresult); 
    array string Keys = explode ( FieldSeparator, DetailKeys); 
    array string Fields; 
    int i,j, numRecords = sizeof ( Records ), numFields = sizeof (Keys); 
    string result = ""; 
    string fres ; 
    // Produce the XML content. 
    for (i = 1, numRecords) 
        Fields = explode ( FieldSeparator, Records[i]); 
        if (sizeof ( Fields ) != numFields ) then
            throw format ("Different number of fields and keys: Fields:%d, Keys:%d ", sizeof ( Fields ), numFields) ; 
        end if
        fres = ""; 
        for (j = 1, numFields)
            fres += XMLtag(Keys[j], Fields[j], 2); 
        end for
        result += XMLtag2 ( DetailRecordKey, fres, 1); 
    end for
    // Add master key
    if (MasterKey != "") then
        result = XMLtag2(MasterKey, result, 0); 
    end if
    // Write to file, or return result if no filename. 
    if (filename != "") then
        int x = open (filename, "w"); 
        write (x, "<?xml version='1.0' encoding='UTF-8' standalone='no'?>\n"); 
        write (x, result ); 
        close (x); 
        result = "OK";
    end if
    long tu = uSec(); 
    print "Time used : " + tu + format  (" (%-.6f sec)", tu/1.0E6); 
    return result; 
end

FileMaker Script to Execute the Dump:

// Calculation used in the "export button" in the layout: 
// Prototype: 
// ACF_run("XMLdumpDataset"; text_filename; text_MasterKey; text_DetailRecordKey; 
// text_DetailKeys; text_SQLresult; text_FieldSeparator; text_RecordSeparator)
//    ==> Return type = text

Set field [Preferences::gResult; 
ACF_run("XMLdumpDataset"; Contacts::gFileName; "Contacts"; "Contact"; 
"ID||FirstName||LastName||Address1||Address2||Zip||City||Country||Email||Phone||Mobile"; 
ExecuteSQL ( "SELECT ID,FirstName,LastName,Address1,Address2,Zip,City,Country,Email,Phone,Mobile FROM Contacts" ; "||" ; "|*|"  ); "||"; "|*|")]

After running this on approximately 2000 contacts, inspecting the console shows:

Time used : 400447 (0.400447 sec)

This time includes writing 450KB of data to the output file. It's important to note that the SQL query time is not included, as it occurs before entering the function. With 400 milliseconds for 2000 contacts, it averages to 0.2 milliseconds per contact—a respectable performance.

The resulting file structure resembles the following:

<?xml version='1.0' encoding='UTF-8' standalone='no'?>
<Contacts>
    <Contact>
        <ID>1000</ID>
        <FirstName>Ole Kristian Ek</FirstName>
        <LastName>Hornnes</LastName>
        <Address1>Gluppeveien 12B</Address1>
        <Address2>Innerst i Hørnet</Address2>
        <Zip>1614</Zip>
        <City>FREDRIKSTAD</City>
        <Country>NO</Country>
        <Email>ole@webapptech.no</Email>
        <Phone>46908981</Phone>
        <Mobile>46908981</Mobile>
    </Contact>
    <Contact>
        <ID>1001</ID>
        <FirstName>Roffe</FirstName>
        <LastName>Hansen</LastName>
        <Address1>Linjegata 12</Address1>
        <Address2>Oppgang B</Address2>
        <Zip>1600</Zip>
        <City>FREDRIKSTAD</City>
        <Country>NO</Country>
        <Email>roffe@b.com</Email>
        <Phone>222222</Phone>
        <Mobile>98765432</Mobile>
    </Contact>
    ...
    ... (439KB of data)
    ...
</Contacts>

This XML file can be used for various purposes, such as data interchange or further processing.

Screenshots from the Test Application:

Test Contacts

This example demonstrates how to efficiently export data to an XML file using ACF, making it a valuable tool for handling structured data in your FileMaker solutions.


Back to top

Rolling Up MySQL Connector in the ACF Plugin

The MySQL connector has been in the works for a while and is expected to be included in the 1.6.3.0 version of the plugin. Those interested in testing it can get a Mac-only beta version since some of the basics are already in place.

Working directly with MySQL databases from the FileMaker application can be highly beneficial. Instead of relying on APIs to communicate with web-based systems, you can interact directly with the web-system's database. This enables you to extract or update data instantly from the FileMaker system without the need for any middleware systems.

While it's possible to use FileMaker's external data sources dialogue to add ODBC connections to tables and then script updates, this might not be the most efficient approach for all scenarios. ODBC also requires the installation of ODBC drivers on client computers for all users. In contrast, working directly with the ACF functions can be more efficient and doesn't necessitate ODBC drivers. This feature will be available when version 1.6.3.0 of the ACF plugin is released.

Let's take a look at an example of a function used to delete spam from a support system. In this case, we're dealing with the osTicket support system, which occasionally receives spam emails. With a relatively simple ACF function, 114 spam entries across four tables in the osTicket system were deleted in less than half a second.

Here's the ACF function used for this purpose:

function connect_support_db ()
    int db = mysql_connect ("<host>", "3306", "<user>", "<password>", "<database name>"); 
    return db; 
end

function delete_support_spam ()
    int db; 
    db = connect_support_db (); 
    
    string sql = 'SELECT tem.id, ti.ticket_id, te.id, th.id, te.title, tem.headers FROM ost_thread_entry_email as tem
left outer join ost_thread_entry as te on  te.id = tem.thread_entry_id
left outer join ost_thread as th on th.id = te.thread_id
left outer join ost_ticket as ti on ti.ticket_id = th.object_id
where tem.headers like "%X-Halon-SPAM: YES%" or te.title ="Undelivered Mail Returned to Sender";'; 

    string saker = ExecuteMySQL ( db, sql, "|*|", "||") ; 
    array string rader, kolonner; 
    int norader, i; 
    array string tem, ti, th, te; 
    if ( saker != "") then 
        rader = explode ( "|*|", saker); 
        norader = sizeof ( rader ); 
        for ( i=1, norader)
            kolonner = explode ( "||", rader[i]); 
            tem[] = kolonner[1]; 
            ti[] = kolonner[2]; 
            te[] = kolonner[3]; 
            th[] = kolonner[4]; 
        end for
    else
        mysql_close ( db ); 
        return "No SPAM found"; 
    end if
    string res;
    res = ExecuteMySQL(db, "DELETE FROM ost_thread_entry_email WHERE id IN (" + implode (",", tem ) + ")");
    res += ExecuteMySQL(db, "DELETE FROM ost_thread_entry WHERE id IN (" + implode (",", te ) + ")");
    res += ExecuteMySQL(db, "DELETE FROM ost_thread WHERE id IN (" + implode (",", th ) + ")");
    res += ExecuteMySQL(db, "DELETE FROM ost_ticket WHERE ticket_id IN (" + implode (",", ti ) + ")");
    
    mysql_close ( db ); 
    return "OK " + norader + " SPAMs deleted.";
end

The process started with SQL development in MySQL Workbench, examining the tables in the osTicket support database, identifying where the tickets are stored, and exploring related tables. After analyzing the patterns in the spam tickets, the SQL query was crafted to fetch the IDs of related records. The SQL was tested to ensure it didn't produce false positives, and then it was integrated into the ACF function.

The result? Over 100 spam tickets removed in less than half a second. To improve the cleanliness of the support system further, an additional condition was added to identify spam tickets generated by the "mail delivery subsystem."

Working with Result Sets

The ExecuteMySQL function in ACF is similar to FileMaker's ExecuteSQL. It returns the result as a delimited text with row and column delimiters. To work with this data, you need to parse it into separate rows and fields. However, in ACF, you have arrays, making parsing more straightforward.

Furthermore, ACF introduces the concept of result sets, allowing you to work with structured data directly. This means you can perform a SQL query and return a handle to a result set. With this handle, you can iterate through rows and columns efficiently.

For MySQL, you can use associative arrays, enabling you to access data using column names as string indexes. In contrast, FileMaker SQL doesn't provide column names in its results, so you must always supply a field list in your SQL queries.

Here's an example of how you can print support emails using result sets:

function print_support_emails ()
    int db; 
    db = connect_support_db (); 
    
    int x = mysql_query ( db, "SELECT * FROM c1support.ost_email;"); 
    int i, y = mysql_getrowcount ( x );
    print

 "row count: " + y + ", res-set id: " + x; 
    array string row; 
    for ( i=1, y)
        row = mysql_getrow ( x ); 
        print  row['email'] + "\n"; 
    end for
    mysql_close ( db ); 
    return "OK";    
end

If you'd like to test the MySQL connections with the ACF plugin, send an email to ole@horneks.no to receive a version of the 1.6.3.0 ACF plugin.

Ole K System Developer


Back to top

Function: Example - Calculate Invoice Lines using SQL

In FileMaker, scripting applies to the current layout in the relationship graph. For an Invoice, the layout position is for the Invoice table, and the InvoiceLines are in a related table shown in a portal on the invoice layout. Calculations on the invoice line can be done as calculated fields or as calculations done when lines change. In this solution (a real example), three sets of sum fields were used on the invoice line. One set was calculated fields, one set was indexed fields, and the last set was calculations that either picked values from the calculated field or the indexed field depending on the invoice approval state. The indexed fields had to be updated in the approval routine. As FileMaker's function for this had some issues, this ACF function was created to perform the calculations on the fields instead.

The ACF Functions:

package InvoiceCalculations "Functions to do invoice calculations"; 

// Allow us to check the version of the routines and reload if too old or not installed. 
function InvCalc_Version ()
    return 12.0;
end

// Decide what fields need to be updated and create SQL assignments for the update part. 
// Output on inconsistency: <field>=<new value>
function checkConsistency (float dbval, float calcval, string FieldName)
    if (dbVal == calcval) then
        return ""; 
    else
        return format ("%s=%f", FieldName, calcval);
    end if
end 

// Do the calculation for all invoice lines and update those that need an update. 

function ApproveInvoiceLinesCalc (string invoiceNumber)
    // Get a resultset of all the invoice lines for the invoice. 
    int lq = SQL_query ("SELECT Auto_nr, Antall, Kostpris, Salgspris_u_MVA, MVA_kode, MVA_prosent, Rabatt_belop, Rabatt_prosent, in_Linjesum_avgiftspliktig, in_Linjesum_avgiftsfritt, in_Linjesum_u_MVA, in_Linjesum_kostpris, in_Linjesum_fritt_og_pliktig, in_Linjesum_rabatt, in_Linjesum_m_MVA, in_Linjesum_MVA, in_Linjesum_DG, in_Linjesum_DB FROM db_InvoiceDescriptions WHERE Faktura_nr=" + invoiceNumber); 

    // Declarations
    float Linjesum_avgiftspliktig, Linjesum_avgiftsfritt, Linjesum_u_MVA, Linjesum_kostpris, Linjesum_fritt_og_pliktig, Linjesum_rabatt, Linjesum_m_MVA, Linjesum_MVA, Linjesum_DG, Linjesum_DB;
    float line_net, linemva, mvapro, rabpro, rabbel, line_kostpris, line_db, line_dg; 

    int num_rows = SQL_getRowCount (lq); 

    print format ("Number of Invoice Lines: %d\n", num_rows); 

    int i, ll; 
    string res, upds; 
    array string row, upd, empty; 

    // Loop through the invoice lines
    for (i = 1, num_rows, 1)
        // The SQL_getRow returns an array, allowing us to access each column in the recordset individually. 
        // The first parameter is the handle, and the second is the row number, starting with "0". 
        
        row = SQL_getRow (lq, i-1); 
        // print ("Columns: " + string (sizeof (row)) + " : " + row[1]);
        // As all columns are text from the SQL, we need to use the float function to convert to float (actually double)
        Linjesum_avgiftspliktig = float (row[9]);
        Linjesum_avgiftsfritt = float (row[10]);
        Linjesum_u_MVA = float (row[11]);
        Linjesum_kostpris = float (row[12]);
        Linjesum_fritt_og_pliktig = float (row[13]);
        Linjesum_rabatt = float (row[14]);
        Linjesum_m_MVA = float (row[15]);
        Linjesum_MVA = float (row[16]);
        Linjesum_DG = float (row[17]);
        Linjesum_DB = float (row[18]);
        mvapro = float (row[6]);
        
        line_net = float (row[2]) * float (row[4]);
        if (row[8] == "") then
            rabbel = float (row[7]); 
        else
            rabbel = line_net * float(row[8]) / 100.0;
        end if
        line_net = line_net - rabbel;
        
        upd = empty; // Empty array
        line_kostpris = float (row[3]) * float (row[2]);
        line_db = line_net - line_kostpris; 
        
        // Calculate linjesum-DG
        if (line_net == 0 && line_db == 0) then 
            line_dg = 0; 
        elseif (line_db < 0 && line_net == 0) then 
            line_dg = -100; 
        elseif (line_db < 0) then
            line_dg = abs(line_db) * 100 / line_net * -1;
        elseif (line_net == 0) then
            line_dg = 100; 
        else     
            line_dg = line_db * 100 / line_net; 
        end if

        // Check consistency between db-values and our calculations. 
        // The checkConsistency functions is defined near the top. 
        if (mvapro == 0) then
            upd[] = checkConsistency (Linjesum_avgiftspliktig, 0, "in_Linjesum_avgiftspliktig"); 
            upd[] = checkConsistency (Linjesum_avgiftsfritt, line_net, "in_Linjesum_avgiftsfritt"); 
            upd[] = checkConsistency (Linjesum_u_MVA, line_net, "in_Linjesum_u_MVA"); 
            upd[] = checkConsistency (Linjesum_fritt_og_pliktig, line_net, "in_Linjesum_fritt_og_pliktig"); 
            upd[] = checkConsistency (Linjesum_MVA, 0.0, "in_Linjesum_MVA");
            upd[] = checkConsistency (Linjesum_m_MVA, line_net, "in_Linjesum_m_MVA");
        else
            linemva = line_net * mvapro / 100; 
            upd[] = checkConsistency (Linjesum_avgiftspliktig, line_net, "in_Linjesum_avgiftspliktig"); 
            upd[] = checkConsistency (Linjesum_avgiftsfritt, 0, "in_Linjesum_avgiftsfritt"); 
            upd[] = checkConsistency (Linjesum_u_MVA, line_net, "in_Linjesum_u_MVA"); 
            upd[] = checkConsistency (Linjesum_fritt_og_pliktig, line_net, "in_Linjesum_fritt_og_pliktig"); 
            upd[] = checkConsistency (Linjesum_MVA, linemva, "in_Linjesum_MVA");
            upd[] = checkConsistency (Linjesum_m_MVA, line_net + linemva, "in_Linjesum_m_MVA");
        end if  
        upd[] = checkConsistency (Linjesum_kostpris, line_kostpris, "in_Linjesum_kostpris");
        upd[] = checkConsistency (Linjesum_DB, line_db, "in_Linjesum_DB");
        upd[] = checkConsistency (Linjesum_DG, line_dg, "in_Linjesum_DG");
         
        // Now we have the "upd" array, containing assignments for update, or empty if the field does not need an update. 
        // Implode creates a comma-separated string with potential to have excessive commas in it because of the empty array rows.
        // Get rid of the excessive commas. 
        upds = implode (",", upd); 
        upds = substitute (upds, ",,", ","); // We need to do this Several times, since
        upds = substitute (upds, ",,", ","); // ,,,, => ,,
        upds = substitute (upds, ",,", ","); // ,, => , and so on. 
        upds = substitute (upds, ",,", ","); 
        upds = substitute (upds, ",,", ","); 
        
        // There might be a comma at the end; we need to get rid of this. 
        // If the last empty rows end up with a single comma. 
        ll = length(upds);
        if (ll != 0) then
            if (right (upds, 1) == ",") then
                upds = left (upds, ll-1);
            end if
        end if

        // Anything to do? Run the Update SQL. "upds" now contains a comma-separated list of assignments. 
        if (upds != "") then    
            // Craft the SQL
            upds = format ("UPDATE db_InvoiceDescriptions SET %s WHERE Auto_nr=%s", upds, row[1]); 
            // Run it. 
            res = ExecuteSQL (upds); // Execute the UPDATE;  
            print (upds + "\n" + res + "\n"); // For debugging, see the console. 
            if (res != "") then // Non-blank result indicates an SQL error. 
                SQL_close (lq); 
                return res; 
            end if
        end if
    end for
    SQL_close (lq); 
    return "OK";
end

In the approval script, we now just call this, instead of the old function that was buggy. 

    // In the FileMaker Script to approve the invoice. 
    Set Variable [ $res; ACF_Run("ApproveInvoiceLinesCalc"; InvoiceHeader::InvoiceNo)]
    if ( $res = "OK")
        // Everything went OK
    else
        // Handle the error. 
    end if

This implementation handled the unstability issue 100%.

References:


Back to top

Example: Importing Timesheets from Kimai to FileMaker

Kimai is an open-source timesheet application written in PHP. It's easy to install on a web-hosting server, configure with users, customers, projects, activities, and more. However, as FileMaker enthusiasts, we often need this data within FileMaker itself. While a web-based system is excellent for tracking registrations, as managers, we require this data in FileMaker for more comprehensive analysis and integration. The solution presented here involves using an ACF function to seamlessly import all registrations into a timesheet table in FileMaker. Once the data is within FileMaker, you can use it for various purposes, including invoicing. You can also extend this example by creating new projects and customers directly from FileMaker.

Kimai image

Setting Up Kimai:

To implement this solution, I set up an EC2 instance on Amazon, installed the ispConfig control panel, and then deployed Kimai on a web site. Kimai offers an export function that allows you to export data to Excel, which I imported into FileMaker manually. While this approach worked, it involved repetitive manual operations. Therefore, I used the ACF plugin to create a function that interacts directly with the MySQL database of Kimai.

Alternate solution without ACF

While it is possible to pull this off without ACF, I think this solution presented here gives you a very fine-tuned and direct solution to the task. Doing it without ACF would require you to buy a ODBC driver software for your Mac, and then configure it to work with Kimai. Then in FileMaker you could connect the tables kimai2_timesheet, kimai2_projects, kimai2_customers, kimai2_activities and kimai2_users as external data-sources. Then use the "import" script-step in filemaker to import the data.

Timestamp Challenges:

During this process, I encountered timestamp discrepancies between the MySQL database and FileMaker. The MySQL timestamps were in Zulu time, while the imported timestamps were in the local time zone, two hours ahead. To resolve this, I added a timestamp conversion step to the function, aligning the times accurately.

Database table in FileMaker:

Here is the Table setup for the "TimeSheet" table in FileMaker.

FieldName Datatype Description
FromTS TimeStamp Starting time for the timesheet record
ToTS TimeStamp Ending time for the timesheet record
Kimai_ID Number The ID from the timesheet table in Kimai, if needed to link in other operations
UserName TEXT The username in Kimai
RegDate TimeSTamp The registration date and time
Customer TEXT The Customer owning the prosject that has been working on
Project TEXT The Project name
Activity TEXT The Activity name (product for invoicing)
Duration TIME The duration for the activity, as time (HH:MM:SS)
Description TEXT The Work Description the user has endtred in Kimai
DurationDec Calculation, Number = Hour ( Duration ) + ( Minute ( Duration )/60 ) + (Seconds ( Duration) / 3600 )


You can then create the neccessary relations in FileMaker and add some calculated fields to whatever neccessary. If you allready has this table, but different names you can adjust the SQL insert in the ACF function to fit whatever needed in your solution.

As in my example, I say that activity named FREE should not be invoiced, and "MEETINGS" should go with 60% timeconsume for invoicing. I have then added those two calculations.

FieldName Datatype Description
TimeFactor Calculation, Number = Case ( Activity="FREE" ; 0 ; Activity="MEETINGS" ; 0,6 ; 1)
TimeToInvoice Calculation, Number = DurationDec*TimeFactor



ACF Functions:

See the source code of the ACF functions below:

Package Kimai "ACF functions for Kimai integration"

// Some utility functions
// -----------------------------------------------------

// MySQL connection function. Makes it easy to connect from other functions as well, if example is extended with other functions. 
function connect_kimai_db ()
    // set in your own values below to do a successfull connect. 
    int db = mysql_connect ("kimai host", "3306", "mysql user", "mysql password", "kimai databasename"); 
    return db; 
end 

// Do string to timestamp conversion. Using "dformat" as filemakers current date format. 

function SQL_to_timestamp ( string s, string dformat )
    timestamp ts;
    
    date d = date ( left(s,10), "%Y-%m-%d"); 
    s = string ( d, dformat) + substring ( s, 10); 
    ts = s; 
    return ts  ; 
end 

// The main function to be called from a FileMaker script
// ------------------------------------------------------
function sync_timesheet ()
    string dformat; 
    // Auto-detect date format used by FileMaker, for use in timestamp asignments. 
    dformat = @date(11;12;2023)@;
    dformat = substitute ( dformat, "11", "%m");
    dformat = substitute ( dformat, "12", "%d");
    dformat = substitute ( dformat, "2023", "%Y");
    
    // Connect to the mySQL database
    int db = connect_kimai_db();
    
    // Set up the mysql query to fetch the new rows we haven't imported before. 
    string sql = "SELECT ts.id, ts.user, ts.activity_id, ts.project_id as projectID, cust.id as customerID, 
    start_time, end_time, duration, description, pro.name as proName, cust.name as custName, act.name as actName, 
    exported, u.username
    FROM kimai2_timesheet as ts
    left join kimai2_projects as pro on pro.id = ts.project_id
    left join kimai2_customers as cust ON cust.id =  pro.customer_id
    left join kimai2_activities as act ON act.id = ts.activity_id
    left join kimai2_users as u ON   u.id =  ts.user
    WHERE exported=0;";
    
    int rs = MySQL_Query ( db, sql );
    int rrc = mySQL_GetRowCount (rs); 
    string dur, res; 
    
    array string remrow; 
    
    int i, import_count=0, h, m, s; 
    array string doneIDs;  
    
    timestamp rts1, rts2;
    
    // Process each row in a loop
    for ( i = 1, rrc )
        remrow = mySQL_GetRow (rs); 
                // print format ("\nColumns for row %d: %d", i, sizeof ( remrow ));

        // Duration in Kimai is in seconds, convert it to a TIME format. 
        s = long ( remrow[8] ); 
        h = s / 3600 ; s = s - h*3600;  
        m = s / 60; s = s - m*60; 
        dur = format("%02d:%02d:%02d", h, m, s); 
        
        // Adjust the timestamps to local time-zone
        rts1 = SQL_to_timestamp(remrow[6], dformat)+7200;
        rts2 = SQL_to_timestamp(remrow[7], dformat)+7200;
        remrow[6] = string ( rts1, "%Y-%m-%d %H:%M:%S");
        remrow[7] = string ( rts2, "%Y-%m-%d %H:%M:%S");
        
        // Create the SQL for insert into TimeSheet table
        sql = format ( "INSERT INTO TimeSheet (Kimai_ID, UserName, RegDate, FromTS, ToTS, Customer, Project, Activity,Duration, Description) 
            VALUES (%d, '%s',TIMESTAMP '%s', TIMESTAMP '%s', TIMESTAMP '%s', '%s', '%s', '%s', TIME '%s','", 
            int(remrow[1]),remrow[14],remrow[6],remrow[6], remrow[7], remrow[11], 
            remrow[10], remrow[12], dur ) 
            + substitute(remrow[9],"'", "\'")+"')"; 

        // Run the SQL
        print "\n"+sql; 
        res = executeSQL ( sql ) ; 
        if ( res == "" ) then
            import_count += 1; // Successfull insert
            doneIDs[]=remrow[1];
        else
            print "\n"+res; // Some error
        end if 
    end for
    
    // Update the Timesheet with the "exported" field set to 1, so we dont import it next time. 
    if ( sizeof (doneIDs)>0) then
        sql = format ( "UPDATE kimai2_timesheet SET exported=1 WHERE id IN (%s)", implode ( ",",doneIDs)); 
        print "\n"+sql+"\n";
        res = executeMySQL (db,  sql);
        print res; 
    end if
    
    // Close the database
    mysql_close ( db);

    // Return status to the calling script. 
    if (rrc != import_count) then
        return format ("Some records did not import well, %d imported, %d has errors, See the console output for details", 
            import_count, rrc-import_count);
    else
        $ImportStatus = format ("Success: %d timesheets imported", import_count);
        return "OK"; 
    end if
end

A button to run this:

A script haveing this script step will run your import, suppose we have a field in our table, as a global field to hold the console output, so we can see how the import perform. The $res variable should be exmined for the "OK" return from the function. If it throws some error, they could be present in a custome dialogue, to example. 

Set variable [$res; ACF_Run ("sync_timesheet")]
Set field [AnyTable::Console; ACF_GetConsolleOutput]
If [$res ≠ "OK"]
    Show Custom Dialog ["Import errors"; $res]
else
    Show Custom Dialog ["Import Success"; $ImportStatus]
End If

Run this script after adding one new timesheet in Kimai, shows this console output (From the print statements in the code). Also a success message with one new timesheet. 

INSERT INTO TimeSheet (Kimai_ID, UserName, RegDate, FromTS, ToTS, Customer, Project, Activity,Duration, Description) 
            VALUES (35, 'olekeh',TIMESTAMP '2023-10-12 12:45:00', TIMESTAMP '2023-10-12 12:45:00', TIMESTAMP '2023-10-12 13:00:00', 'TestCompany LTD', 'Project-X', 'MEETINGS', TIME '00:15:00','Phone meeting with Gjerterude, looking at the statistics')
end-for
UPDATE kimai2_timesheet SET exported=1 WHERE id IN (35)

Data Processing:

Once the ACF function syncs the data from Kimai into FileMaker, you can effectively manage, analyze, and utilize the timesheets for various tasks. With the data in FileMaker, you can perform invoicing, reporting, and other essential operations.

References:

With this solution, you can effortlessly import and synchronize timesheets from Kimai into FileMaker, streamlining your workflows and enhancing your data management capabilities.


Back to top

Sending Form Data from FileMaker to a Web Service

Web services come in many different formats, be it JSON, XML, or simple HTML form data. The latter, though comprehensive to post from FileMaker, is rather easy to post from an HTML page with fields for manual data entry.

Example:

Let's assume we have an HTML form like this:

<!DOCTYPE html>
<html>
<head>
    <title>Sample Form with File Upload</title>
</head>
<body>
    <form action="target_url_here" method="POST" enctype="multipart/form-data">
        <label for="name">Name:</label>
        <input type="text" id="name" name="name" required><br><br>

        <label for="address">Address:</label>
        <input type="text" id="address" name="address" required><br><br>

        <label for="city">City:</label>
        <input type="text" id="city" name="city" required><br><br>

        <label for="zip">Zip:</label>
        <input type="text" id="zip" name="zip" required><br><br>

        <label for="email">Email:</label>
        <input type="email" id="email" name="email" required><br><br>

        <label for="file">Upload a File:</label>
        <input type="file" id="file" name="file"><br><br>

        <input type="submit" value="Submit">
    </form>
</body>
</html>

The POST request sent to the web server looks like this:

Content-Type: multipart/form-data; boundary=----WebKitFormBoundary7MA4YWxkTrZu0gW

----WebKitFormBoundary7MA4YWxkTrZu0gW
Content-Disposition: form-data; name="name"

John Doe
----WebKitFormBoundary7MA4YWxkTrZu0gW
Content-Disposition: form-data; name="address"

123 Main Street
----WebKitFormBoundary7MA4YWxkTrZu0gW
Content-Disposition: form-data; name="city"

Sample City
----WebKitFormBoundary7MA4YWxkTrZu0gW
Content-Disposition: form-data; name="zip"

12345
----WebKitFormBoundary7MA4YWxkTrZu0gW
Content-Disposition: form-data; name="email"

johndoe@example.com
----WebKitFormBoundary7MA4YWxkTrZu0gW
Content-Disposition: form-data; name="file"; filename="example.txt"
Content-Type: text/plain

[Contents of the uploaded file go here]
----WebKitFormBoundary7MA4YWxkTrZu0gW--

If the file(s) are binary, they usually need to be in base64-encoded format.

Sending data from FileMaker to a web service in this format can be challenging, especially when you have to attach Base64-encoded files to the post.

In this practical example, we need to send an invoice to a service to produce EDI documents in an Electronic Trade Format (EHF) for a customer, and we are using a web service to create the XML. The service accepts up to five attached PDF files with the invoice, including the invoice itself. To easily send this data, we have created a custom function to format the POST data as shown above and send it. We initially developed this solution some time ago, and now, I have recreated it with the ACF plugin to improve error handling and readability.

When it comes to error handling, ACF provides automatic exception handling. In the event of an exception occurring within the code, such as a FileMaker calculation returning a question mark (?), our previous custom function failed to detect and handle this appropriately. Consequently, it continued execution, leading to inaccurate results that were not easily discernible to the user.

In the new functions, I deliberately introduced an error to test the error handling mechanism. To my satisfaction, the system promptly responded with the following message:

Error message

Now, we can handle the situation and get correct result produced.

The error shown, was the return from the call to the function, so we check for the text ERROR in it....

# Create and send the Invoice
Set Variable [$EHF; Value: ACFU_EHFService ($$ApiKey;......)]
# Did it go well?
If [Position ( Left ( $EHF ; 100 ) ; "ERROR" ; 1 ; 1 ) > 0]
   Show Custom Dialog ("Error during EHF generation"; $EHF)
   Exit Script [Text Result: $EHF]
End If
...
...

Additionally, I have created a YouTube video explaining the functionality, including the Base64 encoding of binary files.

The ACF code is provided below, allowing you to copy and paste it into your own ACF libraries and customize it according to the web service requirements.

The example includes the use of two other plugins too. The TROI_Url, and Baseelements. 

package EHFsupportFunctions "Support functions for the EHF package 2";

function GetNameFromPath ( string path ) 
    FunctionID 3001;
    if ( isWindows ) then
        path = substitute ( path, "\\", "/");
    end if
    return regex_replace ("^(.*\/)?(.+\.[a-zA-Z0-9]{1,10})$", path, "$2"); 
end

/*
ACF functions and FileMaker calculation engine uses different variables. ACF variables is local to the plugin. 
When doing an assignment like '$$LastPost = postdata', the plugin in fact do a FileMaker calculation 
like: 

Let([$$LastPost = "<text content in postdata>"];"")

That calculation assign the value to the FileMaker variable. 
However, if the length of the text is more than 32K, this will fail due to limitations in string literals.   
We use a Getter function to pull this off instead as we asume the length of postdata will be considerably longer. 

Global declaration - Technique to handle large text assignment from function. 
*/
string LastEHFPost; 
// Getter function...
function GetLastEHFPost ()
    FunctionID 3501; // makes ACFU_GetLastEHFPost when called from FileMaker directly without ACF_Run.
    return LastEHFPost;
end


function EHFAttachement (string path, string Name, string Boundary )

    if ( path == "" ) then
        return "";
    end if
    
    string fn = GetNameFromPath(path);
    string v  = Boundary;
    v = v + format ('Content-Disposition: form-data; name="%s"; filename="%s"\r\n', Name, fn);
    v = v + "Content-Type: text/plain\r\n\r\n";
    
    // Use system commands to convert attachement to Base64. 
    string base64path;
    base64path = path+".base64"; 
    // Different commands for Mac and Windows
    if ( isMac ) then
        // Adjust absolute paths to match standard unix paths. 
        if ( left ( path, 8) != "/Volumes" && left ( path, 6) != "/Users" && left (path,1) == "/") then
            path = "/Volumes"+path; // Paths lack /Volumes as start... - like "/Macintosh HD...."
            base64path = "/Volumes"+base64path;
        end if  
        $$syscmd = format ('openssl base64 -in "%s" -out "%s"', path, base64path);
    else
        $$syscmd = format('cmd.exe /c certutil -f -encode "%s" "%s"', path, base64path);
    end if

    // Verify that the file exists. 
    if ( ! file_exists ( path ) ) then
        throw format ("ERROR: The Attached file '%s' at the path '%s' does not exists", fn, path ); 
    end if

    // Use Baseellements plugin to perform the system command
    $$SysRes = @BE_ExecuteSystemCommand ( $$syscmd ; 10000 )@;
    
    // Retrieve the base64 text
    int x = open (base64path, "r" );
    string base64 = read (x); 
    close (x); 
    if ( isWindows ) then
    // CertUtil makes a Certificate header and trailer that need to be removed. 
        base64 = between (base64, "-----BEGIN CERTIFICATE-----\r\n", "-----END CERTIFICATE-----"); 
    end if 
    // cleanup
    string res = delete_file ( base64path); 
    // return the part...
    return v + base64;
end

function FormField (string vb, string name, string content)
    return format ('%sContent-Disposition: form-data; name="%s"\r\n\r\n', vb, name) + content + "\r\n";
end


function EHFService ( string APIkey, string Bank, string FirmaNavn, string Adresse1, string Adresse2,
    string Postnr, string Sted, string OrgNr, string InvoiceBlock, 
    string AttPath1, string AttDesc1, 
    string AttPath2, string AttDesc2, 
    string AttPath3, string AttDesc3,
    string AttPath4, string AttDesc4,
    string AttPath5, string AttDesc5 )
    
    FunctionID 3502; // makes ACFU_EHFService when called from FileMaker directly without ACF_Run. 
    
    // Remove non-numeric characters from the Org-number
    OrgNr = regex_replace ( "[^0-9]",OrgNr, ""); 
    
    // format the post data to look like its sent from a HTML form. 
    string vb1 = "----WebKitFormBoundaryvaBIACFqQXnhryXmJxO";
    string vb2 = "--" + vb1 + "\r\n";
    string vb3 = "--" + vb1 + "--\r\n";
    string vUrl = "https://gw.ccx.no/ws/EHF20/";
    
    string h1 = "APIkey||Bank||FirmaNavn||Adresse1||Adresse2||Postnr||Sted||OrgNr" + Char(10); 
    string h2 = APIkey  + "||" + Bank + "||" + FirmaNavn + "||" + Adresse1 + "||" + Adresse2 + "||" + 
        Postnr + "||" + Sted + "||" + OrgNr ;
    
        // Build the Post...
    string postData = FormField ( vb2, "CompanyDetails", h1+h2) +
        FormField ( vb2, "InvoiceDetails", InvoiceBlock) +
        FormField ( vb2, "File1Desc", AttDesc1) +
        FormField ( vb2, "File2Desc", AttDesc2) +
        FormField ( vb2, "File3Desc", AttDesc3) +
        FormField ( vb2, "File4Desc", AttDesc4) +
        FormField ( vb2, "File5Desc", AttDesc5) +
        EHFAttachement ( AttPath1, "File1", vb2) +
        EHFAttachement ( AttPath2, "File2", vb2) +
        EHFAttachement ( AttPath3, "File3", vb2) +
        EHFAttachement ( AttPath4, "File4", vb2) +
        EHFAttachement ( AttPath5, "File5", vb2) + vb3; 
    
    
    // $$LastPost = postData; 
    // Use a different technique to assign the postdata to filemaker variable, as mention above. 
    
    LastEHFPost = postData; 
    string cc = @@Let([
        v = ""; 
        $$LastPost = ACFU_GetLastEHFPost
    ];
    v)@@;

    // Use Troi_URL plugin to send the data to the web-service. 
    string res = eval (format ('TURL_SetCustomHeader("-unused " ; "Content-Type: multipart/form-data; boundary=%s")', vb1) );
    string post = format('TURL_Post( "-TimeoutTicks=5000 -NotEncoded -Encoding=UTF8" ;"%s";$$LastPost)', vUrl); 
    string resData = eval ( post); 
    res = @TURL_SetCustomHeader("-unused "; "")@;
    return resData; 
end

Speed Comparison

With both the new and old functions available simultaneously, conducting a speed comparison was a straightforward process.

The plugin provides two functions for measuring execution time: ACF_StartDurationTimer and ACF_GetDuration_uSec. To perform the test, I modified the script calling the functions as follows:

# Measure the ACF Method
Set Variable [$t1; ACF_StartDurationTimer]
Set Variable [$EHF; Value: ACFU_EHFService ($$ApiKey;......)]
Set Variable [$t1; ACF_GetDuration_uSec]
# then $t1 contains the number of uSeconds between start and get. 

# To compare use the old standard custom functions:
Set Variable [$t2; ACF_StartDurationTimer]
Set Variable [$EHF; Value: EHFService ($$ApiKey;......)]
Set Variable [$t2; ACF_GetDuration_uSec]
# Then $t2 contains the numer of uSec for this method. 

# Now we can present both.
Show Custom Dialog ["Time Consume:";"ACF Function: " & $t1 & ¶ & "Standard function: " & $t2]

It is anticipated that this process may take some time to execute, as it encompasses both the conversion of attachments to base64 format and the utilization of an external web service to generate the XML, in addition that we also are handling text in order of megabytes. The numbers are in microseconds.

I attempted the process multiple times, and the results consistently fell within the same time range. On each occasion, the ACF function executed twice as quickly. 0.25 seconds versus 0.5 seconds, approximately.

Speed compare

Considering that the response time for the web service and the base64 conversion remain consistent in both cases, when we account for these times during the comparison, it becomes evident that the ACF functions execute remarkably quickly. Although I haven't precisely measured these times, I estimate they would be at least 150-200 milliseconds. Let's assume 150 milliseconds, leaving us with 100 milliseconds for the ACF and 320 milliseconds for the standard custom function, making the ACF solution approximately three times faster. If it were 200 milliseconds, then the ACF time would be 50 milliseconds and the standard time 270 milliseconds, translating to roughly 5.4 times faster. This outcome is close to what we've seen in comparison to earlier studies.


Back to top

Markdown Functions

In this section, we provide an example of a package that facilitates the creation of HTML files from Markdown documents. Markdown is a popular format for technical documentation due to its focus on clean, readable text, easy integration of code examples, and support for images and hyperlinks. We have integrated Markdown conversion functions into the ACF language to simplify the process of generating HTML documents.

The result is a blistering fast end product, the html files loads extremely fast, are looking good, and the functions to do the converting is also very fast. We processed the 50 documents in this manual in less than 0.5 seconds in total. With the CSS files, you can style the documents in any way you want.

Background

For our documentation project, we've chosen to create HTML documents from Markdown source files. To achieve this, we use a FileMaker application that includes:

Workflow

The Markdown to HTML conversion process involves three phases:

  1. Pre-processing: This step involves copying images from Markdown documents to the HTML/images folder and updating image URLs in the Markdown documents to point to their new locations.
  2. Markdown to HTML Conversion: The Markdown documents are converted to HTML using ACF functions.
  3. Post-processing: After conversion, we add a header and a left column navigation bar to the HTML documents.

Pre-processing

In the pre-processing phase, we perform the following tasks:

The Markdown image tag has this format:

![image 1](../../../Desktop/regex-explanation.png)

We use a regular expression to extract image tags from the Markdown document. The regex pattern !\[[^\]]*\]\(((.+?\/)?([^\/)]+))\) captures image tags, including full paths, directory paths, and file paths.

This is explained this way: (Screen shot from regex101.com)

image 1

Here is the different Paths we need to take care of: 


Original Image path when dragged into the MarkDown document from the Desktop:  
../../../Desktop/imageab.png

Relative Image path for its new location as seen from the MarkDown document: 
 ../html_root_folder/images/imageab.png

Resulting image path in the HTML document: 
images/imageab.png

The absolute path of the document folder is: 
/Users/ole/Projects/ACFmanual/mdDocs/

The absolute path of the HTML path is: 
/Users/ole/Projects/ACFmanual/html_root_folder/

Now, we have some filesystem fun to pick apart all these paths and copy files. This is done in the MoveImages function seen below.

Markdown to HTML Conversion

After completing the pre-processing stage, we have all the referenced images available in the HTML/images folder. We utilize the built-in functions in the ACF language to perform the Markdown to HTML conversion. This function requires the source Markdown file, style/theme information defined for the markdown2html ACF function, and the destination HTML file to be created.

Once this function is executed, we obtain a bare HTML file that contains only the text and images from the Markdown document, without any navigation, headers, or additional elements. To transform this into a comprehensive manual, we require additional elements, which are handled by the post-processing function.

Post-processing

The post-processing function's role is to integrate the generated HTML into a complete page that includes a left sidebar, top bar, navigation, and bottom section.

  1. The left navigation menu needs to be generated. We retrieve all the documents from a table named ACF_Documents, extracted from the database using an SQL query. The left navigation menu is then constructed in a loop, organizing documents into category sections and using HTML ul/li tags. CSS styling is applied to enhance its appearance.
  2. The original HTML file is read and divided into the HEAD and BODY sections.
  3. The content for the top bar is sourced from user preferences.
  4. All these components, including DIV tags to structure the complete document, are placed into an array.
  5. We utilize the implode function to merge all the parts within this array to create the final document.
  6. A simple substitution is employed to remove certain image path prefixes in a cleanup operation.
  7. We iterate through all the documents again, replacing titles within the document with links leading to their respective pages.

Finally, the fully assembled HTML file is written back to disk, completing the process.

The ACF code functions sumary

Here's a breakdown of the functions involved in Markdown conversion:

  1. The Package Header:

    package MarkDownFunctions "MarkDown funksjoner for dok prosjektet...";

  2. The Function for Preprocessing the Markdown Document and Copying Image Files:

    This function extracts image tags from the Markdown document, copies image files to the HTML/images folder, and updates image URLs.

    function MoveImages(string SourceFile, string html_root, string relpath)

  3. The Post-processing Function to Add Header and Left Column to the Document:

    This function adds a header and a left column navigation bar to the generated HTML document.

    function post_processing(string htmlfile, string removeText)

  4. The Markdown Document Conversion Function:

    This function orchestrates the entire Markdown to HTML conversion process.

    function ConvertMarkdown(string sourcefile, string htmlfile, string html_root, string style, string codestyle, string removeText)

  5. A Utility Function to Create HTML Filenames:

    This function generates HTML filenames based on the Markdown file's name.

    function createHTML_Filename(string file, string html_path)

The full listing Listing:

The Package Header:


package MarkDownFunctions "MarkDown funksjoner for dok prosjektet...";

Here is the MoveImages function:


function MoveImages ( string SourceFile, string html_root, string relpath ) 

    print "MoveImages  enters...."; 
    int x = open ( SourceFile, "r"  ); 
    string md = read (x ) ; 
    close ( x ) ; 
    print "Pre prosessing"; 
    string regex = "!\[[^\]]*\]\(((.+?\/)?([^\/)]+))\)"; // used to extract image tags...
    // gr 0 = full match, gr 1 = full path, gr 2 = directory path, gr 3 = file path. 
    array string tags = regex_extract ( regex, md ) ; 
    int no = sizeof ( tags ) ; 
    if ( no == 0 ) then
        print "Pre prosessing - no tags"; 
        return "";      
    end if
    
// Getting the Markdown document folder where relative images starts from. 
    string docFolder = regex_replace ( "^(.+\/)[^\/]+$", SourceFile, "\1" ) ; 

    int i, cnt = 0; 
    string fulltag, fullpath, fileFullPath, dir, file, newPath, newRelPath, newtag, res; 
    
// Loop all tags, and process each one of them. 
    for ( i= 1, no)
        print "\n" + tags[i]; 
        fulltag = getValue ( tags[i], 1);
        fullpath = getValue ( tags[i], 2);
        fileFullPath = fullpath; 
        dir = getValue ( tags[i], 3);
        file = getValue ( tags[i], 4);
        if ( file_exists ( fullpath ) == 0 ) then
             // probably a relative path. Prefix with doc folder to get absolute path. 
            fullpath = docFolder + fullpath; 
        end if
        if ( file_exists ( fullpath ) ) then
            newRelPath = relpath + "images/" + file; 
            newPath = html_root + "/images/" + file; 
            if ( file_exists ( newPath ) == 0) then
                res = copy_file ( fullpath, newPath ) ; 
                if ( res == "OK") then
                    newtag = substitute ( fulltag, fileFullPath, newRelPath ) ; 
                    md = substitute ( md, fulltag, newtag ) ; 
                    cnt++; 
                end if
            end if
        end if
    end for
    if ( cnt > 0 ) then
        // save the modified MarkDown document. Take a backup first, in case we did something nasty...
        res = copy_file ( SourceFile, SourceFile+".bak" ) ; 
        // Write back to file. 
        x = open ( SourceFile, "w"  ); 
        write ( x, md ) ; 
        close ( x ) ;
    end if
    return "OK"; 
END

The Post processing function to add header and left column to the document:


function post_processing (string htmlfile, string removeText)

/* Called from ConvertMarkdown 
    Open the generated HTML document, that is a plain page of the markdown doc, 
    and put on a header, and a left navigation bar.
   */

    print "post processingn";
    int x = open ( htmlfile, "r"  ); 
    string html = read (x ) ; 
    close ( x ) ; 
    string body_part = between ( html, "<body>", "</body>" ) ; 
    string head_part = between ( html, "<head>", "</head>" ) ; 
    string stylesheet = '<link rel="stylesheet" type="text/css" href="include/css/style.css">';
    string top_bar = Preferences::topp_nav; 
    
    // Build the left nav
    string left_nav = Preferences::Left_nav; 
     
doclist = @ExecuteSQL ( "SELECT Title, slug_filename, Category FROM ACF_Documents LEFT OUTER JOIN ACF_Categories as c ON c.PrimaryKey = Category_UUID ORDER BY Sorting, Category" ; "||" ; "|*|")@;
    
    print doclist;  // Debugg print to consolle. 

    array string docs = explode ("|*|", doclist ), flt ; 
    int noDocs = sizeof ( docs ); 
    int i; 
    string curCat = "noone"; 
    for (i = 1, noDocs)
        flt = explode ( "||", docs[i]); 
        if (flt [3] != curCat ) then
            if ( curCat != "noone") then
                left_nav += "</ul>\n"; 
            end if
            left_nav += "<h4>" + flt[3] + "</h4>\n"; 
            left_nav += '<ul class="linklist">\n'; 
            curCat = flt[3]; 
        end if
        left_nav += "<li>" + flt[1] + "</li>\n"; 
    end for
    left_nav += "</ul>\n"; 
    
    // Merge the parts
    array string fileparts = {"<!DOCTYPE html>\n<head>", head_part, stylesheet, "</head>\n<body>", 
        '<div class="hd">', top_bar, 
        '</div><div class="content_band"><div class="left">',left_nav, 
        '</div><div class="content">', body_part,  
        "</div></div>", "</body>n</html>\n" }; 
        
    string result = implode ( "\n", fileparts ) ; 

    // Remove Image path prefixes 
    result = substitute (result, removeText, ""); 
    
    // substitute all titles in the documents with a link to that page. 
    for (i = 1, noDocs)
        flt = explode ( "||", docs[i]); 
        result = substitute (result, flt[1], '<a href="'+flt[2]+'">' + flt[1]+"</a>"); 
    end for

    // Write back to file. 
    x = open ( htmlfile, "w"  ); 
    write ( x, result ) ; 
    close ( x ) ;
    print "End-post-processingn"; 
    return "OK"; 
end

The markdown document conversion function:

This function below uses the two functions above to complete the full conversion. 


function ConvertMarkdown (string sourcefile, string htmlfile, string html_root, string style, string codestyle, string removeText)
    
    print "Convert markdown enters....\n"; 
    set_markdown_html_root ( html_root ) ; 
    string sf = sourcefile, df = htmlfile; 
    $$SourceFile = ""; 
    if (sf == "") then
        sf = select_file ("Velg en Markdown Fil?"); 
        $$SourceFile = sf; 
    end if
    if (sf != "") then
        if (df == "") then
         df = save_file_dialogue ("Lagre fil som?", "xx.html", html_root);
        end if
        if (df != "") then
                 // Pre processing - Handle images in document. 
            string res = MoveImages ( sf, html_root, removeText );
                // Main conversion
            res = markdown2html (sf, style+","+codestyle, df); 
                // Post processing...
            res = post_processing (df, removeText); 
        end if 
    end if
    return "OK";
end

A small utility function to produce html filenames:


function createHTML_Filename ( string file, string html_path ) 

    string newfile = substitute ( lower(file), " ", "_"); 
    newfile = substitute ( newfile, ":", "_"); 

    if (right(html_path, 1) != "/") then
        html_path += "/"; 
    end if
    return html_path + newfile + ".html";
end

This example demonstrates how ACF can be used to automate the conversion of Markdown documents into HTML, making it easier to manage technical documentation projects.


Back to top

Practical Example - Read CREMUL payments file and update database

This is a complete example of how to parse and import data from a CREMUL (Credit Transfer Initiation) payments file into a database using ACF (Advanced Custom Functions for FileMaker) functions. Here's a breakdown of what this code does:

  1. It defines several functions for creating SQL insert statements for different types of data, such as message headers, sequences, and transactions.

  2. It reads the contents of a CREMUL file and splits it into segments, where each segment is separated by a single apostrophe ('').

  3. It then iterates through the segments and processes each one based on its type, extracting relevant information and populating variables.

  4. The code organizes the data into three arrays: dataMH for message headers, dataSQ for sequences, and dataTR for transactions.

  5. It inserts the data from these arrays into the corresponding database tables using SQL insert statements.

  6. The code also performs some data validation and error checking to ensure that the CREMUL file is well-formed.

  7. It includes a function import_cremul_select_file that allows the user to select a CREMUL file interactively and then import its data into the database.

This code is intended to be used as part of a system for processing and storing payment data from CREMUL files, making it easier to generate reports for the financial department. The code appears to be well-documented and includes error handling to ensure that data is imported correctly.

Please note that to use this code effectively, you would need to integrate it with your FileMaker structure. Additionally, you may need to customize the code to match the specific database schema and requirements of your application.

The Cremul File Format

Here is a little sample of a CREMUL file we are going to read into the database. Usually there is several segments on each line only separated by a single apostrophe ('). 


UNH+4+CREMUL:D:96A:UN' 
BGM+435+20010629170120128' 
DTM+137:20010629:102' 

LIN+1' 
DTM+209:20010418:102' 
BUS++DO++230:25:124' 
MOA+349:124113,53' 
RFF+ACK:01918100001' 
DTM+171:20010418:102' 
FII+BF+97600224105'

SEQ++1'
DTM+203:20010418:102'
FII+OR+83801915348'
RFF+ACD:101089152'
RFF+AEK:91521461'
MOA+143:3808,11'
NAD+PL+++HANS ESPEN'
INP+BF+2:SI'
FTX+AAG+++FROM?:HANS ESPEN PAID ON?: 18.04.01' 
PRC+8'
DOC+999+0000773500050154208143212' 
MOA+12:3808,11'
GIS+37'

SEQ++2'
DTM+203:20010418:102'
FII+OR+36251403893'
RFF+ACD:362589343'
RFF+AEK:53191521504'
MOA+143:4843,44'
NAD+PL+++Erik Jensen'
INP+BF+2:SI'
FTX+AAG+++FROM?: Erik Jensen PAID ON?: 18.04.01' 
PRC+8'
DOC+999+0000773500050209408106755' 
MOA+12:4843,44'
GIS+37'

SEQ++3'
DTM+203:20010418:102'
FII+OR+36260900980'
RFF+ACD:362689451'
RFF+AEK:94510009'
MOA+143:115461,98'
NAD+PL+++Øystein Gudmonson'
INP+BF+2:SI'
FTX+AAG+++FROM?: 3626.09.00980 PAID ON?: 18.04.01' 
PRC+8'
DOC+999+0000773500050211808028787' 
MOA+12:115461,98'
GIS+37'

UNT+50+4'

ACF Functions Example:

Here is the import function to parse this format. Then update the three database tables,

This makes it easy to generate a report for the Financial department... 


package cremul_import "Functions to read and import CREMUL data into database tables. ";

/*

The functions below are used to create SQL insert VALUES part that is put into arrays in the main function. 
The arrays are then merged with a comma separator to be all inserted in one single INSERT INTO SQL command. One for each file. 

*/
function createMessageHeader ( string MessageRef, date MessageDate, string FilePath )
    string FileName = regex_replace("^(.+)/(.+\..+)$", FilePath, "\2");
    string ImpBy = @get(AccountName)@;
    string TimeStampNow = string ( now()+3600, "%Y-%m-%d %H:%M:%S"); 
    string data = format ( "('%s',DATE '%s', '%s', '%s', TIMESTAMP '%s', '%s')", 
        MessageRef, string (MessageDate, "%Y-%m-%d" ), FileName, FilePath, TimeStampNow, ImpBy); 
    return data; 
end

function createSequenceLIN ( string MessageRef, int LIN, date ProcDate, 
                string BUSCat, float SeqAm, string SEQBankRef, date valDate, string RecAccNo)
    string data = format ( "('%s',%d, DATE '%s', '%s', %.4f,'%s',DATE '%s','%s')", 
        MessageRef, LIN, string (ProcDate, "%Y-%m-%d" ),BUSCat, SeqAm, SEQBankRef, 
        string (valDate, "%Y-%m-%d" ), RecAccNo); 
    return data; 
end

function createTransaction ( string MessageRef, int LIN, int seq, date recDate, string ArcRef, string OppdragRef, 
        float transfAm, string PayerAccNo,  string PayerName, string Freetext, string INP, int Proc, string DocRef, 
        float ProcAm, int genInd, string NADPL, string KID)
        
    if ( PayerName == "" ) then
        PayerName = NADPL; 
    end if
    
    string data = format ( "('%s',%d, %d, DATE '%s', '%s', '%s', %.4f, '%s', '%s', '%s', '%s', %d, '%s', %.4f, %d, '%s')",  
        MessageRef, LIN, seq, string (recDate, "%Y-%m-%d" ), ArcRef, OppdragRef, 
        transfAm, PayerAccNo,  PayerName, Freetext, INP, Proc, DocRef, 
        ProcAm, genInd, KID); 
    return data; 
end

/*

Helper functions to facilitate some operations in the main function. 

*/

function assert ( string value, string v1, string v2, string message)
    if (value == v1 || value == v2 ) then
        return true; 
    else
        throw "ERROR Assert " + message + " value: " + value; 
    end if
    return false; 
end

function getDate (string datevalue, string formatcode ) 
    date dateval;  
    bool b1 = assert (formatcode, "102", "203", "Invalid formatcode in DTM, should be 102 or 203, is: " ); 
    if ( formatcode == "102") then
        dateval = date (datevalue, "%Y%m%d" ); 
    elseif ( formatcode == "203" ) then
        dateval = date (left ( datevalue, 8 ) , "%Y%m%d" );
    else
    end if

    return dateval; 
end

function getAmounth ( string number ) 
    double xx = double ( substitute (number, ",", ".")); 
    return xx; 
end

/*

Here is the main function. It can be called with a file-path to the CREMUL file, OR use the 
import_cremul_select_file () in the bottom to get a select dialogue to pick the file. 

*/

function import_cremul ( string filename )

    int x = open ( filename, "r"); // Plugins Exception handler handles any error and abort 
                                    // the functions returning a proper error message back to FileMaker.  
    string cremul_data = read ( x ); 
    close ( x ) ; 

/*
Each segment is terminated by a single aphostrophe ('). The file can contain several segments on one line. 
Remove all the line-separator (CR / LF) to get a long string of segments each terminated by the ('). 
Then - split in a array of segments to parce each one on a loop. 
    
*/ 
    // Clean and split the file in segments. 
    cremul_data = substitute ( cremul_data, "\r", ""); 
    cremul_data = substitute ( cremul_data, "\n", ""); 
    cremul_data = substitute ( cremul_data, "\t", ""); 
    
    array string cremul_segments = explode ( "'", cremul_data); 
    
    array string subsegs, subsub; 
    
    array string dataMH, dataSQ, dataTR; // The data arrays for the table data in SQL VALUES format. To be inserted in tables at the end. 
    
    int noSegments = sizeof (cremul_segments ); 
    bool b1; 
    
    string MessageIdentifier, UniqueMessageNumber, BUScat, refPostingACK, refCT, refArchive, refOrderNumber,
    receivingAccountNumber, payersAccountNumber, PayersName,
    NAD_MR, NAD_PL, INP, FriTekst, KID; 
    
    date DocumentDate, ReceivedDate, ProcessedDate, ValueDate, NETSProcessingDate; 
    
    float totalAmount, transactionAmount; // float are doubles in ACF - really. 
    
    int segNo, noSubSegs, currentMessageStart, LevB_LIN, sequence; 
    
    string subSegStart; 
    
    for ( segNo = 1, noSegments )
        if ( cremul_segments[segNo] != "") then
        print "\n" + segNo + ": "+ cremul_segments[segNo]; 
            subsegs = explode ( "+", cremul_segments[segNo]); 
            noSubSegs = sizeof ( subsegs ); 
            subSegStart = subsegs[1]; 
            subSegStart = regex_replace ( "^(\\s|\\n|\\r)*(.*)(\\s|\\n|\\r)*$",subSegStart, "\2"); 
            
            if ( subSegStart == "UNH" ) then  // Start of Message.
                // UNH+1+CREMUL:D:96A:UN
                MessageIdentifier = subsegs[2]; 
                currentMessageStart = segNo; 
                if ( subsegs[3] != "CREMUL:D:96A:UN") then
                    throw "Cremul document is not according to standard: CREMUL:D:96A:UN, but instead: " 
                        + subsegs[3] + " Contact your software vendor to update CREMUL Import to this standard."; 
                end if
                
            elseif (MessageIdentifier == "" ) then
                throw "ERROR cremul file lacks Message identifier"; 
                
            elseif (subSegStart == "BGM") then  // Beginning of message
                // BGM+435+1999090813150001
                UniqueMessageNumber = subsegs[3];
                b1 = assert ( subsegs[2], "435", "455", "BGM Header invalid code, should be 435 or 455, is: "); 
                
            elseif (subSegStart == "DTM") then
                // DTM+137:19970303:102
                // DTM+137:200003031208:203
                subsub = explode ( ":", subsegs[2]); 
                if (subsub[1]== "137" ) then
                    DocumentDate = getDate (subsub[2], subsub[3] ); 
                elseif ( subsub[1]== "171" ) then 
                    NETSProcessingDate = getDate (subsub[2], subsub[3] ); 
                elseif ( subsub[1]== "203" ) then  
                    ReceivedDate = getDate (subsub[2], subsub[3] );
                elseif ( subsub[1]== "193" ) then  
                    ProcessedDate = getDate (subsub[2], subsub[3] );
                elseif ( subsub[1]== "209" ) then  
                    ValueDate = getDate (subsub[2], subsub[3] );
                else
                    throw "Invalud DTM Date Qualifier: " + subsub[1]; 
                end if
                
            elseif (subSegStart == "LIN") then
                // Starts level B ( Sequence numbers unique inside this level. )
                // If sequence number set, flush transactions as we start on a new B segment. 
                // LIN+1
                
                if ( LevB_LIN != 0) then
                    dataTR[] = createTransaction ( UniqueMessageNumber, LevB_LIN, sequence, ReceivedDate, 
                        refArchive, refOrderNumber, 
                        transactionAmount, payersAccountNumber,  PayersName, FriTekst, INP, 0, "", 
                        0.0, 0, NAD_PL, KID); 
                    sequence = 0;   
                    
                    transactionAmount = 0.0; 
                    refArchive = "";        
                    refOrderNumber = ""; 
                    payersAccountNumber = ""; 
                    PayersName = ""; 
                    NAD_PL = ""; 
                    FriTekst = ""; 
                    KID = ""; 
                    INP = ""; 
                    
                end if
                LevB_LIN = subsegs[2]; 
                
                
                
            elseif (subSegStart == "BUS") then
                // BUS++DO++230:25:124
                subsub = explode ( ":", subsegs[5]); 
                BUScat = subsegs[3]+"."+subsub[1]; 
            elseif (subSegStart == "MOA") then
                // MOA+349:124113,53
                subsub = explode ( ":", subsegs[2]); 
                if ( subsub[1] == "60" || subsub[1] == "346" || subsub[1] == "349" || subsub[1] == "362") then
                    totalAmount = getAmounth (subsub[2]); 
                elseif ( subsub[1] == "143" ) then
                    transactionAmount = getAmounth (subsub[2]);
                else
                end if 
/* TODO: Find the use of those codes...
36 (Converted amount) 
60 (Final (posted) amount)
98 (Original amount – the original amount to be paid)
119 (Received amount)
143 (Transfer amount) */
                
            elseif (subSegStart == "RFF") then
                // RFF+ACK:00430409720 - (Bank reference – posted amount)
                // RFF+CT:001005001 - (AutoGiro agreement ID)
                // RFF+ACD:*94011106 - (Bank reference = Archive reference. The customer should tell the 
                    // archive reference to the bank if the customer has questions about a specific transaction)
                // RFF+AEK:6011489652 - (Payment order number. Bankgiro note number is unique. The
                    // number does not have to be unique when other payment instruments are used)
                subsub = explode ( ":", subsegs[2]);    
                if ( subsub[1] == "ACK") then
                    refPostingACK = subsub[2]; 
                elseif ( subsub[1] == "CT" ) then
                    refCT = subsub[2];
                elseif ( subsub[1] == "ACD" ) then
                    refArchive = subsub[2];
                elseif ( subsub[1] == "AEK" ) then
                    refOrderNumber = subsub[2];
                else
                    // Other refs: ABO, ACK, AFO, AGN, AHK, RA, TBR
                    // Investigate the use of those. 
                end if
            elseif (subSegStart == "FII") then
                // FII+BF+12345678901 - Kontnummer til mottaker
                // FII+OR+36251403893 - Betalt fra konto
                // FII+OR+23510524047:Jenny Petterson
                subsub = explode ( ":", subsegs[3]);
                if ( subsegs[2] == "BF") then
                    receivingAccountNumber = subsub[1]; 
                elseif  ( subsegs[2] == "OR") then
                    payersAccountNumber = subsub[1]; 
                    if ( sizeof ( subsub ) > 1) then
                        PayersName = subsub[2]; 
                    else
                        PayersName = ""; 
                    end if
                else
                    // no other known codes....
                end if

                    
                
            elseif (subSegStart == "SEQ") then
                // Start a new sequence
                // Flush old values to payment table, if sequence is different than 0. 
                // SEQ++1
                // Within same level B (starts with the LIN-segment), the first underlaying transaction on level C
                // starts with SEQ+1, the second transaction starts with SEQ+2, the third transaction starts with SEQ+3 etc.
                // (Data element 1050 Sequence number)
                
                
                
                if ( sequence != 0 ) then
                    // flush previous transaction, empty trans data. 
                    dataTR[] = createTransaction ( UniqueMessageNumber, LevB_LIN, sequence, ReceivedDate, 
                            refArchive, refOrderNumber, 
                            transactionAmount, payersAccountNumber,  PayersName, FriTekst, INP, 0, "", 
                            0.0, 0, NAD_PL, KID);
                            
                    transactionAmount = 0.0; 
                    refArchive = "";        
                    refOrderNumber = ""; 
                    payersAccountNumber = ""; 
                    PayersName = ""; 
                    NAD_PL = ""; 
                    FriTekst = ""; 
                    KID = ""; 
                    INP = ""; 
        
                end if
                
                sequence = int( subsegs[3] ); 
                
                if (sequence == 1 ) then
                    if ( LevB_LIN == 1) then 
                        // Flush the message header
                        dataMH[] = createMessageHeader ( UniqueMessageNumber, DocumentDate, filename ); 
                    end if

                   // Flush the LIN header
                   dataSQ[] = createSequenceLIN ( UniqueMessageNumber, LevB_LIN, NETSProcessingDate, 
                       BUScat, totalAmount, refPostingACK, ValueDate, receivingAccountNumber); 
                
                end if
                
            
            elseif (subSegStart == "NAD") then
                // Name and address (M1)
                // NAD+MR+00123456789-001234567 - Mottakers ORG nummer
                // NAD+PL+++Bjarne Frogner AS++Asker++1370 - Beatlers detaljer: 
                // NAD+PL+++Erik Jensen
                // NAD_MR, NAD_PL
                if ( subsegs[2] == "MR" ) then
                    // Mottaker
                    NAD_MR = subsegs[3]; 
                elseif ( subsegs[2] == "PL" ) then
                    // Betaler
                    NAD_PL = subsegs[5];
                end if
                
                
            elseif (subSegStart == "INP") then
                // Instruction to parties (M 1)
                // INP+BF+2:SI
                subsub = explode ( ":", subsegs[3]);
                INP = subsub[2]; 
            
            elseif (subSegStart == "FTX") then
                // Free text (C 1)
                // FTX+REG++14+Import of Toyota-cars
                // FTX+AAG+++Tandberg Data
                FriTekst = subsegs[5]; 
                
            elseif (subSegStart == "PRC") then
                // PRC Process identification
                // Not in NETS documentation
                
            elseif (subSegStart == "DOC") then
                // Document/message details 
                // Not in NETS documentation Den Danske Bank har ref på dette. her ligger KID
                // DOC+999+0000773500050154208143212
                if ( subsegs[2] == "999") then
                    KID = subsegs[3]; 
                end if
                
                
            elseif (subSegStart == "GIS") then
                // General indicator (M 1)
                // GIS+10 -  (Declaration is nessecary)
            elseif (subSegStart == "UNT") then
                // Message trailer (M 1)
                // UNT+25+1
                if ( int ( subsegs[2] ) != segNo - currentMessageStart + 1 ) then
                    throw "Number of lines in Message does not comform with the message trailer: " + subsegs[2] 
                        + "/" + (segNo - currentMessageStart + 1) + " / MessageID:" + UniqueMessageNumber; 
                end if
                
                // TODO: Complete the update of the payments table and related tables. 
                dataTR[] = createTransaction ( UniqueMessageNumber, LevB_LIN, sequence, ReceivedDate, 
                        refArchive, refOrderNumber, 
                        transactionAmount, payersAccountNumber,  PayersName, FriTekst, INP, 0, "", 
                        0.0, 0, NAD_PL, KID); 
                        
                //
                transactionAmount = 0.0; 
                refArchive = "";        
                refOrderNumber = ""; 
                payersAccountNumber = ""; 
                PayersName = ""; 
                NAD_PL = ""; 
                FriTekst = ""; 
                KID = ""; 
                INP = ""; 
                
                 
                // Verify the sum of the amounths and the total. 
                
                currentMessageStart = 0; 
                MessageIdentifier = ""; 
            else
                throw "Unidentified code in CREMUL file: " + subSegStart; 
            end if
        end if
    end for
    
    if ( MessageIdentifier != "") then
        throw "CREMUL File is not complete. Last segment not terminated with Message Trailer (UNT)"; 
    end if
    
/*

If we arrive here, the parsing is successfull, but we haven't done anything to the database yet. Lets update the database. 

Use the data arrays for header, sequences and transactions to insert into tables. 
The data is allready in SQL VALUES format in the three arrays dataMH, dataSQ, and dataTR. 

Use the "implode" function to merge the arrays into comma-separated lists and insert all records for that table using one INSERT statement. 
    INSERT INTO TABLE (field list) VALUES ( record 1 data ),(record 2 data),(record 3 data),(record 4 data)...

*/
    
    // Header
    string res = ExecuteSQL ( "INSERT INTO PaymentMessage (MessageRef, MessageDate, FileName,FilePath, ImportedTimeStamp, ImportedBy) VALUES " + 
    implode ( ",", dataMH)); 
    
    // sequences
    string res2 = ExecuteSQL ( "INSERT INTO PaymentSequences (MessageRef, LIN, NETSProcessingDate, BusCategory,
        SequenceAmounth, SequenceBankreference, ValDate, ReceiversAccountNumber) VALUES " + 
    implode ( ",", dataSQ)); 
    
    // Transactions
    string SQL3 =  "INSERT INTO PaymentTransaction (MessageRef, LIN_ID, SequneceNo, ReceivedDate, ArkivRef, 
        OppdragRef, TransfAmounth, PayerAccountNumber, PayerName, Freetext, INP, Proc, DocRef, ProcAmounth, GeneralIndicator, KID) VALUES " + 
    implode ( ",", dataTR); 
    string res3 = ExecuteSQL (SQL3); 
    
    
    if ( res == "" && res2 == "" && res3 == "") then
        return "OK"; 
    end if
    
    
    return format ( "ERROR SQL Insert res (MH): %s, (SQ): %s, (TR):%s\n", res, res2, res3); 
end

function import_cremul_select_file ()
    string FileName = select_file ("Select a CREMUL FILE?", desktop_directory()); 
    if ( FileName != "") then
        return import_cremul ( FileName );
    end if
    return "OK"; // user cancelled the File Selcet Dialogue. 
end

After we run this function on our test Database with the above example CREMUL file. We got this data

References:

Cremul Doc at NETS (Norway)


Back to top

Concatenating Files for a Book

During my documentation project, I encountered the need to concatenate multiple Markdown documents into a single comprehensive document. I initially used a FileMaker Script to achieve this and subsequently ran the Markdown conversion to generate a complete HTML file with all the pages combined.

However, I faced some formatting issues during this process. Notably, numerous extra blank lines were being inserted into the text. Upon investigation, I realized that this was due to formatting applied by FileMaker to files with alternate line breaks. FileMaker uses CR (Carriage Return), whereas most pure text editors on Mac use LF (Line Feed). The issue likely arose because LF was converted to CRLF, and when written back, the Markdown converter interpreted the extra CR as blank lines.

To address this, I decided to create an ACF function to handle the concatenation process. This approach provided me with full control over the text within the documents. Additionally, I started by creating a Table of Contents (TOC) section with all the document titles, making it easier to navigate through this lengthy document.

The implementation of this approach was an immediate success. You can view the resulting concatenated document for this manual by clicking the following link: ACF_book.html

Here's a snippet of the ACF function responsible for this operation:

function create_ebook_html ( string book, string html_path, string bookname )

    if ( bookname == "") then
        throw "Missing bookname"; 
    end if
    string s, d; 
    string sql = format ( "SELECT cat.Sorting, doc.Sorting, doc.DocumentRelPath, doc.Title, cat.Category, 
    doc.cCategoryText,doc.PrimaryKey FROM ACF_Documents as doc
LEFT OUTER JOIN ACF_Categories as cat ON cat.PrimaryKey = doc.Category_UUID
LEFT OUTER JOIN Books as bk ON bk.PrimaryKey = doc.BookUUID
WHERE bk.Title='%s'
ORDER BY cat.Sorting, doc.Sorting, doc.Title", book);

    string sf = ACF_Documents::DocumentRelPath; // Any document in the book. We only want the directory path. 
    string sourcePath = regex_replace("^(.+)/(.+\..+)$", sf, "\1")+"/";
    // print sourcePath; 
    // return "Debug";
    int rs = SQL_Query (sql);
    int rc = SQL_getRowCount ( rs ); 
    int md = open ( sourcePath+bookname+".md", "w"); 
    
    write (md, "# "+Books::HeadingTitle+"\n\n"); 
    
    int i,x; 
    array string row; 
    string curcat = "---", cont, anchor;

    // TOC ( Table of Content )
    write ( md, "## TABLE OF CONTENT\n\n");
    for ( i=1, rc )
        row = SQL_GetRow ( rs, i-1); 
        if ( row[6] != curcat ) then // Category headings in the toc. 
            write ( md, "\n###"+row[6]+"\n\n");
            curcat = row[6];
        end if
        write ( md, "- [" +row[4]+"](#"+row[7]+ ")\n");
    end for
    write ( md, "---\n");
    
    // The Main content - from all the MD files.
    curcat = "---";
    for ( i=1, rc )
        row = SQL_GetRow ( rs, i-1); 
        if ( row[6] != curcat ) then
            write ( md, "# "+ row[6]+"\n\n");
            curcat = row[6];
        end if
        x = open (row[3], "r" ); 
        cont = read ( x ); 
        anchor = '<a id="'+row[7]+'"></a>';
        write ( md, anchor+"\n"+cont+"\n\n");
        close ( x );  
        
        write ( md, "<br><hr>[Back to top](#toc_0)\n\n");
        
    end for
    close ( md ) ; 
    sql_close ( rs );
    
    res = EasyConvertMarkdown (sourcePath+bookname+".md", html_path+"/"+bookname+".html", html_path, 
        ACF_Documents::Style,ACF_Documents::Codestyle,  Preferences::ImageTagRemoveText);

        // Copy the neccessary files to a new folder. 
        // Copy file also copies directories. 
    destfolder = html_path + "/" + bookname + "/";
    if ( directory_exists (html_path + "/" + bookname)) then
        res = delete_directory ( html_path + "/" + bookname );
    end if
    res = create_directory (html_path + "/" + bookname ) ; 
    
    res = copy_file ( html_path+"/"+bookname+".html", destfolder+"index.html"); 
    res = copy_file ( html_path+"/images", destfolder+"images"); 
    res = copy_file ( html_path+"/css", destfolder+"css"); 
    res = copy_file ( html_path+"/include", destfolder+"include"); 
    res = create_directory ( html_path+ "/" +bookname + "/themes/markdown" ); 
    
    res = copy_file ( html_path+"/themes/markdown/" +  ACF_Documents::Style, destfolder+"themes/markdown/"+  ACF_Documents::Style); 
    s= html_path+"/themes/code/style/" +  ACF_Documents::Codestyle; 
    d= destfolder+"themes/code/style/"+  ACF_Documents::Codestyle;
    res = create_directory ( destfolder+"themes/code/style"); 
    res = copy_file ( s, d); 
    
    s= html_path+"/themes/code/style/" +  ACF_Documents::Style; 
    d= destfolder+"themes/code/style/"+  ACF_Documents::Style;
    res = copy_file ( s, d); 
    
    $$BookHTML = html_path+"/"+bookname+"/"+"index.html"; 


end

FileMaker Script

References:


Back to top

Example: Merging WORD Template Document with Tags

A common requirement in many systems is the ability to create documents from a template and merge them with content from a database. While several plugins are available for this purpose, this example demonstrates how to achieve it using the ACF plugin.

Microsoft Word supports various document formats, including DOCX, DOC, RTF, and more. The DOCX format is essentially a ZIP archive, while the DOC and RTF formats represent single-file documents.

The easiest way to perform merge operations would, of course, involve plain text documents, as they do not contain any formatting information. However, this approach would not provide the flexibility to format the document as desired. You could consider using the MarkDown format and then converting it to an HTML file, but this method does not support document styles such as headers, footers, and other requirements for paper documents or PDFs. The DOCX format can be challenging to process, as it involves a zipped document structure. The DOC format is a binary format and not easily handled for merging purposes. On the other hand, the RTF format is a text-based format but contains numerous formatting directives. This is the format we have chosen for our approach to merging documents.

In this solution, we use the RTF format for the template and then save the result with a .doc extention. This prompts Word to open it in compatibility mode, which is perfectly acceptable. You can later save it as DOCX if that's the desired format.

Let's take a closer look at the RTF format. If you save a Word document as RTF and open it in a plain text editor, you'll notice numerous directives starting with a backslash. These are directives that define various parameters for the document.

Suppose we opt to use double angle brackets on each side of our merging-tag, like this:

<<Name>>

Our task is to open the RTF template, locate all the tags, and replace them with actual database content. While this may seem straightforward, issues can arise when users apply formatting within the tags. For instance, a tag like <<Address>> may become distorted if a user applies the "Bold" style to only the Address word of the tag, resulting in something like this:

<<}{\rtlch\fcs1 \ab\af31507 \ltrch\fcs0 \b\insrsid5650507\charrsid15009246 Address}{\rtlch\fcs1 \af31507 \ltrch\fcs0 \insrsid5650507 >>

How can we address this challenge?

Answer: We can use regular expressions. Specifically, we'll employ a carefully crafted regular expression with the ACF function regex_extract. This function allows us to extract tags from the document in a way that facilitates safe replacements, even when the tags contain formatting codes.

In regular expressions, we use "groups" (enclosed in parentheses) to extract specific portions of a match. With regex_extract, we obtain an array with one row for each match in the document. Each row contains a delimited list, including the full match and all the groups within it. One group contains the bare tag-name without the << >> markers allowing us to identify the merge-tag itself.

Here is the regular expression used in this solution. If you are not familiar with regex, its OK, I have made and tested it: 

string reg = "<<((?:€€[a-z0-9]*|\\}|\\{|\\s)*)([a-z0-9.\-_æøåÆØÅA-Z]*)((?:€€[a-z0-9]+|\\}|\\{|\\s)*)>>";

This regex captures four groups:

  1. The full match
  2. Formatting before the tag
  3. The tag itself
  4. Formatting codes after the tag

When performing replacements in the document, we replace the full match (1) with (2) followed by the text corresponding to (3) and (4).

As the backslash character (\) has a special meaning in regex and string literals in our code, and the document contains this character for each of the directives it would be hard to distinguish them from each other. To simplify the regex, we temporary use double euro signs (€€) as placeholders for backslashes in the document, making the regex easier to construct. After processing, we replace these placeholders back with backslashes.

In the example below, we've created a function to simplify the retrieval of tag text based on a tag's name, called GetSubstitute. This function accepts a tag as input and generates the corresponding text for that tag. While you can customize this function to suit your specific requirements, we've reserved the tag date to automatically return today's date in text format. Otherwise, we assume that the tag refers to a local variable in the script, which is set using the Set Variable script step. In this case, the tag name should be prefixed with a dollar sign ($). This allows the calling script to prepare all the tags referenced in the document before invoking the MergeRTFDocument function to complete the merge.

As mentioned earlier, RTF, being a Microsoft format, uses ISO-8859-1 encoding instead of UTF-8. To ensure that text from FileMaker, which is in UTF-8 encoding, displays correctly in the document, we need to perform a conversion. We accomplish this by using the ACF function from_utf, which takes the text as the first parameter and the encoding name as the second.

The ACF function eval is employed to retrieve the text from the locally prepared script variable in the calling script. If the variable happens to be nonexistent, FileMaker will return an empty string, causing the corresponding tag to be omitted from the document.

Here's the complete example:

Package DocuLib "Library for documentation project"

function DocuLib_Version ()
    return 10;
end

// Function to get the text to replace in the tag. 

function GetSubstitute(string tag)
    string ret;
    if ( tag == "Date") then
        ret = string(now(), "dd.mm.yyyy"); 
    else
    // Ref a FileMaker variable used from the calling script. 
    // As the text should be in ISO-8859-1 format, we do a conversion as well. 
        ret = from_utf( eval ( '$'+tag), "ISO-8859-1"); 
    end if
    // handle new-line characters in the replacement text, put on some RTF newline directives
    ret = substitute ( ret, char(13), "\r€€par ");
    return ret; 
end

// Perform the actual tag replacement.
function MergeRTFDocument ( string template, string outPutDoc )

    string docu; 
    
    // if empty template, then get the user to select it. 
    if ( template == "") then
        template = select_file ("Select a RTF File?");
    end if
    
    // If the user cancels the file selection, return an empty string.
    if ( template == "") then
        return ""; 
    end if
    
    // Get the content of the selected document.
    int x = open ( template, "r"); 
    docu = read ( x ) ; 
    close (x);
    
    // Substitute double euro signs to simplify the regex.
    docu = substitute ( docu, "\\", "€€"); 
    
    // Define the regex pattern.
    string reg = "<<((?:€€[a-z0-9]*|\}|\{|\s)*)([a-z0-9.\-_æøåÆØÅA-Z]*)((?:€€[a-z0-9]+|\}|\{|\s)*)>>"; 
    
    // Extract the tags from the document.
    array string results = regex_extract ( reg, docu,"|*|"); 
    
    array string match; 
    int i, z = sizeof ( results); 
    string s1, s2, s3; 
    
    // Loop through all the matches. 
    for ( i=1, z)
        match = explode ( "|*|", results[i]);
        s1 = match[2];  // Formatting before
        s2 = match[4];  // Formatting after
        // Replace the full match, call our "GetSubstitute" function to obtain the text for the tag.
        docu = substitute ( docu, match[1], s1+GetSubstitute (match[3])+s2 ); 
    end for
    
     // Replace the euro signs back with backslashes.
    docu = substitute ( docu , "€€", "\\");
    
     // Create a new document with the .doc extension.
    if ( outPutDoc == "") then
        outPutDoc = template + ".doc";
    end if
    
     // Write the modified content to the new document.
    x = open ( outPutDoc, "w"); 
    write ( x, docu); 
    close ( x );
    
    return "OK"; 

end

// From the script applied:
// ACF_run( "MergeRTFDocument"; ""; "")

From FileMaker, We use a script like this: 

# Being in the document layout
Set Variable [ $Name; Value:Contacts::First Name & " " & Contacts::Last Name ]
Set Variable [ $Company; Value:Contacts::Company ]
Set Variable [ $Address; Value:Addresses::Address Line 1 & "¶" & Addresses::Address Line 2 & "¶" & Addresses::Postal Code & " " & Addresses::City ]
Set Variable [ $res; Value:ACF_run( "MergeRTFDocument"; "";"") ]

After a testrun, we have this result:

Merged

Summary: This is a working example demonstrating how to perform merging in RTF documents for generating letters, contracts, and other documents from template files. In a fully functional solution, you would require a value list to select the template to use and some configuration settings to specify the location of the templates. Additionally, for archiving the resulting documents, further configuration settings would be necessary. To enhance user experience, you can utilize the openURL script step to automatically open the processed document in Microsoft Word, allowing users to add any additional details not covered by the merge operation. This comprehensive approach streamlines the document generation process.

The complete solution could also have some selection of canned text snippets to include in the document, making the document production faster and implying less afterwork with the merged document. A text field in the document table, could hold the selected canned paragraphs, and merged with a single merge tag.

We could also use the DocumentService functions in this plugin, to facilitate storage and- or encryption of the stored documents where more users can access the documents in a secure way.

References:


Back to top

Demo

Function: Instruction Videoes

I have made some instruction Videos as learning materials about the ACF plugin. It is best shown on a computer in full screen, or on a iPAD, to get the resolution needed for the videoes.

Instruction videos:

Video two: Formatting of text strings and date formatting:

In this video, we introduce a playground for testing functions in the ACF language. We look at text operations and formatting, date formatting and parsing.

Video three: SQL function and parsing the result using array functions:

In this video we focus on SQL functions. How we simplify handling results from multi-column SQL queries, and also introduces MySQL operations that can be used to control web-pages and such directly from FileMaker working on the WEB-page database directly.

We will add more videos here as they are being created.

Happy watching.


Back to top

Demo and Download ACF Plugin

For a demonstration of the ACF plugin, you'll find a demo license, plugin downloads, and instructions for the accompanying demo apps.

The Demo Apps

There are three demo applications available:

When it comes to editing ACF source code, we recommend using the .acf file extension. These files are bundled with syntax coloring for TextMate on Mac and NotePad++ on Windows. Both of these applications are free and provide an excellent environment for editing your source code.

Instruction videos:

For the instruction Videoes - The have been moved to the instruction videoes page. In the download below, there is a folder where the demo materials can be downloaded.



On Mac: To ensure smooth integration, associate your chosen editor (TextMate is preferred due to its syntax coloring) with the .acf file extension. You can do this by right-clicking on an .acf file, selecting "File Info," and choosing TextMate as the default editor for such files. This step is crucial for ensuring that the OpenURL script step in FileMaker works correctly.

On Windows: Similar configurations may be required on Windows.

The Public Demo License

To activate the demo license, follow these steps in your startup script:

// Expires: 31/10/2023

Set Variable [$Res; ACF_RegisterPlugin("Demo"; "ACF_Plugin DEV3.50 - Demo 1027
========LICENSE BEGIN======
QB25ZD76dmuP/xJsxlfh1F0c+xMdv9iT8yEJ0TG7+KHf94RlS94fLi1lu5Fq1a+RtsMQmqoZ
fonVjA8o6shL879N34ckhUyooviXT81vlvNpuEG2FmxD6ehmegzBawur21Q47wyZQzN1Xh3Y
Di+6jsrSR6eggdkSrZW5BZQOczw===
======= LICENSE END ======="
)]

When you order a license, you will receive a code block like this to be updated in your application (that never expires).

Please also review the startup script in the demo applications for additional details.

Download Folder

You can access the download folder containing the demo applications and plugin files by clicking the link below:

Go to the Downloads Folder

The 'ACF.tmbundle.zip' is the syntax color defs for TextMate. The 'Notepad...' file is the syntax coloring defs for NotePad++. The Plugins folder contains the plugin itself.

Download Directory Demo Apps

In the download folder, you will find two plugins: one is version 1.6.2, which includes both Windows and Mac versions of the plugin, and the other is version 1.6.3 for Mac, featuring built-in MySQL connectivity.

Plugin Downloads Folder

Good luck, and enjoy exploring the capabilities of ACF!


Back to top