[Erp5-dev] catalog usage in listboxes

[Łukasz Nowak]

Hello,

I'm preparing few reports in our system. Listbox is defined like this:

Portal Type: Sale Packing List Line
List Method: portal_catalog

Columns:

resource_title | Resource
total_quantity | Quantity
parent_title | Packing List
movement.start_date | Shipping Date
movement.stop_date | Delivery Date
translated_simulation_state_title | State
source_title | Send Goods From
source_section_title | Vendor
destination_title | Customer
destination_section_title | Owner

And those problems arisen:

* translated_simulation_state_title is not shown; if I use
simulation_state then it is shown
* I'm not able to search/sort on source/destination

I've tried to use parent.source_title, movement.source_title - but none
of those works (if I set it explicitly in sortable/search-able columns
it won't help me...).

Additionaly I really do not know why translated_simulation_state_title
hasn't shown.

Is it possible to use portal_catalog list method or shall I create my
own set of scripts/zsqlmethods which will be attached to those listbox
(like as in accounting module listbox)?

How to enable more verbose/debug output about catalog problems? Right
now if something is wrong LOGs are silent (eg. above -
translated_simulation_state_title is not showing - I cannot figure out
why (maybe it is somewhere on wiki - but so indirect, that I wasn't
able to figure out - my suspicion isn't enough to give me any sense
conclusion)).

By the way - is it possible to extend/create information about
portal_catalog, its limitations, best usages, etc?
HowToSearchPortalCatalog[1] is nice, but do not explain more advanced
usage (when I spot something, I'm trying to add it, but my progress is
quite slow).

Regards,
Luke

[1] http://www.erp5.org/HowToSearchPortalCatalog

[Jérome Perrin]

Post by Łukasz Nowak
Hello,
Portal Type: Sale Packing List Line
List Method: portal_catalog

Hi,
first a short note, you may want to use getMovementHistoryList for this
kind of reports (but I'm not sure it supports searching/sorting very well).

Post by Łukasz Nowak
* translated_simulation_state_title is not shown; if I use
simulation_state then it is shown

line.getProperty('translated_simulation_state_title') prints nothing
because accessors are not acquired in getProperty, and
getTranslatedSimulationStateTitle is on packing list, not on line.
'simulation_state' works just because getSimulationState method exists
on DeliveryLine. I don't know why it exists here though (maybe it was
added for indexing).

I don't know if it's the best way, but you can add a
listbox_translated_simulation_state_title editable field that has
'cell/getTranslatedSimulationStateTitle' in its 'default' TALES, this
way you can search & sort, because the listbox column is named
translated_simulation_state_title, which will be used as a catalog key
(using getTranslatedSimulationStateTitle as columns name displays the
value correctly, but searching won't work).

Post by Łukasz Nowak
* I'm not able to search/sort on source/destination
I've tried to use parent.source_title, movement.source_title - but none
of those works (if I set it explicitly in sortable/search-able columns
it won't help me...).

Look at generated query, and
portal_catalog/erp5_mysql_innodb/z_catalog_movement_category_list :
acquired categories are not catalogued for movements (this is an
optimisation).
With related keys, like "movement_source_title" that joins on source_uid
column from movement table, searching / sorting should work.

Post by Łukasz Nowak
Additionaly I really do not know why translated_simulation_state_title
hasn't shown.
Is it possible to use portal_catalog list method or shall I create my
own set of scripts/zsqlmethods which will be attached to those listbox
(like as in accounting module listbox)?

I don't recommend writing custom Z SQL Method, because of maintenance
cost. Most of erp5_accounting now uses portal_catalog / Inventory API
instead of many SQL methods.
With portal_catalog, you can always use related keys and editable fields
or scripts, so in theory you can do anything.

Post by Łukasz Nowak
How to enable more verbose/debug output about catalog problems? Right
now if something is wrong LOGs are silent (eg. above -
translated_simulation_state_title is not showing - I cannot figure out
why (maybe it is somewhere on wiki - but so indirect, that I wasn't
able to figure out - my suspicion isn't enough to give me any sense
conclusion)).
By the way - is it possible to extend/create information about
portal_catalog, its limitations, best usages, etc?
HowToSearchPortalCatalog[1] is nice, but do not explain more advanced
usage (when I spot something, I'm trying to add it, but my progress is
quite slow).

Yes, we can use the wiki page you mentionned. Having well written unit
tests can also be a way to demonstrate an API (better than nothing, and
at least always up to date).

J?rome

[Łukasz Nowak]

Hello,

On 2007-10-16, 19:00:55

Post by Jérome Perrin

Post by Łukasz Nowak
Hello,
Portal Type: Sale Packing List Line
List Method: portal_catalog

Hi,
first a short note, you may want to use getMovementHistoryList for
this kind of reports (but I'm not sure it supports searching/sorting
very well).

Post by Łukasz Nowak
* translated_simulation_state_title is not shown; if I use
simulation_state then it is shown

line.getProperty('translated_simulation_state_title') prints nothing
because accessors are not acquired in getProperty, and
getTranslatedSimulationStateTitle is on packing list, not on line.
'simulation_state' works just because getSimulationState method exists
on DeliveryLine. I don't know why it exists here though (maybe it was
added for indexing).
I don't know if it's the best way, but you can add a
listbox_translated_simulation_state_title editable field that has
'cell/getTranslatedSimulationStateTitle' in its 'default' TALES, this
way you can search & sort, because the listbox column is named
translated_simulation_state_title, which will be used as a catalog
key (using getTranslatedSimulationStateTitle as columns name displays
the value correctly, but searching won't work).

Huh, very nice trick, I've configured listbox to have editable column
'simulation_state', and set translated_simulation_state_title on
listbox's column list and configued field
listbox_translated_simulation_state_title. I dunno why but it works :)

Post by Jérome Perrin

Post by Łukasz Nowak
* I'm not able to search/sort on source/destination
I've tried to use parent.source_title, movement.source_title - but
none of those works (if I set it explicitly in sortable/search-able
columns it won't help me...).

Look at generated query, and
acquired categories are not catalogued for movements (this is an
optimisation).
With related keys, like "movement_source_title" that joins on
source_uid column from movement table, searching / sorting should
work.

I've used: movement.source_title, movement_source_title and
source_title. While using movement.source_title log is saying me:

2007-10-29T12:01:04 PROBLEM(100) SQLCatalog buildSQLQuery could not
build sort index (movement.destination_title -> None)

I *think* I'll have to configure something in catalog, but for now it
is quite cryptic for me. I'll try to resolve this issue when I have
more time.

Post by Jérome Perrin

Post by Łukasz Nowak
Additionaly I really do not know why
translated_simulation_state_title hasn't shown.
Is it possible to use portal_catalog list method or shall I create
my own set of scripts/zsqlmethods which will be attached to those
listbox (like as in accounting module listbox)?

I don't recommend writing custom Z SQL Method, because of maintenance
cost. Most of erp5_accounting now uses portal_catalog / Inventory API
instead of many SQL methods.
With portal_catalog, you can always use related keys and editable
fields or scripts, so in theory you can do anything.

Ok. In practise I'll try to avoid using custom queries as much as
possible. But also in practise I've got some quirks with configuring
related keys (in my context). But I'll try to figure out it
as best as possible.

Post by Jérome Perrin

Post by Łukasz Nowak
How to enable more verbose/debug output about catalog problems?
Right now if something is wrong LOGs are silent (eg. above -
translated_simulation_state_title is not showing - I cannot figure
out why (maybe it is somewhere on wiki - but so indirect, that I
wasn't able to figure out - my suspicion isn't enough to give me
any sense conclusion)).
By the way - is it possible to extend/create information about
portal_catalog, its limitations, best usages, etc?
HowToSearchPortalCatalog[1] is nice, but do not explain more
advanced usage (when I spot something, I'm trying to add it, but my
progress is quite slow).

Yes, we can use the wiki page you mentionned. Having well written
unit tests can also be a way to demonstrate an API (better than
nothing, and at least always up to date).

As I'm on early steps in finding out how portal_catalog works I'd be
glad to see some improvements on wiki pages :)

Thanks again for tips.

Regards,
Luke

[Łukasz Nowak]

Hello,

On 2007-10-16, 19:00:55
J?rome Perrin <jerome at nexedi.com> wrote:

(...)

Post by Jérome Perrin

Post by Łukasz Nowak
* I'm not able to search/sort on source/destination
I've tried to use parent.source_title, movement.source_title - but
none of those works (if I set it explicitly in sortable/search-able
columns it won't help me...).

Look at generated query, and
acquired categories are not catalogued for movements (this is an
optimisation).
With related keys, like "movement_source_title" that joins on
source_uid column from movement table, searching / sorting should
work.

Hm. I've played a lot with it, and I cannot search/sort on
source/destination_title:

=== Code ===
org = context.restrictedTraverse('organisation_module/22')
pc = context.portal_catalog

search_kw = {
'movement.source_title' : org.getTitle(), # XXX
'portal_type' : ['Purchase Packing List Line','Sale Packing List
Line'], 'limit' : 10
}

print pc(src__=1,**search_kw)


for q in pc(**search_kw):
print q.getRelativeUrl()

return printed
=== /Code ===

In this case everything is returned.
If XXX will become:
'movement_source_title' : org.getTitle(), # XXX
everything is returned.

If XXX will become:
'movement.source_uid' : org.getUid(), # XXX
it is working as excepted, but I really do not know how to use it in
listbox - shall I play with editable listbox'es fields for it?

But if XXX become:
'movement.source_section_uid' : org.getUid(), # XXX
Again everything is returned...

Is it possible to use above code with ability to search/sort on
source/destination (and section) in listbox?

I'll try to achieve it, but I worry that only experimentation has
left. _Maybe_ I'll hit good solution...

(...)

Regards,
Luke

[bartek]

Post by Łukasz Nowak
Hello,
On 2007-10-16, 19:00:55
(...)

Post by Jérome Perrin

Post by Łukasz Nowak
* I'm not able to search/sort on source/destination
I've tried to use parent.source_title, movement.source_title - but
none of those works (if I set it explicitly in sortable/search-able
columns it won't help me...).

Look at generated query, and
acquired categories are not catalogued for movements (this is an
optimisation).
With related keys, like "movement_source_title" that joins on
source_uid column from movement table, searching / sorting should
work.

Hm. I've played a lot with it, and I cannot search/sort on
=== Code ===
org = context.restrictedTraverse('organisation_module/22')
pc = context.portal_catalog
search_kw = {
'movement.source_title' : org.getTitle(), # XXX
'portal_type' : ['Purchase Packing List Line','Sale Packing List
Line'], 'limit' : 10
}
print pc(src__=1,**search_kw)
print q.getRelativeUrl()
return printed
=== /Code ===
In this case everything is returned.
'movement_source_title' : org.getTitle(), # XXX
everything is returned.
'movement.source_uid' : org.getUid(), # XXX
it is working as excepted, but I really do not know how to use it in
listbox - shall I play with editable listbox'es fields for it?
'movement.source_section_uid' : org.getUid(), # XXX
Again everything is returned...
Is it possible to use above code with ability to search/sort on
source/destination (and section) in listbox?
I'll try to achieve it, but I worry that only experimentation has
left. _Maybe_ I'll hit good solution...

Doesn't this page:

http://www.erp5.org/HowToSearchPortalCatalog

address some of your problems?

B.

Post by Łukasz Nowak
(...)
Regards,
Luke