Redesigning Bank Reconciliation

Imagine if programming languages didn't exist and developers had to write in assembly language (machine code) all the time. That's roughly how end-users feel when they have to work with bad UX. Doesn't matter how optimized the internal working is, a bad UX makes using the product unpleasant and difficult. Bank Reconciliation in ERPNext was one such feature. In this blog, I share my experience in redesigning it and how the UX is better than before.

What is bank reconciliation?

Bank Reconciliation involves matching the balance shown by the ERP and the balance shown by the bank statement either by creating a new voucher if it does not exist in the system but exists in the bank statement or by updating its clearance date according to the bank statement.

UX issues

Before making it better, we need to understand the UX shortcomings in the previous bank reconciliation interface.

1. The bank statement upload activity was not run by a background job. If the upload is long the request would time out.
2. The mapping between the bank statement fields and the Bank Transaction DocType had to be added in the Bank DocType. There was no way the user would know this unless he reads the documentation line by line.
3. The filters that controlled the bank transactions displayed were not constant. Every time the user selected a value in a filter, another set of filters would show up.
4. The suggestions were given by the system for manual vouchers matching just included names. This means that the user would have to go to the list of the doctype, open the voucher and verify if it is the correct voucher.

At Frappe, we understand the importance of UI/UX so we had to redesign the bank reconciliation tool.

The initial plan

This was my second project after the UAE VAT 201 Report. During the developer monthly planning meeting when I was given this project. I wondered “How long can a redesign project take? 2 weeks? Let me ask for some other work too.” But I was not assigned anything else since the experienced developers expected this to be a long project. I accepted reluctantly.

I started going through our older implementation and talking with mentors to understand what is expected from this project. This is the list we came up with after 2 hrs of brainstorming.

The project was divided into two parts—the bank statement import and bank reconciliation.

Bank statement import

We already have a Data Import Module. So reusing the backend looked like a good idea. We decided that adding an Import Bank statement button on the bank reconciliation page sounded right. I went ahead and ported the entire module to work on a dialog instead of a doctype (this involved reading through all the JS and overriding each method that was directly using a form or page instance). Later, during one of the reviews, we discovered that maintaining so many custom assets would be a difficult task so I ported it to work as a doctype. I was reluctant to this but when I was porting this feature to our latest rebranding I realized the importance of standardization.

Even though Data Import helped a lot, some extra features were added.

1. While importing each bank transaction from the Bank Statement Bank Account field has to be added in each row, this was done by hooking to the functionality of data import.
2. We do not want the user to write the mapping between bank statement header and bank transaction fields each time, so we save the mapping in the bank doctype on import, this mapping is then pulled back depending on the bank linked to the bank account.
3. Rebranding broke some components of the Base Data Import, so I fixed it while porting Bank Statement Import.

These were the outcomes:

1. The process has been streamlined a lot.
2. The UI is very similar to Data Import so it becomes intuitive.
3. Each Bank statement imported has a record in the Bank Statement Import Doctype Listview and the imported bank transactions are linked.
4. Bank Statement Import does not look like a report anymore :).

This is how it looks now:

Bank reconciliation screen

This complete screen involved lots of moving parts, each moving part is complex and demands its section. Don’t worry, I will keep it short and crisp by mentioning only important details :)

Since most of the major issues involved bad UX it made sense to make sure that the frontend is designed with caution. At the same time, it is necessary to use as many standard controls as possible so that it does not become a headache to match each style. Initially, it was a page so I used a field group to use standard controls. But why would you use a page if you are using standard controls? So I quickly converted it to a single doctype and used the standard form actions to control states.

The entire form above can be divided into four components. So I created a separate manager class for each component. There is no inheritance involved but basic composition. Using composition instead of inheritance provides more freedom to how the components interact. The bank reconciliation tool form has references to the summary card manager and the DataTable manager. The summary card manager has references to all three summary cards. The data table manager has reference to a dialog manager which drives the creation and matching process of vouchers corresponding to the listed bank transactions. Let’s see a bit of each.

1. The basic inputs

Here the required data from the Bank Statement needs to be precisely copied. After saving the form the tool is created. You may get a quick view of how it looks here.

2. Summary cards

The user must be notified of the success state. On success, the difference becomes zero, and the color changes to green. The Frappe Chart library provides CSS classes to create these cards. But we need to dynamically manipulate the numbers. So I wrote a wrapper class^[1] for Frappe number cards to manipulate the attributes such as numbers and colors. But these cards need to be managed as a whole so a class to maintain these properties was also written. This is how the summary cards look when the balances are not yet matching

3. Data table manager

Initially, we decided to use Frappe Base List for listing the bank transactions but it did not look feasible because:

1. The List does not look good when placed inside a component especially since the filters do not go well with other components.
2. The Base List is tightly integrated with the List View. A lot of methods need to be overridden to make it work directly with page.

So we decided to use Frappe Datatable—A DataTable manager class ^[2] that encapsulates all the required methods that were implemented. This class manages and manipulates the data that is to be displayed in the data table. A hash table is used to quickly refer to a row displayed in the DataTable. This was done so that the entire DataTable is not searched when a single transaction is updated or removed (If a bank transaction has 0 unallocated amount it is removed from the DataTable) to reflect it in the displayed DataTable. You may get a sense of how it looks in the following Image:

4. Reconciliation dialog manager

According to our initial goal of making sure that the user does not need to switch screens, we made sure to implement all the functionality into the Dialog that pops up when you click on the actions button. The dialog allows you to edit the bank transaction, match it to an existing voucher in ERPNext or create a new voucher using the details from the Bank Transaction. We also allow editing the voucher on a full-page. It helps if you have a lot of custom fields.

The filters allow selecting a specific voucher type to match to make sure no extra data is returned from the backend. Multiple vouchers can be matched against a single bank transaction by using the checkboxes The Reconciliation Dialog Manager handles all this functionality. ^[3]

If you are wondering how did we match and rank the vouchers that are to be displayed in the matching you can head over to the next section. The dialog looks a bit heavy but as you can see in the following image it covers almost all the use cases in a single view.

As I initially mentioned there are lots of minute details or optimizations that work towards the complete UX which are not mentioned here. You can always try out the software to get the full experience.

5. Finding the correct matches to Bank Transactions

So the challenge that I want to discuss here is to get the list of vouchers matching a specific bank transaction and then rank them according to the number fields matched. We are considering 5 doctypes (Payment Entry, Journal Entry, Sales Invoice, Purchase Invoice, and Expense Claim)

Broadly speaking, we had to achieve the following actions:

1. Retrieve rows from each of the 5 doctypes that have matching fields like posting date, amount, reference number, etc
2. Create a new field that counts the number of matching fields from each row. Name it ‘rank’.
3. Merge the rows from each doctype
4. Sort the merged rows according to rank

I implemented it in two ways, will discuss both of them and why I chose one of these.

5.1. The UNION ALL method

Although I did not use this method in the final cut you can see the implementation in one of my previous commits. ^[4]

The code is self-explanatory. I will go through some important details:

1. The innermost SQL queries would match a specific field of the bank transaction to the corresponding field of the voucher.
2. The results of all such vouchers matched with one field at a time need to be unioned (We use union all to retain duplicates. Why? Check the next point).
3. Now comes the interesting part. Since we retained duplicates, if we count the number of duplicates we will get the count of how many times each voucher was matched. But why do we need that? We had matched only one field at a time, so if a voucher had three matching fields it would repeat three times. Since we are grouping by voucher name and counting the duplicates we will get the number of fields of each voucher matched in the rank column.
4. Since we have the rank, all we need to do is sort descending by rank. Standard Order By is used to achieve it.

This method although lets the database optimize the query as much as possible has a big issue. When you use UNION ALL with an aggregate function, the output of each subquery is written to the disk. Since disk access is slower than RAM access, the benefit that I was assuming with letting the database do sorting would not matter. Moreover, we do not want so many temp files to be created each time we want to get the matching vouchers. ^[5]

5.2. The CASE WHEN Method

Although most of the previous method was right I had to get rid of the UNION ALL but to let the database optimize.

1. A query per doctype.
2. Count the number of matching fields in a row by using CASE WHEN. ^[6]
3. Sorting can be done based on the rank field.

Conclusion

In the end, the project took two months of development and was rigorously reviewed and tested for another two months. This project took my JS skills to another level and gave me a realization that learning SQL never ends. This has been my biggest contribution to opensource which involved adding around 3K lines and deleting 5K lines.

Special thanks to Saqib for mentoring me throughout. I cannot end the blog without thanking Prasad for helping me make this blog more appealing.

Here we are at the end, Hope you learned something new today. There are more details that are not included in this blog but you can head over to the PR to know more.

References

1. Summary card manager class
2. Datatable manager class
3. Reconciliation dialog manager class
4. The UNION ALL method for finding matching vouchers
5. Union All worklog
6. Example of using Case When to get the rank