SEE MY YOUTUBE VIDEO ON THIS TOPIC HERE: https://youtu.be/pu7eLHE9if8

Since SAP Business One 9.0 I had been hearing about new EFT capabilities built right into the system and I had a client request this feature so I decided to really dig into the subject. So after a LOT of investigation and testing I will summarize in plain English what I found.

 

EFT_02.png

 

(NOTE: my focus here is on EFT files, NOT bank reconciliation files but SBO can do this as well. I will attempt to note this whenever possible.)

 

SAP Business One Has a Really Well Thought Out EFT System

It’s hard to find and understand but it is well thought out once you get the hang of it. It can do EFT files for payment, import files for bank reconciliation and generate CSV files in specific format for any other file type you would want to work with. I was using it specifically for an EFT outgoing payment file to a bank for payments.

 

 

The Electronic File System is Two Part

It is not documented very well but you need two SAP add-ons to make this system work properly. One to edit the files called Electronic File Manager (EFM Format Definition) and then one to generate the payment file from the Payment Wizard (Payment). Both should come with a standard installation, in this screenshot I’m using SBO 9.0 PL15.

 

EFT_01.png

 

Built into SBO all you need to define is the file format for the payment method (Administration >> Setup >> Banking >> Payment Methods) for it to be activated with your particular payment method. Of course you need to define the payment method as default for the vendors (this can be done with DTW) and then you are good to go with the payment wizard. If you are having issues with the actual upload to the “File Formats – Setup” just right click the row and click “Assign Format Project…” to assign your file.

 

The System is Based on Crystal Reports

The EFM uses a very nice Crystal Reports data source setup. You can import and export datasources to Crystal to make modifications, re-import them easily (with automatic link updates) and generally craft your output data using CR grouping and formatting. There is a limited set of formatting formula options within the EFM tool though.

 

The Actual File Export is Hard to Find!

I made my file and installed it without any issues but finding the actual output was damned near impossible. Once I found it though, the system was pretty tight, letting you generate test files and even recompile the information source before exporting. SO, if you spot missing bank information or the bank rejects the file for some reason you can make the changes and regenerate the file before exporting.

 

EFT_03.png

 

Once you find the little button then you are good to go, but I had no idea where to find it! Here is what the screen looks like:

 

EFT_04.png

 

The button is on the very last page of the Payment Wizard form at the bottom and is only visible when the “Payment” add-on is running.

 

Documentation is Hard to Find

You will find an ooolllddd expert empowerment session which is referenced but it doesn’t really have the information you are looking for (2007A time period). But if you look hard enough you will stumble across a couple of good references. Here are some links:

 

Reference #1
Reference #2

 

You can also use the EFM help file which has a lot of information specifically for formatting and editing within the editor but good overall reference once you find it. Activate the add-on called “EFM Format Definition” from SBO, then relaunch the client. Once installed and running go to Tools >> Electronic File Manager: Format Definition to run the editor. Once in the editor click Help >> Online Help or F1.

 

There is a Great Sample to Start With

In Reference #2 there is a .BPP file which is a great starting point to understand the structure, I don’t think two banks will be exactly the same so you will likely be adapting it from scratch any way you approach it.

 

There is NO Standard Layout

From what I can see, each bank (I’m located in Canada, maybe USA or other is different) has their own type of file format definitions that differ slightly (or majorly depending on your bank).

 

Here are the formats that come with the system:

 

EFT_05.png

 

(NOTE: Minus the 80Byte which is my own)

 

So if any of those formats fit your definition exactly then you are in luck and it will save you a ton of work. If not, then you will have to get the file format definition from your bank. Also as a side note, banks may or may not be willing to really help you out with the formatting and testing, depends on the bank and branch, etc.

 

Summary

With the two add-ons in place and the reference materials you load up your EFM program and use the template as a starting point, from there you can just add sections and subsections based on your bank documentation. Upload the file and associate it with the payment method of choice (must be bank transfer) and then set as default for vendors. As you are using the payment wizard the steps will be the same, once you get to the very end and the run has been executed, just look for the button in the lower left area to generate your file. Upload to your bank et voilà!

To report this post you need to login first.

11 Comments

You must be Logged on to comment or reply to a post.

  1. Denis Nevzorov

    Hello Mike!

    Thank you for post.

    May be you can help me. I created BPP file and all work correct till moment when i try generate bank file with several payments. I got not right format:

    Docnum – 1

    Docnum – 2

    Doctotal- 100

    Doctotal- 200

    etc

    Should be:

    Docnum- 1

    Doctotal -100

    Docnum 2

    DOctotal- 200

    etc

    In Crystal report i can see right format. Do you have any ideas?

    Thank you.

    (0) 
    1. Mike Taylor Post author

      Hi Denis,

      I map the Group Header from Crystal to the details section I want to group by.  See example: /wp-content/uploads/2015/08/layout_772029.png

      When you use the original layout it doesn`t properly group the records.  When you send the EFT file to be processed you don`t normally want:

      Doc1

      Doc2

      Vendor Total 1

      Doc1

      Doc2

      Vendor Total 2

      This would mean that the vendor will receive two individual payments (or in some case hundreds of individual payments) that they would have to reconcile instead of one grouped payment.  Most companies just want the vendor totals such as:

      Vendor Total 1

      Vendor Total 2

      So I had to reformat my CR to do this.  I base all of my EFT files off of my new grouped CR .RPT and it is much easier.  I also cleaned out the unused fields and renamed my julian date fields, etc. to make linking easier.

      So keep this in mind as well when making a layout.  If you do need each individual row, try my Group Header #3 linking as above in my image.  If not, let me know,

      M

      (0) 
      1. Julie Jamieson

        Hi Mike,

        I have a csv format which is working, but the bank require the IBAN number to be exported inside double quotes. I have tried updating the source to enclose it, but the quotes get stripped on output.

        I have tried using concat to add the quotes but they get enclosed in their own quotes so I get 3 at each end.

        Have you come across anything similar and managed to resolve it?

        (0) 
        1. Mike Taylor Post author

          Hi Julie,

          The files I have worked with all require TXT only (usually 80 byte length meaning just 80 characters per line, no comma delimiting).  I have not had to work with a CSV style output yet for a bank layout.

          SO, I would first ask if you maybe used the wrong output file type?  I think it would be unusual for the ACH 80 or 1464 byte files to be delimited by commas since they use a fixed column width.

          Secondly, did you try the concat from within the EFM Manager?  Or just added quotes in the Crystal Report?  If you didn’t try both I would suggest it.  Make sure if you are doing the formula in the EFM Manager that you have to set the reference for the field first before using it.

          Also, be careful not to use “curly” or rich-text style double quotes as these special characters can cause issues.  IE, watch out for copy and paste between MS Word or a word processor like that.

          Let me know!

          Mike

          (0) 
          1. Julie Jamieson

            Hi Mike,

            The format required is not fixed length so I can’t use a txt format as that leaves spaces which aren’t allowed.

            Have tried both concat in EFM and adding in CR, and am just using doudle quotes from keyboard.

            Not sure what you mean by setting the reference? but the field is created

            (0) 
            1. Mike Taylor Post author

              Hi Julie, if you are already doing the concat in the EFM then you understand the reference field system for formulas.  I don’t know what else to try with your layout, I would need to have the data, the file and the PDF describing the layout. If you feel like emailing me the requirements file to mtaylor@forgestik.com I’ll take a quick look.

              (0) 

Leave a Reply