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:
It defines several functions for creating SQL insert statements for different types of data, such as message headers, sequences, and transactions.
It reads the contents of a CREMUL file and splits it into segments, where each segment is separated by a single apostrophe ('').
It then iterates through the segments and processes each one based on its type, extracting relevant information and populating variables.
The code organizes the data into three arrays:
dataMHfor message headers,dataSQfor sequences, anddataTRfor transactions.It inserts the data from these arrays into the corresponding database tables using SQL insert statements.
The code also performs some data validation and error checking to ensure that the CREMUL file is well-formed.
It includes a function
import_cremul_select_filethat 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,
- Header
- Sequences
- Transactions
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: