Custom Design or Manually Create Data Exchange Definition in Business Central - Bank Account Statement (Posted BRS) Report
Agenda of the Video: Based on any Company Requirement, we can manually assign Columns for BRS Report using Data Exchange Definition Setup.
➡️To create a Customised Bank Statement Report, take the help of a Developer or Technical Consultant.
✅Hint: BRS = Bank Reconciliation Statement,
OR
Bank Account Statement (Report).
#brs #statement #StatementReport #microsoftdynamics365businesscentral #d365bc #finance #bankBook #bankreconciliationstatement #microsoft
---------------------------------------------------------------------------
✅Topic: Custom Design or Manual Creation of Data Exchange Definition Setup (for Import Bank Statement, File Format) in Business Central
Report Name = Statement Report
Number of Columns = 6
➡️1️⃣ Transaction Date✅
➡️2️⃣ Description (Transaction Details)✅
➡️3️⃣ Statement Amount✅
➡️4️⃣ Value Date (Bank Clearing Date)✅
➡️5️⃣ Related Party Name✅
➡️6️⃣ Related Party Transaction Details (Reference)✅
---------
You may also add...
➡️7️⃣ Check (Cheque) Number (No.)✅
➡️8️⃣ Check (Cheque) Date (issue date)✅
➡️➡️9️⃣ Debit (Dr.) Amount✅
➡️1️⃣0️⃣ Credit (Cr.) Amount✅
---------
Outcome➡️ Match Balance as per Company Bank Book = Balance as Per Pass Book (Bank Book).
Read the related Blog Post: ➡️https://navisionfunctionalexpert.com/...
-------------------------------------------------------------------------
➡️To create a Customised Bank Statement Report, take the help of a Developer or Technical Consultant.
✅Hint: BRS = Bank Reconciliation Statement,
OR
Bank Account Statement (Report).
#brs #statement #StatementReport #microsoftdynamics365businesscentral #d365bc #finance #bankBook #bankreconciliationstatement #microsoft
---------------------------------------------------------------------------
✅Topic: Custom Design or Manual Creation of Data Exchange Definition Setup (for Import Bank Statement, File Format) in Business Central
Report Name = Statement Report
Number of Columns = 6
➡️1️⃣ Transaction Date✅
➡️2️⃣ Description (Transaction Details)✅
➡️3️⃣ Statement Amount✅
➡️4️⃣ Value Date (Bank Clearing Date)✅
➡️5️⃣ Related Party Name✅
➡️6️⃣ Related Party Transaction Details (Reference)✅
---------
You may also add...
➡️7️⃣ Check (Cheque) Number (No.)✅
➡️8️⃣ Check (Cheque) Date (issue date)✅
➡️➡️9️⃣ Debit (Dr.) Amount✅
➡️1️⃣0️⃣ Credit (Cr.) Amount✅
---------
Outcome➡️ Match Balance as per Company Bank Book = Balance as Per Pass Book (Bank Book).
Read the related Blog Post: ➡️https://navisionfunctionalexpert.com/...
-------------------------------------------------------------------------
Category
📚
LearningTranscript
00:00In the first video we published, I told you how you can create a format for bank statement import and export.
00:27I also showed you how you can design a data exchange definition file setup.
00:40Let me show you again as a short video.
00:44This is how I made it.
00:47This time I have taken 5 columns and I will use those 5 columns.
00:57Now let's come to Bank Account Reconciliation.
01:05Before starting this, let me show you the data exchange definition.
01:14You will get the data exchange definition, bank export and import setup in video part 2.
01:20In the same video part 2, you will also get the data exchange definition.
01:24Now I have to check the DBI bank that I have made.
01:30I have made it with this name and it is also installed in my bank setup.
01:34If it is installed in my bank setup, then let's check it.
01:38You have to check the entire header.
01:43You can also pre-define the header.
01:47You can use the wizard for bank statement importing.
01:53But I made it manually because I needed extra columns.
01:58So I used 5 columns.
02:02The first column is my transaction date, second is description, third is amount, fourth is value date.
02:10What is the difference between transaction date and value date?
02:13Transaction date is the day you posted on the system.
02:16You made a bank leisure entry, bank payment, bank receipt, contra, any entry.
02:21And what you posted was your transaction date.
02:25But value date, when you get the bank statement from the bank.
02:29So when was the entry recorded in the bank?
02:34Suppose you issued a cheque and deposited it after 2 days.
02:40And the bank also takes 1 or 2 days for clearing.
02:44So the day the bank cleared the cheque, the value date can be 2 or 4 days later than your transaction date.
02:52If it is not the same day, then you have to match the value date for the same transaction.
03:00So the value date will have a separate column and the transaction date will have a separate column.
03:04Because you posted on a different day in the system, but the bank cleared the cheque on a different day.
03:10So these can be two different dates.
03:13That entry was posted in your system and the bank recorded that entry.
03:19So you can keep it separately to match it.
03:23Third party name, if any third party is involved, then you can record its name in the bank statement.
03:30So you can map it in your entries and post it.
03:33The system gives you this functionality.
03:35Document number, if you have any invoice number, check number or any document number through which the document was posted.
03:44For reference, you can also take other reference number, additional transaction number.
03:51But if you want to use a posted document number or want to use a document number for reference,
03:58which was also on your system and which was also in your bank statement for matching, then you can use this.
04:04So what to do for this, I have done mapping for this.
04:08I will click here.
04:09I have defined the line in the line definition.
04:13I clicked on manage and field mapping.
04:16How did I do field mapping?
04:18I made six columns.
04:23And after making six columns, what did I do?
04:26I took the transaction date, I took the description, I took the amount, I took the value date, I took the third party name, I took the document number.
04:34The column caption that is coming here, you will not get that name on the system table.
04:40So what do you have to do, you have to go inside, check once and see what name it is here.
04:46What is the name here?
04:48First we took the transaction date.
04:50So we have to check with the name of the transaction date whether we are getting it or not.
04:53So we are getting the transaction date, so we took it.
04:55Secondly, we need the description.
04:57Are we getting the description from this name?
04:59Check it.
05:00Yes, I am getting it.
05:01So you took that too.
05:02So this is the value date.
05:04Are we getting the value date?
05:06And if you want to scroll and search, then it's okay, otherwise you can search from here too.
05:10So you are getting the value date too.
05:12So you took the value date too.
05:14But this third party name and this amount, in the case of amount, you are getting the statement amount.
05:20You can check this.
05:21So I took the statement amount.
05:23And what is happening in the case of third party, in the case of third party, I have taken the 15 number series.
05:30Number.
05:31Related party.
05:33I was getting it from this name.
05:34And if you want to add a check, you can take the check number.
05:37If you want to add additional transaction information, you can make more columns.
05:41And you can map.
05:43I took the document number.
05:45This was not so much of a compressor for me.
05:48So what did I do?
05:49I have kept it at the end.
05:50I have already mapped the priorities for me.
05:54And see what is optional, this is an optional button.
05:56What is optional, third party name is optional for me.
05:59Document number is optional for me.
06:01So I have kept everything optional.
06:02And the transformation rule that I have given is that the document number will be alphanumeric.
06:09Okay, so I took the code from here.
06:12Alphanumeric.
06:13And whatever my document number is, it can be like that.
06:15And there can also be an overwrite value.
06:17If I import it, it will overwrite it.
06:20So you have to come like this.
06:23And whatever mapping code I have taken here, you can take it.
06:27And the column count was 6, I will make it 6.
06:31So this is 6, we have taken 6.
06:34Okay.
06:35So when you have it here, the system works like this.
06:39That there are 6 columns in the column definition.
06:41Then we mapped it.
06:44And now we will go back.
06:47So this data definition file, if you make it manually, it will help you a lot.
06:55After making it, if you do manual work, then after making it, you will have to attach it.
07:03In the bank export import setup.
07:06Now I have attached this.
07:09You can check that.
07:11I have attached this.
07:15Here.
07:16According to its name, you will know.
07:19Because I have written year 2024 in it.
07:22So this is the map here.
07:24August 24.
07:25With this name.
07:26Okay.
07:27DBI import.
07:28And I have also kept the data exchange definition code in the same way.
07:32So you can map it like this.
07:34And this mapping will be in your bank account card.
07:37Your bank account card.
07:39Now, like this one, which is on the bank account reconciliation page.
07:43Okay.
07:44But still I will show you the bank account card once.
07:47We can go from here too.
07:48We can go from there too.
07:51Now where is this mapped in this bank account card?
07:54In this bank account card.
08:00This is mapped here.
08:03The bank export import setup is mapped here.
08:09You have to map the bank export import setup with this.
08:13And in the bank export import setup.
08:15You have to attach the data exchange definition setup.
08:19So you will be able to import easily.
08:22With conditions.
08:23Whatever conditions you are applying.
08:32Okay.
08:33That's it.
08:35Bye.
08:36Bye.
08:37Bye.
08:38Bye.
08:39Bye.
08:40Bye.
08:41Bye.
08:42Bye.
08:43Bye.
08:44Bye.
08:45Bye.
08:46Bye.
08:47Bye.
08:48Bye.
08:49Bye.
08:50Bye.
08:51Bye.
08:52Bye.
08:53Bye.
08:54Bye.
08:55Bye.
08:56Bye.
08:57Bye.
08:58Bye.
08:59Bye.
09:00Bye.