Bank Reconciliation Book-to-Bank functionality

Here’s a brief video of the book-to-bank functionality I’ve added. Why? Because I’m used to reconciling to the net change in cash, not the total of credits and debits. I don’t even think I’ve ever seen a bank statement offers that, but I didn’t want to take it away, because obviously people are using it. This method makes more sense to me and it’s how I learned how to reconcile a bank account.

So here’s where I’m stuck: I’d like to include some kind of indicator that tells the user the date of the oldest uncleared item, preferably in the current fiscal year so as to eliminate some extra noise. My SQL is working for Payment Entry but not for Journal Entry even though they’re basically identical. I’d like to add a BETWEEN statement to get the fiscal year start to current period end to make this a bit more manageable. This seems like a better way to inform the user than a message that just says “You have uncleared items in a previous period” which is how QuickBooks does it.

oldest_payment_entry = frappe.db.sql(
		"""select min(posting_date) from `tabPayment Entry`
		where ifnull(clearance_date,"")!="" """)
oldest_journal_entry = frappe.db.sql(
		"""select min(posting_date) from `tabJournal Entry`
		where ifnull(clearance_date,"")!="" """)

Once I’ve debugged the SQL piece (with your help, you lovely community) I’ll be making a pull request to merge the changes in.

1 Like

did you finalize this feature? because i am also looking for this and unfortunately i could not find a way forward

This qualifies as a breaking change to the design of this interface and should not be merged. The best case is an alternative reconciliation or a setting to make it so. What I have now only exists as custom code.
I will add it to the agenda for the Accounts Working group call on 3/21/19.

thanks for the update

hi @tmatteson This is what I was looking for today with a client. Are you using this function? And what has been your experience? If possible kindly share the Git link where I can pull it for my instance.

Thank you.

1 Like