Dynamics Community Forum Thread Details

Dynamics GP Electronic Bank Reconciliation

Does anyone have a sample file that I can show the bank I'm working with?

They don't seem to understand what a simple flat file is and they keep sending me the complete bank statement with headers on each page and descriptions wrapped on 3 or more lines

Categories:

Microsoft Dynamics GP

Hello Deanne,

Ask the Bank to send it in 'bai' format with .bai (or .txt or .csv) suffix. Maybe they will understand that terminology. Basically each line in the file needs to start with a record type code to signify what type of line it is. If they send you a file with 'record type codes' at the beginning of each line, then the system should be able to read it.

Always open files like this with Notepad, not Excel. (Excel has auto-formatting functionality that strips out leading zeroes)

Example file: (You can copy the below in to Notepad and save it as a .txt or .csv file)

----------------------------------------

01,,,170412,0840,53064,,,2/
02,TEST,125108272,1,17012,,USD,2/
03,44444,USD,010,0,,Z/
16,475,10000,,200609,,,CK0001,/
16,475,20000,,200609,,,CK0002,/
49,3127136,5/
03,55555,USD,010,0,,Z/
16,475,10100,,200609,,,0001,/
16,475,20100,,200609,,,0002,/
49,3127136,5/
98,3127136,1,6/
99,60200,,4/

----------------------------------------------

For example, in the above. the '03' line tells the system to use the checkbook ID that has 44444 as the bank account. Then the '16' line is the first payment to bring in. Transaction code 475 means 'check'. The check amount is $100.00, cleared on June 9th, 2020 and the check number is 'CK0001'.

---------------------------------------------

In a BAI file the record type codes are: (Although lines 03 and 16 are the main lines needed to be able to read a file)

01- file control line

02- batch/group header line

03 - Account identifier --includes bank account number (to be able to find the checkbook in GP)

16 - payment detail line - includes check amount, check number, transaction type (check or deposit), and bank cleared date

49 - Account footer

98 - Group footer

99 - File footer

88- Continuation line

I hope that helps Let me know what additional questions you have.

Cheryl Waswick