Skip to Content

CREATING A FORMAT FOR A CSV FILE CONTAINING INFORMATION FOR RECONCILIATION USING ELECTRONIC FILE MANAGER PART 2

This blog is continuation of :

CREATING A FORMAT FOR A CSV FILE CONTAINING INFORMATION FOR RECONCILIATION USING ELECTRONIC FILE MANAGER PART 1

http://scn.sap.com/community/business-one/blog/2012/10/24/creating-a-format-for-a-csv-file-containing-information-for-reconciliation-using-electronic-file-manager

Aimed to explain how to design a bfp file bank statement format type, containing information relevant for reconcilation in SAP Business One Bank Statemt Processing.

(Steps 1 to 4)

–CONTINUATION (from STEP 4)–

Bank Account

The import statement functionality is designed to link the statement to the specific bank account selected in the bank statement processing window.

This has one consequence: The mapping of the bank account is mandatory.

Problem: In the csv file we don’t have any information related to the bank account, the bank account is something that we just know.

Solution:

The functionality allows assigning a Constant instead of Source value, so we will set our bank account as a constant value

The system allows assigning an additional field  to same position, so we do will use any of the already defined fields and link to it the additional mapping

General

Give Name and a Reference No

/wp-content/uploads/2012/10/csv30_149891.png

Attributes

Notice that we are assigning same location (index 1) than the field Date.

Could be any field that we are sure must be present in the file.

/wp-content/uploads/2012/10/csv31_149892.png

Conditions

(none)

Mapping

Notice that we have selected the box Use Constant instead of Source Value and in there the bank Account: 12345678

/wp-content/uploads/2012/10/csv32_149893.png

/wp-content/uploads/2012/10/csv33_149897.png

5 DO TEST HOW WOULD IT LOOK LIKE: DATA PREVIEW

In the menu File>Data Preview you can check if the outcome is as you like:

/wp-content/uploads/2012/10/csv34_149898.png

/wp-content/uploads/2012/10/csv35_149899.png

6 DO TEST IN BUSINESS ONE

After having assigned the new format to the company database and bank account 12345678 you can test with sample

/wp-content/uploads/2012/10/csv36_149900.png

ADDITIONAL INFORMATION

Training material and other examples related to this topic can be found in:

https://service.sap.com/~sapidb/011000358700000208942011E

To report this post you need to login first.

8 Comments

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

  1. Deepak Tyagi

    Hi jose

    I follow the same as you describe in your blog as part 1 and part 2.

    But not able to import the bank statement.

    First I did make a .bfp file through EFM and save it @ ‘E:\SAP\EFM\Yes bank statement Format’ as “YESBnkFrmt.bfp” . the same i attached in bank set up as bellow.

    Now I have a bank statement which is @ Desktop named as “BankStmtImport.csv”.

    Now I did try to import the statement, it shows the below error..

    you can see the data preview in EFM as below

    waiting for your reply..

    Thanks

    (0) 
  2. Jose Antonio Castillo Post author

    From 9.1, when you create a new bank format , the system will ask you if you want the old or the new functionality

    The new one, will open the standard view of add-on EFM (electronic File manager), and is the logical unification expected in the product.

    This has its pros and cons:

    Pros:

    You have all the same functionality that you have in EFM : which is by far, wider

    -A good one is that you can create the structure from and schema xsd, or a csv, and not only manual like in the old functionality,

    -The option to create bank formats for XML files (which with the old functionality is impossible)

    – Put validations, and complex mapping with conditions functions, etc

    Cons:

    In my opinion , the testing, is not so user friendly because if you do ‘advanced test’ using your sample bank statement file, you get in the output window an XML, which is what Business one will map, In the old application you had a user friendly preview , with header and rows.

    In summary, but is your choice: for plain text files I find it easier the old one, but if I had to create one that is a csv or xml, I would use the new one because I can create the structure from a csv or from xsd just by importing a csv or xsd file

    (0) 

Leave a Reply