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;

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;

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;

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.

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.

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,

    Denise

    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 *