default search widget from SQLFORM.grid only searches the reference field by id but not format=''

[Rudy]

Hi there,

I have a quotation table which references the company table. When i created a SQLFORM.grid, I couldn't use the company_name as keyword in the default search widget as company field in the quotation table is integer type (id) even though it's displayed as company_name. I can write an action select_quote_by_name() to address it, but I wonder if there is any simple way to address this matter while I can still leverage the default search widget? Thanks in advance!

db.define_table('company',

                Field('company_name', requires=IS_NOT_EMPTY()), 

                format='%(company_name)s')

db.define_table('quotation',

                Field('company', 'reference company'),

                Field('project_name', requires=IS_NOT_EMPTY()),

                Field('quote_amount', 'double', default=0, writable=False))

grid=SQLFORM.grid(db.quotation)

[Richard Vézina]

Hello Rudy,

I am not sure I understand what you mean by : "I can write an action select_quote_by_name() to address it"

But, you sould be able to use "company" reference field representation like so :

db.define_table('quotation',

                Field('company', 'reference company',

                         represent=lambda id, row: db.company(id).company_name if id else T('N/A')),

                Field('project_name', requires=IS_NOT_EMPTY()),

                Field('quote_amount', 'double', default=0, writable=False))

This should show company_name in grid and you should be allow to search with company name in the search widget.

Richard

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Rudy]

Hi Richard,

Thanks so much for your advises. Before i added the represent=... to quotation table, i was able to see the company name (instead of company id) on my grid display, so displaying it wasn't an issue. I was also able to set a search criteria in the search widget e.g. Company Name = XYZ (from dropdown), the search textbook displayed quotation.company='4' next to the Search button in the widget. Only when I typed the company name in the Search textbox and hit the Search button, it didn't find it, but I could search for any string in text or string fields. If this system is for my own use, there is no issue, but for end user, it's very natural that enduser would like to find all the quotations for a single customer by typing the company name in the Search textbook, I wonder if there is a way to do that.

I tried below suggestion with 'represent', it didn't allow me to do what I wanted as described above. Any further input is appreciated. Thanks again

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.

[Richard Vézina]

Ok, I recall... It very sad that the search widget wouldn't be more helpful... In the past I ask for a way to obfuscated the backend field name, rname feature was intend to that at first, but at some point the development shift focus and it use finally get to use "natural" language field name, kind of the reverse of what I wanted... Anyway this is not fully related to your problem...

So, if you don't have issue with field name of the backend being exposed to the end user, you can workaround your issue by putting the id between paranthesis, so the power user can get used to remember the id of the record... Or teach them to use the query constructor, they pick the record by the representation but when they add the query it gonna be translated into the id... Hard to read back before confirm/submit the research if it get big, as you have to trust what the query builder had done and that you didn't make mistake between and/or... But it works.

Though it surely not ideal for end user... I start my app well before the SQLFORM.grid() feature, and I never really integrate with .grid() except in a couple of admin places, mostly because I don't want to expose backend field name... I never get time to customize the search widget and obfuscate the field name and maybe resolve the issue with the id that you state out as you only need to pass the input throught a python dict than search in the back end with the id you found or not base on the input, so it is not a big deal to hack... You only need to do it for reference field... Or you can search with "like" in SQL in the field you use for reprensentation...

Another solution you may consider is to use a js grid, like Datatables.net (open source - free) or ExtJS (open source maybe free if your app is not commercial) or other js frontend lib you may find convenient... You may construct you own HTML table to feed these js lib yourself with web2py HELPERS or you can (better) create a json controller to feed those tools even activate server side processing (grid paging) so you don't load a ton of data into you html page.

Walking the extra miles and integrate with other js tech may be useful, as you learn new skill and make your app look more professional, it really depends of your need and the nature of your project.

Good luck.

Richard

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscribe@googlegroups.com.

[Anthony]

The grid takes a "searchable" argument, which can be a function that generates a DAL query based on the submitted keywords and the fields. For an example, see the default search function: https://github.com/web2py/web2py/blob/baa129f8715a9d126a81e7ce098d349894d65baa/gluon/sqlhtml.py#L1922.

You can also substitute the default search widget with a custom widget via the "search_widget" argument. For an example, see the default widget: https://github.com/web2py/web2py/blob/baa129f8715a9d126a81e7ce098d349894d65baa/gluon/sqlhtml.py#L1955.

Anthony

[Rudy]

Hi Anthony,

Thanks for your advises, I'll try the searchable argument function first to leverage the built in search widget, i do find it very handy.

[Rudy]

Richard, thanks again for sharing your advises, much appreciate them.