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.