Interacting with a Web Microservice and Updating the Database
This demo showcases how to use an ACF custom function to interact with a web microservice and update the database with the data it returns.
The microservice, developed in PHP, is responsible for moving XML files stored locally to an SFTP server. It then scans a designated folder on that SFTP server to retrieve XML receipt files. These files are decoded, and the service returns an SQL statement that updates the database.
This is part of a comprehensive solution designed to generate EHF format files for invoicing. Another service within the solution handles the generation of these files based on invoice data. The generated files are stored on the server until this microservice is called to deliver them to the SFTP server, where they are queued for delivery.
The function is triggered once all EHF documents are generated. It retrieves and decodes the receipt files, then uses an SQL INSERT statement to add the retrieved data into a FileMaker table, allowing the receipts to be viewed directly within the invoice layout.
/*
Check status, send accumulated EHF files from the server, retrieve receipt files, and update the database.
*/
function EHFStatus ( string APIkey, string Bank, string Company, string addr1, string addr2, string postnr, string sted, string orgnr )
// Make the post data:
string crlf = "\r\n";
string vb1 = "----WebKitFormBoundaryvaBIqQXnhryXmJxO";
string vb2 = "--" + vb1 + crlf;
string vb3 = "--" + vb1 + "--" + crlf;
string vUrl = "https://example.com/ws/EHFStatus_new.php";
string h1 = "APIkey||Bank||FirmaNavn||Adresse1||Adresse2||Postnr||Sted||OrgNr\n";
string h2 = APIkey + "||" + Bank + "||" + Company + "||" + addr1 + "||" + addr2 + "||" + postnr + "||" + sted + "||" + orgnr + crlf;
string postdata = vb2 + "Content-Disposition: form-data; name=\"CompanyDetails\"" + crlf + crlf + h1 + h2;
postdata = postdata + vb3;
$$LastPost = postdata; // To debug, let's see the post after call in the data viewer
// headers in the post request
string hdrs = format ( "Content-Type: multipart/form-data; boundary=%s",vb1 );
// Then send the data
string resData = HTTP_POST ( vUrl, postData, hdrs);
// Parse the result...
if ( pos ( resData, "FEIL")>-1) then
alert ( resData);
end if
string sttag = "===DATA TO BE INSERTED===";
string entag = "===END DATA to BE INSERTED===";
string insdata = trimboth ( between ( resData, sttag, entag));
bool allcomp = pos(resData, "===ALL COMPLETED===")>0;
bool partly = pos(resData, "===PARTLY COMPLETED===")>0;
// Update the database
string sql, ressql;
if (length(insdata)>0 ) then
sql = "INSERT INTO EHF_InvoiceStatus (Invoice_nr, RecDate, RecTime, Message) " + insdata;
print sql;
ressql = ExecuteSQL ( sql );
if (ressql != "" ) then
alert (ressql);
end if
end if
return partly;
end
When run, this now results in a SQL statement to update the database. The SQL is executed by the function using its ExecuteSQL implementation that also can insert data opposed to the same function in FileMaker that does not allow that. The microservice just returns the part starting with "VALUES", so the first part is supplied by the function in the SQL below.
INSERT INTO EHF_InvoiceStatus (Invoice_nr, RecDate, RecTime, Message) VALUES
(55893, DATE '2024-08-16','13:15:20','Document has been processed and forwarded to PEPPOL'),
(55890, DATE '2024-08-16','13:15:20','Document has been processed and forwarded to PEPPOL'),
(55892, DATE '2024-08-16','13:15:20','Document has been processed and forwarded to PEPPOL'),
(55891, DATE '2024-08-16','13:15:20','Document has been processed and forwarded to PEPPOL'),
(55889, DATE '2024-08-16','13:15:20','Document has been processed and forwarded to PEPPOL'),
(55895, DATE '2024-08-16','13:15:20','Document has been processed and forwarded to PEPPOL'),
(55896, DATE '2024-08-16','13:15:20','Document has been processed and forwarded to PEPPOL'),
(55897, DATE '2024-08-16','13:15:20','Document has been processed and forwarded to PEPPOL'),
(55899, DATE '2024-08-16','13:15:20','Document has been processed and forwarded to PEPPOL'),
(55901, DATE '2024-08-16','13:15:20','Document has been processed and forwarded to PEPPOL'),
(55897, DATE '2024-08-16','12:56:36','Kvittering for mottatt EHF faktura fra NETS'),
(55899, DATE '2024-08-16','12:56:36','Kvittering for mottatt EHF faktura fra NETS'),
(55901, DATE '2024-08-16','12:56:36','Kvittering for mottatt EHF faktura fra NETS'),
(55889, DATE '2024-08-16','12:56:33','Kvittering for mottatt EHF faktura fra NETS'),
(55890, DATE '2024-08-16','12:56:33','Kvittering for mottatt EHF faktura fra NETS'),
(55891, DATE '2024-08-16','12:56:32','Kvittering for mottatt EHF faktura fra NETS'),
(55892, DATE '2024-08-16','12:56:32','Kvittering for mottatt EHF faktura fra NETS'),
(55893, DATE '2024-08-16','12:56:34','Kvittering for mottatt EHF faktura fra NETS'),
(55895, DATE '2024-08-16','12:56:35','Kvittering for mottatt EHF faktura fra NETS'),
(55896, DATE '2024-08-16','12:56:36','Kvittering for mottatt EHF faktura fra NETS')
If everything is OK, the return of the ExecuteSQL is an empty string, so we test. If any error, we got an error message that is displayed using the "Alert" function.
If there are more than 50 files, the microservice returns the status for those, and needs to be re-run to handle the next 50. The check
bool partly = pos(resData, "===PARTLY COMPLETED===")>0;
tells us if we are done or have done a partly operation. This is to avoid the PHP script getting a timeout during its operation. This value is returned to FileMaker, that executes this in a loop until it returns "0".
In the invoice layout, we got this for one of the invoices.
The PHP script in the microservice accumulates a string containing the SQL part. The PHP service has this implementation:
if ($finished) {
echo "\n===ALL COMPLETED===\n";
} else {
echo "\n===PARTLY COMPLETED===\n";
}
if ($Records) {
$xsql = trim($sql, "\n\r,");
echo "\n===DATA TO BE INSERTED===\n".$xsql."\n===END DATA to BE INSERTED===\n";
}
This makes the return have this SQL part enclosed in a start and end tag in its output.
In the ACF function, we extract this part using those lines:
string sttag = "===DATA TO BE INSERTED===";
string entag = "===END DATA to BE INSERTED===";
string insdata = trimboth ( between ( resData, sttag, entag));
The "between" function extracts what is between the start and end tag, and the "trimboth" function trims away white space on each side. This ensures that the variable "insdata" is empty if there is nothing but white space between the tags. Otherwise, there is some data to be applied in our SQL statement.
if (length(insdata)>0 ) then
sql = "INSERT INTO EHF_InvoiceStatus (Invoice_nr, RecDate, RecTime, Message) " + insdata;
print sql;
ressql = ExecuteSQL ( sql );
if (ressql != "" ) then
alert (ressql);
end if
end if
How did we do it before?
We used two different plugins to accomplish this. TroiURL to do the HTTP POST part, and MyFMbutlerDoSQL to execute the SQL query. Since MyFMButler_DoSQL isn't maintained anymore, we needed to come up with some change.
We had a standard Custom Function like this:
Then the FM script needed to parse the result to extract the SQL data, and use MyFMbutler_DoSQL to insert the values.
You see, the custom function is similar, but there is no error checking. The custom function above only does the microservice part. The new function does it all completely, with error checking and a proper message. This then seems to be a valid upgrade to our functionality.