Welcome to the latest release of the ACF Plugin, version 1.7.4.3. For the latest improvements, see the version history.
Creating Excel Spreadsheets in native XLSX format with the new Excel functions makes this task easier.
Container variable type: compatible with FileMaker's Container field type.
Intel Mac: From MacOS 10.13 High Sierra
Arm (Silicon) Mac (M1, M2...) : From MacOS 12.3 Monterey
All architectures can run up to the current MacOS 14.2 Somono
64-bit Intel PC's with Windows 10 and Windows 11
All 64-bit FileMaker versions.
The Excel style option: "Alignment", and the value: "Middle" did not work. The XML output to the workbook stylesheet used "middle" as vertical alignment but should use "centred". Fixed.
The command Excel_SetRowHeights has got a new variant:
Excel_SetRowHeights ( SheetID, FromRow, ToRow, Height);
Fixed bug in Excel_SetRowHeights variant with array. The command failed on start-row different from 1.
Better error handling on conversion from date to string. The function does not throw an exception on invalid dates but returns 1.1.1970 as the string. This is to avoid erroneous data causing reports to be non-working.
Bugfix: the function ABS rounded floating point numbers to whole numbers. The function used the C++ function "abs" which is not defined for floating numbers. Instead, when floating numbers it now uses "fabs". Fixed.
Bugfix: Accessing JSON boolean values did not return any value. Like {"success": true}
It now returns the string type "true" or "false." The bug made it difficult to evaluate the result of an operation that produces a JSON object containing such elements.
New command: Excel_SheetProtection ( sheetID, new_password {, old_password {, spin_count}})
for unprotected sheets, no need to supply old_password (optional blank)
new password set to blank clears the sheet protection
The spin count is default 100 000;
Compatible with Excel hashing algorithm. Applying sheet protection can be unprotected in Excel specifying passwords.
New Command Excel_DefineTable - See example: (This will be elaborated on in the documentation soon.) Ref and autofilterref
// Define a table for the data... Excel_DefineTable (s, JSON( "name", "CustomTable", "ref", Excel_Sheetaddress (3, 1, 20, 6), "autoFilterRef", Excel_Sheetaddress (3, 1, 19, 6), "filterButtons", JSONarray(1,1,1,0,0,0), // if not array, but single 1 or 0, means all on or all off. "style", "TableStyleMedium2", "options", JSONarray( "totalsRowShown","showFirstColumn", "showRowStripes" ), "columnNames", JSONarray( "Name","Position","Office","Age","Startdate","Salary" ), "totalsRowFunctions", JSONarray( "count","", "", "average", "", "sum" ), "totalsRowStyle", "Num" // pick the number format from this regular style )); Some minor debugging.
New datatype: Container - Compatible with FileMaker's Container field type. You can declare variables or parameters as type Container- Functions can also return this type, or use it as a parameter in certain functions.
New commands / Functions:
int ImgID = Excel_AddImage (string path | Container );
Excel_PlaceImage ( int SheetID, int ImgID, row, column, int/float width, int/float height, int/float offsetX, int float offsetY);
string content = Excel_ContainerGetData ( Container);
string FileName = Excel_ContainerGetFileName ( Container );
Container_LoadFile ( Container, string Path);
string Size = get_imagesize(String path | Container );
Excel_SetGridLines ( int sheetID, bool on ) // Fslse is off
Excel_SetRowHeights ( int sheetID, int fromRow, array float heights);
Excel_SetRowHeights ( int sheetID, int Row, float height);
Added Date, Time and Timestamp literals to the ACF language for hard-coded values. The literals have these formats:
!2025-03-12! // Date literal 12 march 2025!12:34:29! // Time literal!2025-03-12T14:30:00! // Timestamp literal.
Added Date, Time and Timestamp parameterized converters in addition to the string converters we had before.
Prototypes:
date ( int mnd, int day, int year ) => Datetime ( int hour, int minutes{, int seconds}) => Timetimestamp (int mnd, int day, int year, int hour, int minutes{, int seconds}) => Timestamp
See example:
date dx = date(3, 25, 2025);time tx = time(22, 45,0);time t1x = time(now());timestamp tsx = timestamp(3,25,2025,10,30,0);print format ("\nDate: %s, time: %s, now: %s, TimeStamp:%s\n", string(dx), string(tx), string(t1x), string(tsx));Output:Date: 25/03/2025, time: 22:45:00, now: 13:30:06, TimeStamp:25/03/2025 10:30:00
Corrected bug: When opening a workbook with formulas referencing other sheets, inserting or removing rows/columns did not correctly update the references from other sheets in the workbook.
Corrected bug: When inserting or deleting rows/columns in workbooks edited by Excel, Excel grouped formulas into shared formulas. The references section was not updated correctly for those.
Corrected bug in Excel_Open: When opening workbooks containing more than nine sheets, an issue with sheetID sorting made the sheets be parsed in the incorrect order. The names of the tabs and the sheet contents did not correspond.
Corrected bug in Excel_SetColumnWidth when called for a single column. Returned an exception about invalid sheetID.
Improved the classification of styles concerning the NumFormaCodes to classify as DATE, TIME, and TIMESTAMP formatting. The complete list of all internal format codes used by Excel is used for classification. Make putting a date value into a cell with a style already configured to format dates, not creating a duplicate style.
Same functionality as in the Mac version. This is a comprehensive update to the Windows version.
I solved some Windows-only issues. Amongst date and time handling, the long datatype on Windows was 32bit, but it is now changed to 64bit to confirm with the Mac version.
Solved a bug in date handling before 1960 that made the dates one-off when parsing dates, due to an issue with leap-year calculations with negative Unix timestamps. (Unix timestamp is used for the internal date format, and starts 1.1.1970, calculated in seconds). This issue is still present on the Mac version, the fix will be included in the next Mac release.
Added new functions to do column insertion and deletion for a range of rows, as opposed to the Excel_InsertColumns and Excel_DeleteColumns that works for the whole spreadsheet.
// Excel_ShiftRight ( sheetID, column, numberOfCols, fromRow, toRow);// Excel_ShiftLeft ( sheetID, column, numberOfCols, fromRow, toRow);
Changed logic for updating formulas to care for interlinking when moving cells around.
Revised handling of date, time and timestamp values in Excel_setCell and Excel_GetCell
Automatic creation of styles when setting a date, time or timestamp value based on the style given in the Excel_setCell call, if the style does not have NumFormat to display date, time or timestamp. For example, If the style given is "myNum", having numformat "### ### ###.00", font 13pt Arial, fill color yellow. This style does not show date values well, so the style is duplicated, given name "myNum_Date", numformat is set to empty string, and numFormatCode is set to 14, which is the internal code for date values used by Excel. In this way, the date will be shown correctly, on a yellow background using 13pt Arial font. All this happens when using "Excel_SetCell", and using a date variable for its value.
Corrected a crashing bug in the plugin. Enforced additional checks for array bounds and improved memory handling.
Added new functions for manipulating Rows and Columns:
// Default in number of row/column=1, insertabove=false, insertleft=false// Excel_InsertRows (int sheetID, int row {, int numberofrows {, bool insertabove}});// Excel_InsertColumns (int sheetID, int column {, int numberofcols {, bool insertleft}});// Excel_DeleteRows (int sheetID, int fromRow, int toRow )// Excel_DeleteColumns (int sheetID, fromCol, toCol);
Revised the stylesheet operations, especially handling the defaults and using the styles reference count. Making the stylesheet cleaner and has improved compatibility with Excel-generated source documents.
Improved compatibility when importing Excel Spreadsheets made in Excel, and saved to a new workbook. Better handling of font, fills, shared formulas, and number formats with internal format numbers defined by Excel.
When Importing from Excel, as the names defined by us is not stored on the Excel file, the format uses style by index instead. All the styles imported get generic names "Style1, Style2, and so on". If you create a new style, it will try to match with one of the generic styles, and if found a match, this style is renamed instead.
If opening and saving Excel files several times, the style table might contain styles not used. To get rid of those, a new command Excel_CleanUnusedStyles, has been developed.
Improved the style handling for Excel_Open and Excel_SaveAsWorkbook, compatibility with Excel's style-attributes has been improved.
New functions:
Excel_GetGetStyleOptions ( wb, styleName) => returning a JSON object describing the style. The object is compatible with Excel_SetStyleOptions.
JSONarray(list of array ellements, any type) => retuning a JSON object containing a JSON array.
Improved JSON function, array objects.
General debugging Excel functions.
Added function WorbokRef = Excel_Open ( path, accessmode )
Accessmode must be "r" in this version. Changes will not be saved.
The function import an existing excel document. The Excel_GetCell, Excel_GetColumns and Excel_GetRows can be used to extract content from the spreadsheet.
An "Alpha-state" function called "Excel_SaveAsWorkbook (WorkbookRef, path )" is implemented mainly for debugg purposes, but this will be extended to full support with styles and everything in future release. The command export imported content to another spreadsheet.
Added functions:
Excel_SetConditionalFormatting (sheetID, JSON config object)
Excel_Liststyles (WorkBook ID) => array string
Excel_mergeCells ( SheetID, row, column, rowSpan, colSpan)
Excel_GetCell (SheetID, row, column) => String
Excel_GetColumns ( Sheet_ID, row, FromCol, ToCol ) => array string
Excel_GetRows ( Sheet_ID, col, FromRow, ToRow ) => array string
excel_sheetaddress (row, column {, toRow, toColumn}) => string (i.e. A1:B6)
Mac Beta of the new XLSX functions for creating Excel spreadsheets. Documentation is under way, follow our web-site for documentation.
Windows release only: Changed principle for SystemCommand to avoid a black window-command-prompt flashing up while executing the command.
Added 3 more access modes for open(<path>, <access mode>)
rb - open binary file for read access. read only.
wb - open binary file for write access. write only
ab - open binary file for append access. write only.
r, w, wa is for text-based access modes, support both read, write, readln, writtln.
fixed bug in HTTP_POST, not handling binary post data well.
New commands HTTP_PUT and HTTP_DELETE, having the same parameters as HTTP_POST, by doing PUT and DELETE requests instead.
Added function SystemCommand to execute OS commands. The function has two parameters: Command, and directory path to cd into before the command is applied.
Added system variables HTTP_STATUS_CODE and HTTP_HEADERS, which can be used when working with APIs, to verify request went OK (200), and for the headers - it is now possible to extract cookies from the headers to be used with authentication protocols when working with web-services.
Corrected issues with global variables that sometimes did not work correctly. Compiler issue.
New function SystemCommand to execute operating system commands from within the ACF function.
Parameters: `SystemCommand ("Command", "/path/to/directory/to/cd/into", environment);
environment is default "/bin/bash" if this parameter is omitted. On Windows: "cmd"
Fixed issue with JSON and XML implementation where there is no root object, and assignment a single tag.
Fixed issues with the handling of SQL errors. Returned only "?" as the function result, and an error on the console. However, the error did not give enough insight to be able to debug the issue, especially when using placeholders one could not see the actual SQL statement that gave the error. This is now fixed with a print on the console showing the SQL statement. The function also returns the error instead of the "?".
Fixed Escaping of SQL literal content. The literals use single apostrophes on each side, and single apostrophes inside the literal was escaped with \' - that was not correct for SQL. Correct escaping is two single apostrophes, like: '' instead. Now fixed. The problem was most experienced when updating or inserting user content in the database.
Fixed a compiler bug in the Power (^) function, which occurred when operands were non-float values. The incorrect return type caused calculation errors, such as 3*5^0 producing an erroneous result, while 3*5.0^0.0 returned the correct result.
Added functions:
Log10 - Calculate Briggs logarithm of the argument
LogLn - Calculate the natural logarithm of the argument.
Fixed bug in date and timestamp formatting functions, where on the Windows platform an invalid format string made Filemaker quit unexpectedly. The issue has never been seen on MacOS. The fix causes now a runtime error with a proper message in the function instead.
Implemented math trigonometric functions asin, acos, and atan .
Implemented check for curl CA-certificate environment variable CURL_CA_BUNDLE. If it exists and points to a certificate file containing the CA certificates, the plugin uses this when executing HTTP_GET, HTTP_POST and SEND_EMAIL functions when using the SSL protocol. This is usually not needed for MacOS, as it has such a file in its default location, but Windows uses a different system for CA certificates that makes downloading and installing such files in the system necessary to speak over the SSL protocol. For some special sites, it might be necessary to use a different CA file, and this is now possible.
Break inside for-loop didn't pop the step variable, so the console received a warning about something left on the stack at return. Fixed.
Fixed a bug in handling SQL placeholders in SQL statements. The ":" prefix of the variable names was also checked inside string literals making it impossible to update or insert literal values containing a colon.
Added function "proper" which makes the title case of a given string as a parameter, (in the family of upper/lower functions). This is also a standard FileMaker function, but now also standard in ACF.
Bugfix: The ACF functions upper and lower to convert a string to uppercase and lowercase did not check if the current locale was initialized. This caused a runtime exception if not initialized by other functions. Fixed in this version. Bug introduced in 1.7.1.3 as an optimization task to reduce the overhead of the startup of the ACF functions.
Optimized code for ACF_run startup, delayed initialization of locale until needed. Removed 1.3mS from startup code, down to 0.1mS
Added native TRIM functions to trim left, right and both sides of a text string. The function trims away white space including CR, LF, TAB, SPACE, and NBSP ( 0xC2 0xA0).
TrimBoth - Both sides
TrimLeft and TrimRight for either side.
The functions are highly effective, trims a string in less than 2uS. (Tested with 200 char string and mix of white space on each side in a loop running 10.000 trim operations).
In version 1.7.1.1, we've fixed a critical bug causing a 1-byte displacement in the binary output when referencing inter-package functions via the USE statement, which previously led to unloadable compiled packages. We've introduced an updated, more reliable method to prevent this issue, rolling out file format version 2 that retains compatibility with the original format. Format version 1 is deprecated and treated as version 0, excluding inter-package link tables. Users are encouraged to update to ensure compatibility and take advantage of these corrections. All packages compiled with 1.7.1.0 must be recompiled with this plugin version.
Implemented "USE" statement to allow inter-package function calls. With the "use" statement, you tell the package that it can call functions in the other namespace (another package name). See an example in the article addressing this feature.
Corrected some minor bugs: The "repeat-until" falsely reported a "left content on stack" warning.
Discovered bug in date, time, and timestamp compare construct. Fixed.
New functions: open_zip and close_zipfunctions and included all filesystem operations to work on paths inside ZIP archives, see article about ZIP file support here
The functions left, right, substr, and mid handle UTF8 strings based on characters instead of bytes. for example left("bæææ", 3) returns bææ that represents 5 bytes. (each "æ" is two bytes: C3 A6). This change will ensure that multi-byte characters don't got cut.
The functions char and ascii now use Unicode representation instead of pure ASCII values. For ascii, it means that if the first character in a string is a multibyte UTF8 character, it will return the unicode for that character instead of only the first byte. Also, char` - will produce a string with the UTF8 character based on the Unicode taken as a parameter.
The functions upper and lower now use the currently active locale set by set_locale to handle locale-specific conversion to upper and lowercase strings.
The function length now counts characters in a string, instead of bytes. This means that length("bææ")returns 3, instead of 5 bytes. This works well in most cases.
The new function bytes return the actual number of bytes in the string.
Some adjustments to the format function and UTF8 strings. s-format with length now fits with multi-byte UTF8 strings, so that the length is expanded with the extra number of bytes required to fit the string. For example, the format %-10s (left justified string, 10 char), and the string "Bodø" that is 5 bytes, will expand the format to %-11s, so that the 6-space padding makes the result 10 characters.
Included boost_locale and the ICU libraries to improve the following functions about UTF-8 handling:
ICU stands for "International Components for Unicode." It is a mature, widely used set of C/C++ and Java libraries that provide robust and full-featured Unicode and locale support. This includes functions for string comparison, normalization, conversion between different character encodings, date/time formatting, and language-sensitive text collation. ICU is extensively used in various applications and platforms for internationalization (i18n) to ensure that software can be used in multiple languages and cultural contexts.
New command: set_locale. See the reference on page: new locale command for the syntax and a list of often-used locale codes.
The sort_array command now uses the locale for the correct sorting of national characters.
The compare function can now use the correct locale when comparing strings with if (string1<string2) then statement.
The regex functions are improved and now use ICU for special UTF8 characters.
new functions: encrypt and decrypt. See manual page: encryption/decrypt functions
new function: sort_array - multi-array multi-level sorting function for sorting a single array or a group of arrays. See manual page sort_array function
Two new ACF functions, create_hash and create_hmac for generating message digests for all the methods: MD5, SHA1, SHA256, SHA384, and SHA512. Documentation can be found on the following WEB-page: horneks.no/manuals/ACF/createhashfunction.html
Implemented new language grammar features:
Conditional expressions (see Syntax differences, conditional expressions )
Case ... end case structures. (see Case structures)
break statement to exit loops and case constructs. A break that is placed inside an if-construct will break out of the loop surrounding it. Breaks inside case constructs break out of the case and continue execution after end case. The break statement exits any type of loop construct being for, while, or repeat until.
The send_email function has been enhanced to encode display names in email addresses and the subject line, ensuring compliance with email standards. Additionally, we've refined the processing of lengthy subject lines by appropriately splitting them across multiple lines in the mail headers. For emails with numerous recipients in the "To" or "Cc" fields, our improved implementation now effectively splits these lines following RFC standards. The encoding utilized is UTF-8 with Quoted Printable. It's worth noting that the encoding of different body parts was already accurately implemented starting from version 1.7.0.9.
Fixed bug in send_email where mail contained several inline images. The styling of the HTML mail was incorrect.
Fixed a little bug in the send_email implementation. It did not recognize correctly the email addresses on the form name <myemail@example.com> , only the naked emails without the name in front.
Updated the TextMate editor bundle to correctly handle indentation and added some new language functions. It is in the download DMG.
Implemented send_email function, according to the web page at: https://horneks.no/manuals/ACF/sendane-mailfromtheacfplugin.html
Resolved SSL issues on macOS Sonoma by updating the cURL and OpenSSL libraries. Previously, the plugin used cURL with LibreSSL, which was compatible with earlier macOS versions but not with Sonoma. The plugin now utilizes cURL with OpenSSL 3.0.9. This update is essential for the proper functioning of HTTP_GET, HTTP_POST, and document service functions.
Plugin and installer dmg is now Notarized @ apple, so there should be no issues installing the plugin on newer MacOS releases. Tested with MacOS Sonomoa 14.2
Minor adjustments to XML implementation for improved handling of intermediate XML documents. When attaching intermediate XMLs to a master document, if the intermediate document's root element is "intermediateRoot", only its child elements are attached. The function for adding key/value pair lists now encapsulates the list within an "intermediateRoot" element. Conversely, other ACF functions return XML nodes without this wrapper. This approach greatly facilitates the construction of complex XML structures, allowing for seamless assembly of disparate parts into a unified master document. This implementation strategy is essential due to XML's limitation of only allowing a single root element per document. Further details will be provided in the documentation.
Our team has made substantial efforts in the quality assurance department to ensure the highest standards. The ACF compiler, integral to the plugin, now more accurately predicts stack usage for the runtime, focusing on data types involved in various operations.
We've implemented automated testing and detailed logging, leading to the identification and resolution of several critical issues. These efforts have significantly enhanced the overall stability and quality of the plugin.
New functions - Check the revision history below for details.
Minor adjustments to JSON functionality for alignment with XML implementation.
Enhanced debugging of XML functions.
Introduction of XML support functions and data type.
Seamless XML to JSON conversion capabilities.
Incorporation of JSON support as detailed at ACF JSON Data Type Documentation.
Enhanced document service with one-time password security.
Introduction of Confirm, Alert, and Request functions for exception handling.
Robust TRY/CATCH/TRY AGAIN logic for exceptions.
New HTTP_Get and HTTP_Post functions.
Enhanced OTP functions for Two-factor authentication.
Advanced SQL functionality with placeholders.
Updated MySQL compatibility.
Refined Document Service functions.
New BASE64_ENCODE, BASE32_ENCODE, BASE64_DECODE, BASE32_DECODE functions.
Addressed issues with compiling nameless packages. Nameless packages are now labeled "STD".
Overhauled document service for improved stability, particularly in handling unusual parameters.
Implemented 'List_files', absent in version 1.6.2.
Introduced 'Delete_directory', though usage advisories apply due to potential risks with incorrect path specifications.
For detailed information and guidance, visit our online documentation.
Additionally, an offline version of the documentation, as of 09.12.2023, is included in this download. Start with the index.html file at the top of the archive for offline reference.
Your feedback and suggestions are invaluable to us. If you have any thoughts or concerns, especially regarding features like 'Delete_directory', we welcome your input. Thank you for choosing the ACF Plugin!