How to get date when sales invoice is fully "Paid"

I want to keep record of customer invoice age history i.e when an sales invoice gets cleared. Or date on which outstanding_amount of sales invoice gets zero.
What is best / easiest possible solution ?

Hi there,

The most robust way, I think, would be with a script report. You’d run through each invoice and poll ledger entries against them. It would take a bit of python knowledge to do, but you would have less worry about data consistency than if you used a custom field of some sort.

python is no issue.
I can think of two different ways.

  1. schedular event , just like invoice status, which will pull invoice and check if outstanding_amount just got zero or status changed to Paid then update custom_field of age by number of days.
  1. Controller hook on payment Entry which check if then related invoice’s outstanding_amount just changed to zero then update the custom_field of age by number of days

or any other way because i want to avoid custom docField of “age” in sales_invoice docType. I need expert opinions?

I agree with your preference to avoid using a custom field “age”. That’s why I suggested using a script report instead. Wherever possible, tidy/normalized data is always preferable.

If you decide to use a custom field despite the downsides, I’d suggest a controller hook on the GL Entry doctype. Both scheduler events and controller hooks have limitations, such as the fact that payment entries aren’t the only thing that can change the outstanding balance on a sales invoice (off the top of my head, both journal entries and dunnings can, too).

Nice suggestion of GL Entry.
i can think of 1 more way via notification: on value change, set property after alert

About script report, don’t you think it will get too slow to calculate dynamically. or may be i an not getting your point correctly.

That might be possible, though I believe that outstanding_balance happens via a frappe.db.set_value call, which happens at a low level bypassing ORM lifecycle calls.

It depends on how often you need to view the report and how many invoices you’re dealing with. A lot of it, I suspect, could be done directly at the database query level, which is extremely optimized.