ACF Plugin is coming to 4D
We have ported ACF Plugin from FileMaker to 4D.
Not because 4D needs another programming language. It already has a strong one. The point is that ACF has grown into a practical toolbox for work that often lives around database systems: Excel generation, ad-hoc dialogs, FTP/SFTP, cryptography, image generation, SQL helpers, PDF output, and source files that live outside the binary .4DBase database.
That last part matters. ACF packages are plain source files, so tools like Codex can read, edit, and extend them directly. In practice this makes it possible to build reporting, export, document, and integration features very quickly.
The plugin is available in our download area as an early alpha release.
ACF also reduces deployment pressure. ACF functions can be loaded and compiled in a compiled 4DC database, or you can compile in-house and distribute a binary package ready to be loaded into the solution.
We use the same ACFcompiler as FileMaker. The binary package format is the same for both platforms, while each platform implements runtime functionality adapted to its own host environment. This means one compiler, one package format, and platform-specific runtimes.
For 4D programmers, ACF becomes an additional layer for reusable automation and reporting logic. 4D methods and ACF functions can work together. ACF functions are organized in source files, where each file becomes a package with a unique name from the package header on the first line. A package can contain any number of functions.
Once a package is compiled and loaded into the plugin, its functions can be called from 4D through typed runners matching the return type, such as ACF_RunText, ACF_RunObject, ACF_RunBlob, ACF_RunBoolean, ACF_RunLong, or ACF_RunReal.
The plugin can also generate 4D prototypes for all loaded functions. These can be pasted into a scratch method for reference, giving you ready-to-edit function calls with named parameters.
Here is a real-world example of an ACF function. I asked Codex to make this from a list of fields in the sample database. In less than a minute, the function was working and produced a styled Excel spreadsheet in .xlsx format.
// Made by Codex ----- //
function SQL4D_AliasIntoExcel(string path)
array int aboID, aboNr, antall, betKundeID, firmaID;
array bool aktiv, avtaleGiro, bestiltUtlop, gaveabonnement, konsernkunde, kvikkas;
array float belopAtTrekke, bestilltBelop, fakturabelop;
array date bestillingsdato, betaltdato, efakturaBestillt, fakturadato, sistEndretDato;
array string avisID, brevtype, budID, firmaNavn, jttaReferanse, kampanjekode, navn;
string sql = 'SELECT abo.Abo_Nr AS AboNR, abo.ID AS AboID, abo.Aktiv, abo.Antall,
abo.Avis_ID, abo.AvtaleGiro, abo.BelopAtTrekke,
abo.Bestillingsdato, abo.BestilltBelop, abo.BestiltUtlop,
abo.Bet_Kunde_ID, abo.Betaltdato, abo.Bud_ID, abo.Brevtype,
abo.Efaktura_Bestillt, abo.Fakturabelop, abo.Fakturadato,
abo.Gaveabonnement, k.Firma_ID, k.Firmanavn,
k.JTTA_referanse, k.Kampanjekode, k.Konsernkunde,
k.Kvikkas, k.Navn, k.SistEndretDato
FROM Abonnement AS abo
LEFT JOIN Kunder AS k ON k.ID = abo.Kunde_ID
WHERE abo.Abo_Nr IS NOT NULL
INTO :aboNr, :aboID, :aktiv, :antall, :avisID, :avtaleGiro, :belopAtTrekke,
:bestillingsdato, :bestilltBelop, :bestiltUtlop, :betKundeID, :betaltdato,
:budID, :brevtype, :efakturaBestillt, :fakturabelop, :fakturadato,
:gaveabonnement, :firmaID, :firmaNavn, :jttaReferanse, :kampanjekode,
:konsernkunde, :kvikkas, :navn, :sistEndretDato';
string res = ExecuteSQL(sql);
print res + "\n";
if (path == "") then
string dir = Documents_directory() + "ExcelSQL4D/";
res = create_directory(dir);
path = dir + format("SQL4D_AliasInto_%s.xlsx", string(usec()));
end if
array string headers = {
"AboNR", "AboID", "Aktiv", "Antall", "Avis_ID", "AvtaleGiro", "BelopAtTrekke",
"Bestillingsdato", "BestilltBelop", "BestiltUtlop", "Bet_Kunde_ID", "Betaltdato",
"Bud_ID", "Brevtype", "Efaktura_Bestillt", "Fakturabelop", "Fakturadato",
"Gaveabonnement", "Firma_ID", "Firmanavn", "JTTA_referanse", "Kampanjekode",
"Konsernkunde", "Kvikkas", "Navn", "SistEndretDato"
};
int wb = Excel_Create(path, "Alias INTO arrays");
int s = Excel_GetSheetID(wb, 1);
Excel_AddStyle(wb, "H1", JSON("Font", "Arial", "FontSize", 16, "Decoration", "Bold"));
Excel_AddStyle(wb, "H2", JSON("Font", "Arial", "FontSize", 12, "Decoration", "Bold"));
Excel_AddStyle(wb, "CH", JSON("Font", "Arial", "FontSize", 11, "Decoration", "Bold", "FillColor", "DDDDDD"));
Excel_AddStyle(wb, "dataTx", JSON("Font", "Arial", "FontSize", 10));
Excel_AddStyle(wb, "datoTx", "dataTx");
Excel_AddStyle(wb, "dataNum0", JSON("Font", "Arial", "FontSize", 10, "NumFormat", "### ### ### ##0"));
Excel_AddStyle(wb, "dataNum2", JSON("Font", "Arial", "FontSize", 10, "NumFormat", "### ### ### ##0.00"));
Excel_SetCell(s, 1, 1, "4D SQL alias + INTO array test", "H1");
Excel_SetCell(s, 2, 1, format("%d rows from ExecuteSQL", sizeof(aboID)), "H2");
Excel_SetColumns(s, 4, 1, headers, "CH");
int i, row;
for (i = 1, sizeof(aboID))
row = i + 4;
Excel_SetCell(s, row, 1, aboNr[i], "dataNum0");
Excel_SetCell(s, row, 2, aboID[i], "dataNum0");
Excel_SetCell(s, row, 3, aktiv[i], "dataTx");
Excel_SetCell(s, row, 4, antall[i], "dataNum0");
Excel_SetCell(s, row, 5, avisID[i], "dataTx");
Excel_SetCell(s, row, 6, avtaleGiro[i], "dataTx");
Excel_SetCell(s, row, 7, belopAtTrekke[i], "dataNum2");
Excel_SetCell(s, row, 8, bestillingsdato[i], "datoTx");
Excel_SetCell(s, row, 9, bestilltBelop[i], "dataNum2");
Excel_SetCell(s, row, 10, bestiltUtlop[i], "dataTx");
Excel_SetCell(s, row, 11, betKundeID[i], "dataNum0");
Excel_SetCell(s, row, 12, betaltdato[i], "datoTx");
Excel_SetCell(s, row, 13, budID[i], "dataTx");
Excel_SetCell(s, row, 14, brevtype[i], "dataTx");
Excel_SetCell(s, row, 15, efakturaBestillt[i], "datoTx");
Excel_SetCell(s, row, 16, fakturabelop[i], "dataNum2");
Excel_SetCell(s, row, 17, fakturadato[i], "datoTx");
Excel_SetCell(s, row, 18, gaveabonnement[i], "dataTx");
Excel_SetCell(s, row, 19, firmaID[i], "dataNum0");
Excel_SetCell(s, row, 20, firmaNavn[i], "dataTx");
Excel_SetCell(s, row, 21, jttaReferanse[i], "dataTx");
Excel_SetCell(s, row, 22, kampanjekode[i], "dataTx");
Excel_SetCell(s, row, 23, konsernkunde[i], "dataTx");
Excel_SetCell(s, row, 24, kvikkas[i], "dataTx");
Excel_SetCell(s, row, 25, navn[i], "dataTx");
Excel_SetCell(s, row, 26, sistEndretDato[i], "datoTx");
end for
Excel_Close(wb);
return path;
end
The function can be run from a 4D method like this:
// --- SQL4D_AliasIntoExcel ---
C_OBJECT($params)
$params:=New object("path"; "~/Desktop/SQL4D_AliasInto_test.xlsx")
$textResult:=ACF_RunText("SQL4D_AliasIntoExcel"; $params)
ALERT($textResult)
The database is a test-database, with fictive data and many empty fields. Anyway it shows the process working.
The resulting spreadsheet:
