For example, in Sales Invoice List, I want to filter only docs that “Outstanding Amount” < “Total Amount”.
I think, filter now only can compare a field with a value, i.e., Out Standing Amount > 0.
But to compare 2 fields, seem super tricky to me. Only way I can think of is to create and compute a new field, i.e., “Partly Paid” = [1, 0] and use filter on this field. Super complicated for a simple task.
Any easier way to do?
in which document you want to apply this logic??
It is Sales invoice. But actually I would ask also for the concept as I should apply any doctype.
You can do this with Query Builder, but it is not possible from the List page or with any of the standard api methods/rest interface.
Thanks! @peterg
I am aware of that, and so, may be the only way is to make another field and compute it with update hook then 
That would be a another good way to do it, much more performant than running the calculation for every document on every read.
We have to admit that it won’t be that clean and easy to do things in the low code way although I am ok/not ok with adding new custom_partly_paid field. As in many area, Frappe use doc.db_set() any bypass all the event hooks.
In this case, I want to hook to Sales Invoice to set value,
But the doc.db_set() in set_status() don’t trigger hook.
Frankly, it is quite annoying.
The only way I can see is to write python code in a custom app using class extension to the set_status().
Another way is to write a scheduled job to keep updating (which is CPU intensive and not real time).
May be server script doc event should have one for db_set() too, WDYT about this?
Hmm…I don’t understand what you’re saying here. What’s the issue with using an update hook? In what circumstances is it being bypassed?
You could also use a virtual field, though I haven’t checked recently where/when those are accessible from the desk interface and api calls.
Firstly, the virtual field is not good here, becasue it can’t be used as filter or display in the list view.
Says, I have Sales Invoice 100 USD, and submitted.
Then, I make payment partially 30 USD, and submit the payment, now, the sales invoice’s set_status() as I linked to the github will be called (when submit Payment).
There, on set_status(), I want server script to trigger on sales invoice but it won’t happen because set_status() is not using save() method, but use db_set().
Ah, I see what you’re saying. Yes, since the status field is a derived value, changes aren’t triggering event hooks. If you want to catch changes to the outstanding balance of a Sales Invoice, your best bet would be to catch the thing making the change (i.e., watch for changes to GL Entry or Payment Entry docs).
Thanks for your answer, at least I know I don’t misunderstand it.
