Redesigning Bank Reconciliation in ERPNext

What is bank reconciliation?

Bank reconciliation entails matching the balance shown by the ERP with the balance shown by the bank statement, either by establishing a new voucher if it does not exist in the system but does in the bank statement, or by amending the voucher's clearing date to match the bank statement.


UX issues

Before we can improve it, we must first understand ERPNext's UX flaws in their previous bank reconciliation interface.

  1. A background job was not used to execute the bank statement upload. The request would time out if the upload was too long.

  2. The Bank DocType had to be updated to include the mapping between the bank statement data and the Bank Transaction DocType. Unless the user reads the documentation line by line, there was no way for him to know.

  3. The filters that determined which bank transactions were displayed changed over time. A new set of filters would appear every time the user picked a value in a filter.

  4. The system provided ideas for manual vouchers that only included names. This implies the user must go to the doctype list, open the voucher, and double-check that it is the correct voucher.

They knew the importance of UI/UX at ERPNext, so they had to redesign this tool.


Bank statement import

A Data Import Module has been available. As a result, reusing the backend appeared to be a smart approach. Mohammad Hasnain Rajan, Software Engineer at Frappe, thought it would be a good idea to add an Import Bank Statement button to the bank reconciliation page. Instead of utilizing a doctype, they ported the entire module to use a dialog (this involved reading through all the JS and overriding each method that was directly using a form or page instance). After discovering that maintaining so many custom assets would be challenging during one of the reviews, they adapted it to work as a doctype. hesitant at first, but as I worked on porting this feature to our most recent rebranding, I understood how important standards is.


Even though Data Import was really helpful, some more functionalities were introduced.

  1. When importing each bank transaction from the Bank Statement, the Bank Account field has to be added to each row. This was accomplished by hooking into the data import procedure.

  2. They didn't want the user to have to write the mapping between the bank statement header and the bank transaction fields every time they import, so they saved it in the bank doctype and fetched it back depending on the bank associated to the bank account.

  3. Rebranding broke some Base Data Import components, which had to be addressed while porting Bank Statement Import.


The outcome?

  1. The whole process has been simplified.

  2. UI is quite similar to Data Import, thus it's very easy to use.

  3. The Bank Statement Import Doctype Listview includes a record for each bank statement imported, and the imported bank transactions are linked.

  4. Importing bank statements no longer looks like a boring report!



Bank reconciliation screen

This entire screen has a lot of moving pieces, each of which is intricate and has its own segment. Let's speed this up by only stating the most crucial parts!


Because the majority of the major issues included poor UX, it made sense to ensure that the frontend was carefully planned. Simultaneously, it is vital to use as many common controls as feasible so that matching each style does not become a pain. Because it was originally a page, they utilized a field group to implement standard controls. But, if you're utilizing normal controls, why would you use a page? This was quickly addressed by transforming everything to a single doctype and controlled states using regular form actions.


There are four parts to the above form. As a result, each component now has its own manager class. There is no inheritance involved, only fundamental structure. The interaction of the components is more flexible when composition is used instead of inheritance. The summary card manager and the DataTable manager are mentioned on the bank reconciliation tool form. All three summary cards are referenced in the summary card manager. The data table manager contains a dialog manager reference, which controls the creation and matching of vouchers for the mentioned bank transactions. Let's have a look at some of each.


1. The basic inputs

The required data from the Bank Statement must be accurately copied here. The tool is created once the form is saved. You may get a small glimpse of how it looks here:


2. Summary cards

The success state must be communicated to the user. When you succeed, the discrepancy disappears and the color turns green. CSS classes are provided by the Frappe Chart library to build these cards. However, we must manipulate the numbers in a dynamic manner.


3. Data table manager

Initially, Frappe planned to publish the bank transactions using Frappe Base List, but this did not appear to be viable because:

  1. When the List is placed inside a component, it doesn't flow well, especially since the filters do not go well with other components.

  2. The List View and the Base List are intimately connected. To have it operate directly with the page, many methods must be overridden.

They decided on a Frappe Datatable—A DataTable management class that encapsulates all of the implemented and necessary methods. This class is in charge of managing and manipulating the data that will be shown in the data table. A hash table is used to refer to a row in the DataTable fast. This was done so that when a single transaction is updated or removed (if a bank transaction has 0 unallocated amount, it is removed from the DataTable), the full DataTable is not searched to reflect it in the presented DataTable. The following image will give you an idea of how it looks:


4. Reconciliation dialog manager

They made sure to put all of the functionality into the Dialog that pops up when you click on the actions button, in order to ensure that the user does not have to transfer screens. The dialog allows you to update the bank transaction, match it to an existing voucher in ERPNext, or create a new voucher using the bank transaction's details. This also allows full-page modification of the voucher. If you have a lot of custom fields, it helps.


To ensure that no unnecessary data is returned from the backend, the filters allow you to specify a certain voucher type to match. Using the checkboxes, several vouchers can be matched against a single bank transaction. All of this is handled by the Reconciliation Dialog Manager.



Conclusion

After two months of development, the project was extensively reviewed and tested for another two months. This project advanced Mohammad's JS abilities and taught him that understanding SQL is a never-ending process. This was his largest contribution to opensource, involving the addition of over 3000 lines and the deletion of approximately 5000 lines.



Excited to learn more about ERPNext? Join ERPNext Users Philippines today!

CLICK HERE to like our Facebook page and get the latest updates.

4 views