EFT File Format Import/Export Error

Microsoft Dynamics GPI’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;

EFT File Format

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;

   EFTFormatID = 'HSBC'
   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;

SQL Results of Query on CM00103

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;

   SERIES = 4
   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.

What should we write about next?

Your Name (required) –
Your Email (required) –

3 thoughts on “EFT File Format Import/Export Error

  1. DeniseF says:

    Hi Ian,

    You seem to have a working BACS file format. I’m wondering which Dynamics GP fields you used for Header Label 1- “Serial Number” & Batch Header – “File Number” ? I’m having trouble identifying unique fields in GP that can populate this information. Any help or suggestions your can provide will be greatly appreciated.

    thank you,


    1. Ian Grieve says:

      Hi Denise,

      I used Transmission Date for Header Label 1 Serial Number (formatted YYMMDD) and a constant of 001 for the Batch Header File Number.

      I don’t remember what these are meant to be off the top of my head but the client is successfully using this template.

Leave a Reply

Your email address will not be published. Required fields are marked *