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;

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.

Ian Grieve

About Ian Grieve

Ian is a Microsoft Dynamics GP certified consultant specialising in the delivery of Microsoft Dynamics GP projects and currently working for Perfect Image Ltd., a Microsoft Partner and VAR in the North East of England. Ian has worked with Microsoft Dynamics GP since 2003 and, over the nine years since, has dealt with all aspects of the product life-cycle from presales, to implementation, to technical and functional training, to post go-live support and subsequent upgrades and process reviews. In his spare time, Ian runs the azurecurve | Ramblings of a Dynamics GP Consultant blog dedicated to Microsoft Dynamics GP and related products.
This entry was posted in Dynamics, GP, Microsoft and tagged , , , , , , , , . Bookmark the permalink.

2 comments on “EFT File Format Import/Export Error

  1. DeniseF on said:

    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

    • 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 *

*

* Copy This Password *

* Type Or Paste Password Here *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>