fnct_search missing for many function fields

Hello,

This is an interesting suggestion, but the reality is much more complicated, unfortunately.
There are two mains reasons for that:

1.- Not many function fields can have a proper fnct_search method. In many cases, the only way to implement it would be to compute the value of the function field for all database records, and then search among these for a match. This is not only very inefficient, but also dangerous in terms of performance, and cannot be allowed. On top of it, this approach requires implementing many search operators (like, ilike, =, !=, >, <) in pure python (for searching among the computed results in memory) which would be stupid: the database is the right place for this.
So it only makes sense to implement the fnct_search when this search operation can be translated into a very efficient postgresql query. This is the case for the "Available quantity" of production lots, because you can make an efficient query on the only stock moves that are related to this specific production lots, presumably not many, and the compute their balance.
It is typically _not_ the case for the "Quantity on Hand" field of products, because you would need to compute that value for _all_ products in the database, and only afterwards match the desired filter.
And as you would like a button on the search view for "Quantity on Hand > 0", imagine the disaster!

2.- For the reason exposed in 1.- about operator implementation, the few fnct_search implementations we have only support very few operators, and not very complex domains. So you get a very partial support for searching on function fields this way.

Because of the above, let me suggest a different approach: the community can suggest minimalist implementations of fnct_search using merge proposals. If the implementation is efficient and correct (or can be made so), we'll help to get that merged into the official branches.
And of course, if this is something you cannot live without, you could always make a custom module that adds the desired fnct_search - but that would be too dangerous in the core :-)

PS: By the way I've explained many times that the search on function fields that was available in 5.0 was totally broken and misleading ;-) It only worked on the records currently visible in the client. This was by nature wrong, and even worse: most people were not aware of it! Please do not call that a "lost feature", it was never there ;-) To use your example: if you were filtering/sorting on the "Product/Stock on hand" you would only do that on the 80 products visible by default - useless and wrong....
Instead of having a (silently) wrong and misleading feature, it's clearly better to remove it. As you can understand with the above explanation, making this feature really work is not just a matter of throwing in a few easy fnct_search implementations!

Thanks for the explanation, but IMHO this is a real desaster for multi stock locations where certain locations are used only for a few products ( as I explained already frequently )
if we can not filter (efficiently) for !=0 we get the complete list of products for every location.

and of course it was necessary to increase the limit in v5 to see all records (which was sort of slow) and sort/filter them afterwards, but it was possible, now it's not any more. so I still see it as a lost feature, because OpenERP does not give a chance to get a usable result anymore.

BTW it's problematic if the designers prohibit that users can do their work efficiently.
it always takes much more time if the computer system does not offer the functionality and users have to do it manually = export all data to prepare data/reports which are required in an organisation. the later does NOT care about the issues you mentioned.

suppress "zero" lines is (was) a general usability issue in OpenERP. Nobody is interested to see endless lists and reports with zeros. please see also our new financial reports.
http://bazaar.launchpad.net/~c2c/c2c-financial-addons/6.0/files/head:/account_financial_report_webkit/

may be we will need some client side tools to accomplish day2day tasks.

please check how Koo did it.

On 09/16/2011 12:27 AM, Ferdinand @ Camptocamp wrote:
> Thanks for the explanation, but IMHO this is a real desaster for
> multi stock locations where certain locations are used only for a
> few products ( as I explained already frequently ) if we can not
> filter (efficiently) for !=0 we get the complete list of products for
> every location.

That's a valid but different and more specific topic, one that can be
discussed without having to put it in a global "function fields" package.

> and of course it was necessary to increase the limit in v5 to see
> all records (which was sort of slow) and sort/filter them afterwards,
> but it was possible, now it's not any more.

A very bad practice indeed, and that obviously does not scale: hitting
every single stock move line in the database, multiple times.
That's just a bad solution for function fields in general, no matter how
you look at it.
Instead of supporting this kind of broken solution, it's much better to
offer better alternatives for specific needs.

> suppress "zero" lines is (was) a general usability issue in OpenERP.
> Nobody is interested to see endless lists and reports with zeros.

One possible alternative for inventory per location: have you tried to
use the "Warehouse>Reporting>Inventory Analysis" view? If you
group/filter per location, you should only see the products that have
ever been in the specific location (some could still have 0 qty if the
products are all gone, but for specialized locations this would work
well enough).
That analysis view should suffer much less of performance issues,
because you can drill-down to zoom only on what you care for, and it has
been optimized to hit the database less.

For the generic Products lists I don't think we need this, most users
will expect to see the complete list of products anyway (See bug 636215
where this was already discussed - almost a duplicate)

One more alternative for location inventory views: the wizard that opens
the inventory for a location could have an option to show only available
products - very simple and much more scalable, I think.

This is all a different wishlist improvement, though. Might be good to
put this on feedback.openerp.com if it's not there yet?
Wishlist bug 677454 might also be considered there, a bit different but
probably doable efficiently too.

> please check how Koo did it.

What does Koo improve in the computation of function fields?

thanks

1) Warehouse>Reporting>Inventory Analysis
the grouping does not work correctly currently (can not group by location AND product - its always product/location regardless in which order the buttons are pressed) so I can not try and confirm

1a) IMHO nevertheless needs a button "Show only not zero" (default ON) , because all products where in=out will be shown with 0 Quantity - in the long run almost every product.
(instead of making everyone create/save a function "Quantity is not equal 0" - which of course works here as qty is a DB (view) field)

1b) the result of any grouped views as "Inventory Analysis" can neither be printed nor exported for further processing
- a missing feature

BTW we are working on a XML/CSV export utility which is started by a wizard and runs on the server to avoid transfer timeouts etc.
http://bazaar.launchpad.net/~c2c/c2c-rd-addons/trunk/view/head:/table_generate_xml
- draw back - OpenOffice does not import all +100.000 CSV raw data we export.
- workaround - create a module/view in OpenERP that groups before export.

2) the wizard that opens inventory for a location could have an option to show only available products
* this is what I have done for v5 already - should be come standard and default in many wizards

3) Koo
I thought of incremental display of data to avoid the necessity to compute all lines at once
and (new) apply filters on the computed results.
IMHO there is no reason why filters can not be applied to results (appart that it is not coded)
the "simple" rule
domain
* is DB field - add condition to SQL WHERE query
* else filter results.
would do it.
If the organization needs filtered results these must be produced somewhere / somehow

4) Performance
4a) one day we will need materialized views (period sums) to avoid expensive computation based on millions of stock_moves
in one of our former installations we have some 50000 moves for the most frequent used products (for ~ 10 years).
If OpenERP has to compute the qty always on 50000 moves it will become slow overall because theses are the products used most frequently.
4b) please think again of SAAS or remote (ADSL) locations. All data we do not have to transmit save time (and costs for not flat rate lines) and users do not have to examine /exclude these visually.
for many operations it does not make sense to display products with real stock = 0 especially if
http://bazaar.launchpad.net/~c2c/c2c-rd-addons/trunk/files/head:/c2c_stock_negative/
is installed.
the purpose of forbidding negative stock is to correct the data before the process can continue. - it's painful but often the only way to enforce correct stock handling and correct average prices(per stock location) and real time moves.

sorry for beeing long....

On 09/16/2011 01:33 PM, Ferdinand @ Camptocamp wrote:
> 1) Warehouse>Reporting>Inventory Analysis the grouping does not work
> correctly currently (can not group by location AND product - its
> always product/location regardless in which order the buttons are
> pressed) so I can not try and confirm

We would have to analyze this as a bug and reproduce it, because as far
as I can see it works well in the 3 clients I just tested: 6.1-web,
6.0-web and 6.1-gtk, and I can have grouping as Location>Products

> 1a) IMHO nevertheless needs a button "Show only not zero" (default
> ON) , because all products where in=out will be shown with 0 Quantity
> - in the long run almost every product. (instead of making everyone
> create/save a function "Quantity is not equal 0" - which of course
> works here as qty is a DB (view) field)

Why not, that could be nice to have.

> 1b) the result of any grouped views as "Inventory Analysis" can
> neither be printed nor exported for further processing - a missing
> feature

The Print button on any Analysis view should work well in the GTK client
to export the data as PDF, also respecting the grouping. And for the web
client, all browsers let you print the current page with their built-in
print menu.

As for CSV export, we tried to provide a basic support for the "export
data" wizard on all analysis views, similarly to what works on normal
tables. Just tested on the 6.0-web branch on runbot (exporting the
inventory analysis view after unchecking 'import compatible' mode) and
it seems to mostly work (you can export the data), but maybe not in all
cases.

> BTW we are working on a XML/CSV export utility which is started by a
> wizard and runs on the server to avoid transfer timeouts etc.
> http://bazaar.launchpad.net/~c2c/c2c-rd-addons/trunk/view/head:/table_generate_xml
> - draw back - OpenOffice does not import all +100.000 CSV raw data we
> export.
> - workaround - create a module/view in OpenERP that groups before
> export.

Great, all contributions are always welcome!
The case of timeouts for long-running wizards might need a proper
solution, though, rather than workarounds.

> 2) the wizard that opens inventory for a location could have an
> option to show only available products * this is what I have done for
> v5 already - should be come standard and default in many wizards

Excellent, could you perhaps provide a minimalistic merge proposal for
adding that to trunk, in order to make it available in standard addons?

> 3) Koo I thought of incremental display of data to avoid the
> necessity to compute all lines at once
> and (new) apply filters on the computed results.

Afraid that won't help for function fields, as performing a correct
search/filter would still require loading the whole table - no magic bullet.

> IMHO there is no reason why filters can not be applied to results
> (appart that it is not coded)
> the "simple" rule
> domain
> * is DB field - add condition to SQL WHERE query
> * else filter results.
> would do it.

My opinion is that this is very counter-intuitive and would be a great
cause of hidden mistakes for most users - how will they be able to tell
the difference between the two...

Hello,

Let me comment that a Akretion we also have several customer very
embarrassed by the lack of filtering of product quantities with 0.
As for the Inventory Analysis, it turns out it is extremely slow in 6.0 with
many moves and indeed, it's impracticable to export as far as I know.

We actually even lost one of our first hardly conquested Brazilian customer
because of such issues and also the useless dashboard (that even need to be
removed thanks to our addons-no-fluff branch) that were making
OpenERP impracticable in a 10 users companies but with many moves (an
audiobook editor). An other customer of us is #2 in the USA and we believe
it's only because of such size that we manage to tackle such bugs one by one
by developing workarounds. Had them been smaller they would have give up
too. So those issues really matter.
So just weighting in to tell that indeed, what Ferdinand has reported is a
quite important issues for companies with many moves.

On Fri, Sep 16, 2011 at 10:27 AM, Olivier Dony (OpenERP) <
<email address hidden>> wrote:

> On 09/16/2011 01:33 PM, Ferdinand @ Camptocamp wrote:
> > 1) Warehouse>Reporting>Inventory Analysis the grouping does not work
> > correctly currently (can not group by location AND product - its
> > always product/location regardless in which order the buttons are
> > pressed) so I can not try and confirm
>
> We would have to analyze this as a bug and reproduce it, because as far
> as I can see it works well in the 3 clients I just tested: 6.1-web,
> 6.0-web and 6.1-gtk, and I can have grouping as Location>Products
>
>
> > 1a) IMHO nevertheless needs a button "Show only not zero" (default
> > ON) , because all products where in=out will be shown with 0 Quantity
> > - in the long run almost every product. (instead of making everyone
> > create/save a function "Quantity is not equal 0" - which of course
> > works here as qty is a DB (view) field)
>
> Why not, that could be nice to have.
>
>
> > 1b) the result of any grouped views as "Inventory Analysis" can
> > neither be printed nor exported for further processing - a missing
> > feature
>
> The Print button on any Analysis view should work well in the GTK client
> to export the data as PDF, also respecting the grouping. And for the web
> client, all browsers let you print the current page with their built-in
> print menu.
>
> As for CSV export, we tried to provide a basic support for the "export
> data" wizard on all analysis views, similarly to what works on normal
> tables. Just tested on the 6.0-web branch on runbot (exporting the
> inventory analysis view after unchecking 'import compatible' mode) and
> it seems to mostly work (you can export the data), but maybe not in all
> cases.
>
>
> > BTW we are working on a XML/CSV export utility which is started by a
> > wizard and runs on the server to avoid transfer timeouts etc.
> >
> http://bazaar.launchpad.net/~c2c/c2c-rd-addons/trunk/view/head:/table_generate_xml
> > - draw back - OpenOffice does not import all +100.000 CSV raw data we
> > export.
> > - workaround - create a module/view in OpenERP that groups before
> > export.
>
> Great, all contributions are always welcome!
> The case of time...

Am Freitag, 16. September 2011, 13:27:05 schrieben Sie:

> On 09/16/2011 01:33 PM, Ferdinand @ Camptocamp wrote:

> > 1b) the result of any grouped views as "Inventory Analysis" can
> > neither be printed nor exported for further processing - a missing
> > feature

>
> The Print button on any Analysis view should work well in the GTK client
> to export the data as PDF, also respecting the grouping. And for the web
> client, all browsers let you print the current page with their built-in
> print menu.
pls see screenshot
IMHO not entrprise ready

>
> As for CSV export, we tried to provide a basic support for the "export
> data" wizard on all analysis views, similarly to what works on normal
> tables. Just tested on the 6.0-web branch on runbot (exporting the
> inventory analysis view after unchecking 'import compatible' mode) and
> it seems to mostly work (you can export the data), but maybe not in all
> cases.
well it does not work with grouped data

> > 2) the wizard that opens inventory for a location could have an
> > option to show only available products * this is what I have done for
> > v5 already - should be come standard and default in many wizards

>
> Excellent, could you perhaps provide a minimalistic merge proposal for
> adding that to trunk, in order to make it available in standard addons?
I'll try to do so

> Afraid that won't help for function fields, as performing a correct
> search/filter would still require loading the whole table - no magic bullet.
true - but not at once, it would be incremental
execpt for sort operations which of cours need the complete results

but beeing honest -
how much extra time does it take to sort let's say 10000 records (products) in python memory using the results of function fields (compared to any other manual method)?
the time to culculate the function field can be omitted, because if the user needs the full list sorted these must be calucalted in any case.

> > IMHO there is no reason why filters can not be applied to results
> > (appart that it is not coded)
> > the "simple" rule
> > domain
> > * is DB field - add condition to SQL WHERE query
> > * else filter results.
> > would do it.

>
> My opinion is that this is very counter-intuitive and would be a great
> cause of hidden mistakes for most users - how will they be able to tell
> the difference between the two cases?

why it is not the job of the user to decide on this, it's a one time coding
issue
put all database field condition in the SQL WHERE clause
and filter the result of the SQL query using python just before displaying
these.

let' say the company wants to see all products with a stock value of >10000

OpenERP should be able to provide this info.

and of course all records must be examined,

but this is reality - organizations (and bosses) ask these sort of questions

and we partners are looking really silly if we have to explain that the most
advanced open source ERP can not to such "easy" tasks.

--
ferdinand

This module does not currently work correctly, but see if this can be of use or adapted to server some purpose in regards to multiple stock locations. One customer I have has over 1,000 stock locations. It is not possible find where a product is in the system. When you update the stock using the update button it will show the total value of stock in all locations on hand, but if you do not know where the stock is, you cant manage the stock. This is a serious problem with any customer than has many locations. If you click the sort button at the top it does not sort by highest to lowest quantity like you can on the sales order view.

please read here for serverside search and sort of function fields
http://www.camptocamp.com/fr/blog/2011/10/sort-and-filter-options-for-function-fields-in-openerp/

Can someone please mark this "wishlist"?
From comment #1 is seems it's not really a "won't fix" topic: " the community can suggest minimalist implementations of fnct_search using merge proposals. If the implementation is efficient and correct (or can be made so), we'll help to get that merged into the official branches."
Lionel.

Ferdinand wrote that a generic solution was found: has there been a merge proposal on this topic?
Lionel.

yes we are working on it, still some issues

Hello
Does someone know if this problem is solved or if there is a working solution in OERP V7?
René

On Thu, Feb 7, 2013 at 11:17 AM, René Glauser <email address hidden>wrote:

> Hello
> Does someone know if this problem is solved or if there is a working
> solution in OERP V7?
> René
>

Hello, I believe it's the same situation in OpenERP v7.

Regards.

We came up with 2 solutions
1) one2many_sorted
we use it in reports and often created an second one2many relation to display data in a user friendly way

http://bazaar.launchpad.net/~camptocamp/c2c-rd-addons/7.0/files/head:/one2many_sorted/

2) server patch (for 6.0)
see attachments for server - allows filter and sort of all fields

2a) and GTK
necessary to make function fields searchable

until now nobody really cares

* add this to the server config file to activate the pythonic sort

[orm]

fallback_search = True

I wonder did any one in management of OpenERP SA ever imagine a Partner explaining to a client of his :

"sorry, you are not able to perform a search on quantities of product available in stock".

This is relevant of openerp's global strategy and is just astonishing...

Would it be so resource intensive to change "qty_available" to a **stored** function field with appropriate store parameters (watching stock.move) ?

An other option : stock level per location per product could be a dedicated object with appropriate triggers all throughout the stock modules in order to maintain a single value readable at all times.

Anyway the most important consideration is not how we would achieve it, it is the end user experience.

For the records:
- you can't use "store" on context-sensitive function fields like qyt_available because the results do not depend (only) on the data.
- "stock level per location per product could be a dedicated object": yes this is the "Quant" object in v8

I can only repeat myself - for some years now asking for this feature

the server must be patched/adapted - see above #17 and 18

it is just not true that it is not possible

and it is better/cheaper/faster to let the server do the search / filter / sort then asking the user to export data and do it in excel ....

Sure it can be done (we did it on v6) -just not by a trivial "store={}" trick.
I thought someone had posted a patch for this already, but I couldn't find.

Ferdinand do you have a patch against v7.0 ?
Markus Schneider posted this module for v7: http://bazaar.launchpad.net/~initos.com/initos.com-openerp-addons/7.0/revision/17
Otherwise we can still clean up and port our own patches to v7 if that's the only solution.

@Lionel
https://bugs.launchpad.net/openobject-server/+bug/629332

** Ferdinand do you have a patch against v7.0 ?
No I gave up to discuss improvements
The "not invented here syndrome" is prevailing - see financial reports in v8 - camptocamp has provided super reports and we see the unusable old stuff again using the new report engine.