SQLFORM smart grid child table fields and editing excelike

[Yebach]

Hello

I am using SQLFORM smartgrid. My child table field is not shown on my form (status.s_code). Why?

Also. If I (user) click(s) on edit, all fields are editable. how do I set which fileds are or can be editable and which one not.

Is it possible to create a form that is click in editable (like excel?), so u don't have to open another window?

thank you

This is my code in contorller.

workers = db(db.worker.w_organisation == 10).select(db.worker.w_id_w, db.worker.w_organisation, db.worker.w_first_name, db.worker.w_last_name,db.worker.w_nick_name,db.worker.w_email,db.worker.w_status,db.worker.w_note).as_list()

#Define the query object. Here we are pulling all contacts having date of birth less than 18 Nov 1990

query = ((db.worker.w_organisation == 10) )#& (db.worker.w_status==db.status.s_id_s))

 

fields = (db.worker.w_id_w,db.worker.w_first_name, db.worker.w_last_name,db.worker.w_nick_name,db.worker.w_email,db.status.s_code,db.worker.w_note)

#Define headers as tuples/dictionaries

headers = {'worker.w_id_w' :   'Id', 

'worker.w_first_name' :   'Ime',

  'worker.w_last_name' : 'Priimek',

  'worker.w_nick_name' : 'Vzdevek',

  'worker.w_email' : 'E-posta',

  'status.s_code': 'Status',

  'worker.w_note' : 'Komentar' }

#Let's specify a default sort order on date_of_birth column in grid

default_sort_order=[db.worker.w_last_name]

form = SQLFORM.smartgrid(db.worker,fields = fields,headers= headers,linked_tables=['status'])

[Anthony]

On Tuesday, July 15, 2014 6:58:28 AM UTC-4, Yebach wrote:

Hello

I am using SQLFORM smartgrid. My child table field is not shown on my form (status.s_code). Why?

Can you show your models? Does the child table have a field of type "reference" that refers to the parent table?
 

Also. If I (user) click(s) on edit, all fields are editable. how do I set which fileds are or can be editable and which one not.

You can set the readable/writable attributes of the fields at any point before generating the grid.
 

Is it possible to create a form that is click in editable (like excel?), so u don't have to open another window?

Not out of the box -- you would have to code that yourself.

Anthony

[Yebach]

models for two tables that need to be shown

db.define_table('worker',

Field('id', type ='id'),

Field('w_organisation', type ='integer'),

Field('w_user', type ='integer'),

Field('w_status', 'reference status'),

Field('w_first_name',type='text'),

Field('w_last_name',type='text'),

Field('w_nick_name',type='text'),

Field('w_email',type='text'),

Field('w_note',type='text'),

migrate=settings.migrate

)

db.define_table('status',

Field('id', type ='id'),

Field('s_code', type ='text'),

Field('s_description', type ='text'),

migrate=settings.migrate

)

[Yebach]

Also, how do I costumize the edit view (etc. drop down menus, filed size). The input text fields are enormous. Using set max length does not work. It prevents the text larger then set to be inserted (but only on submit button click it rases an error - is it possibel to create an on the fly validator for field ) but size of fileds is still too big. 

[Anthony]

workers = db(db.worker.w_organisation == 10).select(db.worker.w_id_w, db.worker.w_organisation, db.worker.w_first_name, db.worker.w_last_name,db.worker.w_nick_name,db.worker.w_email,db.worker.w_status,db.worker.w_note).as_list()

What is the above for? It is not used below.
 

#Define the query object. Here we are pulling all contacts having date of birth less than 18 Nov 1990

query = ((db.worker.w_organisation == 10) )#& (db.worker.w_status==db.status.s_id_s))

Why are you defining this query? Based on your model, looks like db.status.s_id_s should just be db.status.id. Also, not clear how this query selects based on birth date.
 

  fields = (db.worker.w_id_w,db.worker.w_first_name, db.worker.w_last_name,db.worker.w_nick_name,db.worker.w_email,db.status.s_code,db.worker.w_note)

Note, smartgrid is for browsing one table at a time (and linking to referencing tables), so you do not include fields from multiple tables in the same grid. According to the book:

A SQLFORM.smartgrid looks a lot like a grid, in fact it contains a grid but it is designed to take as input not a query but only one table and to browse said table and selected referencing tables.

 
If you want to include some extra columns, you can do so via the "links" argument, which can be a dict with table names as the keys so you can specify different links for each table.

form = SQLFORM.smartgrid(db.worker,fields = fields,headers= headers,linked_tables=['status'])

Note, db.status is not a child of db.worker -- it's the opposite -- there is a one-to-many relationship from db.status to db.worker. So, specifying linked_tables=['status'] will have no effect. On the other hand, if you do SQLFORM.smartgrid(db.status, linked_tables=['worker']), each row of the status grid will include a column with a "Workers" link to display a grid of the workers associated with a given status.

Anthony
 

[Vid Ogris]

Hell

I changed to SQLFORM.grid.

It kind of works now.

I even managed to include status as drop down in my edit view. 

I created a left join on my status table.

The problem I am facing now is that when a user selects a status for worker and clicks submit I get an error. looks like it is trying to insert a value for worker.w_status filed that does not exist in status table. How come? I mean it reads them from status table? What am I missing??

thank you

My pseudocode

query = ((db.worker.w_organisation == 10))

 

#Define the fields to show on grid. Note: (you need to specify id field in fields section in 1.99.2

fields = (db.worker.id,

db.worker.w_first_name,

db.worker.w_last_name,

db.status.s_code,

db.worker.w_nick_name,

db.worker.w_email,

db.worker.w_note)

#Define headers as tuples/dictionaries

# headers = {'worker.id' :   'Id', 

# 'worker.w_first_name' :   'Ime',

#   'worker.w_last_name' : 'Priimek',

#   'status.s_code' : 'Status',

#   'worker.w_nick_name' : 'Vzdevek',

#   'worker.w_email' : 'E-posta',

#   'worker.w_note' : 'Komentar' }

#

#Let's specify a default sort order on date_of_birth column in grid

default_sort_order=[db.worker.w_last_name]

db.worker.w_organisation.readable = False

db.worker.w_user.readable = False

db.worker.w_organisation.editable = False

db.worker.w_user.editable = False

#Nardiš polje bl text like :) WIU WIU

db.worker.w_first_name.widget = SQLFORM.widgets.string.widget

#Validators

db.worker.w_status.requires = IS_IN_DB(db,db.status.s_code) # This created drop down but does not insert the right value into field

db.worker.w_first_name.requires = [IS_NOT_EMPTY(error_message='Obvezen vnos imena')]

db.worker.w_email.requires = IS_EMAIL(error_message='Nepravilen e-mail')

form = SQLFORM.grid(query=query, 

left=db.status.on(db.worker.w_status == db.status.id),

fields=fields,  searchable=True, orderby=default_sort_order,create=True,

deletable=True, editable=True, paginate=25)

--
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 a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/6xckg0C7Nb4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Lep pozdrav 

Vid Ogris

[Anthony]

#Validators

db.worker.w_status.requires = IS_IN_DB(db,db.status.s_code) # This created drop down but does not insert the right value into field

Because w_status is a reference field, it must store the record ID from the db.status table, not the s_code value (which is a text field). If you want the dropdown to display the s_code values, you must use the "label" argument (which is the third argument):

IS_IN_DB(db, db.status.id, '%(s_code)s')

However, there is an easier way. In your model definitions, (1) define the status table before the worker table (usually a good idea to define referenced tables before the tables that reference them) and (2) in the status table definition, add format='%(s_code)s'. If you do that, you don't have to define a validator for the w_status field at all -- it will automatically get a default validator like the one specified above.

Anthony

[Vid Ogris]

Thanx. 

Works like a charm.

One more question though. How to change the links in table for view, Edit, delete. Now I have only hyperlinked text, while I would like to have buttons?

--

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 a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/6xckg0C7Nb4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.