EFT File Format Import/Export Error

I’ve recently been creating an HSBC Standard 18 BACs file format for a client in Microsoft Dynamics GP 2010 R2.

Unfortunately, once I released it to their system, they reported an error with the import where the Creditor’s Cheque Name wasn’t being output on the resulting file.

I did some testing and it turns out to be a bug in the export function of the EFT File Format (Cards » Financial » EFT File Format) window in Microsoft Dynamics GP 2010 R2; I have subsequently tested on GP 2010 SP3 and GP 2013 and the import/export works fine. I realised something odd was going on when I looked at the template in the EFT File Format window (Cards » Financial » EFT File Format) and saw that while the Series was defined as Purchasing in the header, the scrolling window for the Data Field only had Receivables Management tables and fields available to it and not the Payables Management tables and fields I would expect;

I ran a SQLquery against the database to check the template header on the Chequebook EFT Format Header (CM00103) table and quickly narrowed the problem down to the Series as it had been set by the import.

The query to identify the problem was a simple one;

SELECT
   EFTFormatID
   ,DSCRIPTN
   ,SERIES
FROM
   CM00103
WHERE
   EFTFormatID = 'HSBC'
OR
   SERIES = 4

Payables Management is Series 4 in Microsoft Dynamics GP but I found that on the imported EFT file format the SERIES column had been set to 0 during the import;

Two minutes later I had an update script which would set the SERIES for a specific EFT File Format which in this example is HSBC;

UPDATE
   CM00103
SET
   SERIES = 4
WHERE
   EFTFormatID = HSBC'

To update a different EFT File Format, change the highlighted section to the relevant ID.

Once I’d run the script to correct the Series, the generated EFT files included the Creditor’s Cheque Name and the file could be submitted to ther BACs processor.