CREATING A FORMAT FOR A CSV FILE CONTAINING INFORMATION FOR RECONCILIATION USING ELECTRONIC FILE MANAGER PART 2
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
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
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.
Conditions
(none)
Mapping
Notice that we have selected the box Use Constant instead of Source Value and in there the bank Account: 12345678
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:
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
ADDITIONAL INFORMATION
Training material and other examples related to this topic can be found in:
Gud informative Blog
Very helpful and informative blog
Good One
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
One more Thing, I am not able to run test or Advanced test because these are disabled (Grey).
Thanks .
any suggestion Jose! ?
ohhh ..Thanks a lot Jose..... Done it.
Dear Mr Deepak Tyagi,
How did resolve this Error, when you Imported the file.txt?
Error
Thank you.
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