JCurve Solutions would like to create new a report to retrieve data from transaction relate to bank account with unreconciled. And print reports in Excel format.
Requirement
New report ‘Bank Reconcile’ to retrieve data from transactions related to a bank account unreconciled.
Assumptions:
- This report will retrieve data from transaction related to a bank account with unreconciled
- Print report as Excel format
Sample Report:
Report Details – Criteria:
| Criteria | |||
| # | Field | Description | Details |
| 1 | Subsidiary | Select Subsidiary for report | name of Subsidiary |
| 2 | Bank account | Select Bank account based on subsidiary | Bank account filter by subsidiary |
| 3 | As at | Select as at Date to filter transaction | Input as at date to recall report compare with transaction date |
| Report Details | |||
| # | Field | Description | Details |
| 1 | Company Name | Show Subsidiary name that select for report | name of Subsidiary from custrecord_rapid_tht_company_legalname |
| 2 | Bank reconciliation | Report Name | Fixed |
| 3 | Bank Name and Account no | Bank Name and Account no | Bank account in select in the criteria |
| 4 | As at | Show date range to recall report | Based on date range to select in the criteria |
| Report Column | |||
| # | Field | Description | Details |
| 1 | Balance of Bank account ยอดคงเหลือตาม GL | Balance of Bank account | Show Balance of account before the select as of date. It will bepossible that the account will have balance either on debit or credit |
| 2 | Check and Payments | Group by Transaction of deduct from bank account | |
| 3 | Transaction type | Transaction type record in Ns | Type of document record |
| 4 | Date | Date | Date of transaction |
| 5 | Document No | Document No | Document no ex. Bill payment, Journal voucher no |
| 6 | Name | Name relate to transaction | Name relate in transaction (Entity) |
| 7 | Amount | Transaction amount | Amount >> reverse sign >> Credit Balance but put plus sign |
| 8 | Balance amount of Payment | Balance amount from Check and Payments | Summary for Check and Payments |
| 9 | Deposit and Other Credit | Group by Transaction of add in bank from bank account >> Debit Balance | |
| 10 | Transaction type | Transaction type record in Ns | Type of document record ex. Deposit, Journal voucher that relate toBank account |
| 11 | Date | Date | Date of transaction |
| 12 | Document No | Document No | Document no ex. Deposit no, Journal voucher no |
| 13 | Name | Name relates to transaction | Name relate in transaction (Entity) |
| 14 | Amount | Transaction amount | Amount >> reverse sign >> Debit Balance but put minus sign |
| 15 | Balance amount of Receive | Balance amount from Deposit and Other Credit | Summary for Deposit and Other Credit |
| Report Summary | |||
| # | Field | Description | NS field |
| 1 | Total Unreconciled ยอดคงเหลือตาม ธนาคาร | Balance of Bank account + Balance amount of payment – Balance amount of Receive |
Our Solution
This requirement can be achieved by creating a new report using a transaction saved search. And this search will retrieve data from transactions related to a bank account with unreconciled.
The navigation to the custom report can be added from the classic center. The user can navigate to Reports -> Custom Reports -> Bank Reconcile. Then upon clicking the “Bank Reconcile”, the user will be redirected to a report page.
The report will first display a detailed report based on the default filters, such as the subsidiary as G-ABLE CO., LTD, Bank account as 1101-0201เงินฝากออมทรัพย์ CITIBANK A/C 555-0482-012, and the As of Date as today, and then show a summary of the total amount in the report’s last row.
The fields given below will be indicated on the header part of the report.
| Criteria/ Available Filter: | ||
| # | Field | Field Name from NS records |
| 1 | Subsidiary | Subsidiary(subsidiary) list |
| 2 | Bank account | Account list from the Chart of Accounts |
| 3 | As at | Due Date(duedate) |
| Backend Criteria for Report | ||
| 1 | Account Type | Bank |
| Report Details: | ||
| # | Field | Field Name from NS records |
| 1 | Company Name | Subsidiary(subsidiary) name selected in filter |
| 2 | Bank reconciliation | Fixed |
| 3 | Bank Name and Account no | Bank account in select in the criteria |
| 4 | As at | Based on the due date(duedate) select in the criteria |
Please see the table below for the report column details for the detailed ‘Bank Reconcile’ report.
| Report Column | ||
| # | Field | Field Name from NS records |
| 1 | Balance of Bank account ยอดคงเหลือตาม GL | By default, will set the first bank account in the list of chart accounts. |
| 2 | Check and Payments | Fixed |
| 3 | Transaction type | Show the transaction types of checks and payments |
| 4 | Date | Transaction Date(trandate) from records |
| 5 | Document No | Document number of transactions |
| 6 | Name | Name(entity) on the transactions |
| 7 | Amount | Credit Amount from the GL impact of the records and put plus sign |
| 8 | Balance amount of Payment | Total amount of Credit Amount(plus sign) |
| 9 | Deposit and Other Credit | Fixed |
| 10 | Transaction type | Show the transaction types of deposit |
| 11 | Date | Transaction Date(trandate) from records |
| 12 | Document No | Document number of transactions |
| 13 | Name | Name(entity) on the transactions |
| 14 | Amount | Debit Amount from the GL impact of the records and put negative sign |
| 15 | Balance amount of Receive | Total amount of Debit Amount(negative sign) |
| Report Summary | ||
| # | Field | Description |
| 1 | Total Unreconciled ยอดคงเหลือตาม ธนาคาร | Balance of Bank account + Balance amount of payment – Balance amount of Receive |
Finally, on the report page, an Export button will be added to download the detailed report in Excel format.
When you click the export button, the report will be downloaded in Excel format based on the report’s size in the following cases:
- If the file size is less than 10Mb (based on predefined line count), the excel file will be downloaded directly from Netsuite. And in real-time, the excel file will be exported by splited files based on the line counts that can be included in the single file.
- Otherwise, the Report Download will take place through a scheduled process where there is a delay in the process based on the availability of the Netsuite Queue. For that will make use if a custom record to track the requests
- If the file size is greater than 10Mb, then we will split the file into multiple files with predefined line counts in a single file due to the size of each individual attachment cannot exceed 10Mb. And the generated multiple files will be emailed to the initiator’s email if the file count is smaller than or equal to the specific count that we can attach in the email.
- If the size of files is greater than the specific count, we will generate multiple files with predefined line counts in a single file. And all generated files will be saved in Netsuite’s file cabinet and attached to the corresponding custom record entry which tracks that Report generation process.
Once all export functions have been completed in NetSuite, a single email with the custom record link will be sent to the download initiator. The initiator can export files by login to the NetSuite and by navigating to the custom record entry with the link from the email.
The summary line on the Excel file will be calculated based on the result lines included in the corresponding file in the case of splitted file download
Note: Exported multiple files will be attached to the email is a very exceptional case. i.e. Most of the export function occurs in real-time or the files attached to the custom record.
Assumptions
- Each report page can include 1000 lines, with the 1001st line displaying a summary of the Total Amount of all results in the report.
- Only transactions that have not been reconciled will display in the report.
Risks
- The line counts in the single file to progress with the scheduled process can be confirmed during the development phase.
- If the file is more than 19Mb, the user should go to NetSuite and manually download the file from the account.
- The file cabinet will be stored with many files in the future. This will have an impact on the Netsuite account’s file cabinet. So, users have to remove these files from a file cabinet in the future to free up the storage.
- The report on the UI will be displayed as normal Netsite Table layout without any styles. It will follow the standard behaviour
- As per the standard suitelet elements, we can arrange the report in the following lines:
- First Bank account name – balance(All other columns are empty)
- Then Checks & Payments(In Thai) row
- Transaction Details…
- Amount in + sign(Credit)
- In the balance column, the total of credit amount
- Then Deposits & Other Credits(In Thai) row
- Transactions Details…
- Amount in – sign(Debit)
- In the balance column, the total of Debit amount
- The last row contains the total amount in the Balance column(Sum of Credit and Debit amount). All other columns are empty
- We are running all reports as a suitlet page, and the details will be displayed on multiple pages based on the results loading capacity. As a result of the lack of script loading, the sorting functionality will not work properly.