SQLform grid custom function for delete

[Yebach]

Hello

Is there a way to use SQLform.grid and add a function and new buttons for delete record and other purposos

I do not allow user to actually delete records just to change status in database, so the record still stays there.

Any suggestions?

[Anthony]

You can use the "links" argument to create additional buttons/links, either in separate columns or in the column that includes the view/edit/delete buttons.

Alternatively, you might consider setting up record versioning, either for just this table, or for the whole database. If you include an "is_active" boolean field in the table, the record versioning functionality will preserve all records and simply set is_active=False for deleted records (it will also add a common_filter to the table to filter out is_active=False records from all queries).

If you want the is_active/filtering functionality of record versioning but you don't actually want to save the older versions of records in an archive table, you can do this manually by (a) adding your own common_filter to filter out inactive records, and (b) adding a _before_delete callback that sets in_active=False and then abandons the delete operation.

The benefit if using the record versioning functionality (or manual alternative described above) is that it will apply to any delete operations on the table (done via the DAL), not just those coming from this particular grid interface.

Anthony

[Vid Ogris]

Thank you for fast reply

Will try with links. I have more then just active or inactive status, so i guess the other option is out. But will keep that in mind for next projects and/or tasks 

thank you again

--
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/3T1Qaf39wJg/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

[Vid Ogris]

I am trying with something like this

links = [lambda row: A('',_class='glyphicon glyphicon glyphicon-remove-sign',

_href =URL('settings','deactivate', 

vars=dict(s = '2')))]

But I cannot send vars to my controller function

I also tried with callback()

Any idea why?

I need to send id of the record and two static parametes e.g. 'tablename', 'tablefiled'

[Anthony]

What do you mean you cannot send vars? Exactly what is happening? Please show more code.

[Dave S]

On Thursday, September 17, 2015 at 3:55:48 AM UTC-7, Anthony wrote:

What do you mean you cannot send vars? Exactly what is happening? Please show more code.

The snippet above looks lik he's trying to set vars within the URL helper inside the lambda.  Is he trying to make a link that looks like this?

      http://mydomain/myapp/settings/deactivate/vars={(s,'2')}

(abusing dict notation slightly)

/dps

[Vid Ogris]

Something like this yes

This is my code

in my controler where i define my SQLFORM.grid

links = [lambda row: A('',_class='glyphicon glyphicon glyphicon-remove-sign',

_href =URL('settings','deactivate', 

vars=dict(s = '2')))]

--
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/3T1Qaf39wJg/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.

[Anthony]

Yes, but what problem do you observe? Are the URLs not getting generated properly? Is the value not available in request.get_vars? What happens when you click? Can you show the code of the action associated with that URL?

[Vid Ogris]

Hello

So I managed to put link and create it but now I have a probelm getting the id of the record I want to deal with

links = [lambda row: A('',_class='glyphicon glyphicon glyphicon-remove-sign',

callback=URL('settings','deactivate',vars=dict(table='skills',field = 'sk_status', value = form.vars.id )))]

how do i get sqlform grid record to send it to my function???

Anthony this is my function

I have to change field status in a table. We have more than one status (active, pending, deleted archived) so I couldnt use the web2py default funciton as true or false

since we name our filed status based on table i have to send table name and table field that is why this kind of function because i use it in different tables

@auth.requires_login() def deactivate(): ##Aktiviramo delavca ko user klikne na activate user = auth.user_id org = db(db.auth_user.id == user).select(db.auth_user.organization)[0]["organization"] #Worker je id zaposlenenga ki ga damo v aktiven id = request.vars["id"] table = request.vars["table"] field = request.vars["field"] print request print id query = db(db[table].id == id).update(**{field: 1}) return locals()

Thank you

2015-09-18 18:41 GMT+08:00 Anthony <abas...@gmail.com>:

Yes, but what problem do you observe? Are the URLs not getting generated properly? Is the value not available in request.get_vars? What happens when you click? Can you show the code of the action associated with that URL?

--
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/3T1Qaf39wJg/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.

[Anthony]

On Saturday, September 19, 2015 at 4:18:49 AM UTC-4, Yebach wrote:

Hello

So I managed to put link and create it but now I have a probelm getting the id of the record I want to deal with

links = [lambda row: A('',_class='glyphicon glyphicon glyphicon-remove-sign',

callback=URL('settings','deactivate',vars=dict(table='skills',field = 'sk_status', value = form.vars.id )))]

If you want the id of the record in a given row of the table, then instead of value=form.vars.id, it should be value=row.id.
 

@auth.requires_login() def deactivate(): ##Aktiviramo delavca ko user klikne na activate user = auth.user_id org = db(db.auth_user.id == user).select(db.auth_user.organization)[0]["organization"] #Worker je id zaposlenenga ki ga damo v aktiven id = request.vars["id"]

In the URLs generated in the grid, you used the variable name "value", yet here you are looking for the variable name "id". The above line should be:

    id = request.vars.value

Or you can change the URLs to use "id" as the variable name.

Anthony

[Vid Ogris]

Ok thanx. This worked.

One more question. My SQLFORM.grid is not refreshed after callback. So record stays there. HOw to do a grid refresh?

--

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/3T1Qaf39wJg/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.

[A3]

explained here: 

https://groups.google.com/d/msg/web2py/asCPsD9UGb8/ksmYX0UjBx0J

Op dinsdag 22 september 2015 04:22:39 UTC+2 schreef Yebach:

[Vid Ogris]

why am I  getting an  error AttributeError: 'Row' object has no attribute 'id'

when I try to set links = [lambda row: A('',_class='glyphicon glyphicon glyphicon-remove-sign',

callback=URL('settings','deactivate',vars=dict(table='workers_skills',field = 'ws_status'

,value = row.id )))], 

 

[Niphlod]

is your "id" in the grid ? id it's not in the fields then it's not accessible....

[Vid Ogris]

The thing is IF I put links = [lambda row: A('',_class='glyphicon glyphicon glyphicon-remove-sign',

callback=URL('settings','deactivate',vars=dict(table='workers_skills',field = 'ws_status'

,value = row.workers_skills.id )))]

Then my function for "deleting" record works but when I want to go to edit I get an error. If I put row.id then the oposite

[Vid Ogris]

My whole function 

@auth.requires_login()

def workers_skills():

user = auth.user_id

org = db(db.auth_user.id == user).select(db.auth_user.organization)[0]["organization"]

db.workers_skills.ws_organisation.default = org

query=((db.workers_skills.ws_organisation == org) & (db.workers_skills.ws_status == 1))

# select(db.workers_skills.ws_priority,

# db.workers_skills.ws_worker,

# db.workers.w_first_name,

# db.workers.w_last_name,

# db.skills.sk_name,

query_inactive = db((db.workers_skills.ws_organisation == org) & (db.workers_skills.ws_status == 100))\

.select(

db.workers_skills.id, db.workers.w_nick_name, db.skills.sk_name, db.workers_skills.ws_status, db.workers_skills.ws_priority,

join=[db.workers.on(db.workers.id==db.workers_skills.ws_skill),

db.skills.on(db.skills.id==db.workers_skills.ws_skill)],

orderby=db.workers_skills.ws_worker).as_list()

fields = (db.workers_skills.id,

db.workers.w_first_name,

db.workers.w_last_name,

db.skills.sk_name,

db.workers_skills.ws_priority

)

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

default_sort_order=[db.workers.w_last_name]

db.workers_skills.ws_organisation.readable = db.workers_skills.ws_organisation.writable = False

db.workers_skills.ws_worker.requires= IS_IN_DB(db(db.workers.w_organisation == org),db.workers.id,'%(w_last_name)s %(w_first_name)s',zero=T('Select worker'))

db.workers_skills.ws_skill.requires= IS_IN_DB(db(db.skills.sk_organisation == org),db.skills.id,'%(sk_name)s',zero=T('Select skill'))

#Nardiš polje bl text like :) WIU WIU

db.workers_skills.ws_priority.widget = SQLFORM.widgets.integer.widget

#VAlidatorji

#db.workers.w_status.requires = IS_IN_DB(db,db.status.s_code) #tega sm rešu v db.py

# db.workers_skills.ws_worker.requires = [IS_NOT_EMPTY(error_message=T('Missing worker name'))]

# db.workers_skills.ws_skill.requires = [IS_NOT_EMPTY(error_message=T('Missing skill name'))]

db.workers_skills.ws_priority.requires = [IS_NOT_EMPTY(error_message=T('Missing priority level'))]

links = [lambda row: A('',_class='glyphicon glyphicon glyphicon-remove-sign',

callback=URL('settings','deactivate',vars=dict(table='workers_skills',field = 'ws_status'

,value = row.workers_skills.id )))]

grid_workersskills= SQLFORM.grid(query=query,left=[db.workers.on(db.workers_skills.ws_worker==db.workers.id),

      db.skills.on(db.workers_skills.ws_skill==db.skills.id)], 

fields=fields,  searchable=False, orderby=[db.workers.w_nick_name],create=True,

deletable=False, editable=True, paginate=50, buttons_placement = 'right',

showbuttontext = False,

links = links, 

#oncreate=myfunction,

ui = dict(widget='',

         header='',

         content='',

         default='',

         cornerall='',

         cornertop='',

         cornerbottom='',

         button='button btn btn-default',

         buttontext='buttontext button',

         buttonadd='icon plus icon-plus glyphicon glyphicon-plus',

         buttonback='icon leftarrow icon-arrow-left glyphicon glyphicon-arrow-left',

         buttonexport='icon downarrow icon-download glyphicon glyphicon-download',

         buttondelete='icon trash icon-trash glyphicon glyphicon-trash',

         buttonedit='icon pen icon-pencil glyphicon glyphicon-pencil',

         buttontable='icon rightarrow icon-arrow-right glyphicon glyphicon-arrow-right',

         buttonview='icon magnifier icon-zoom-in glyphicon glyphicon-eye-open',

         ),

exportclasses  = dict(csv_with_hidden_cols=False, html = False, tsv = False, tsv_with_hidden_cols=False, json = False))

return dict(grid_workersskills=grid_workersskills, query_inactive=query_inactive)

[Anthony]

On Friday, October 30, 2015 at 11:03:18 PM UTC-4, Yebach wrote:

The thing is IF I put links = [lambda row: A('',_class='glyphicon glyphicon glyphicon-remove-sign',

callback=URL('settings','deactivate',vars=dict(table='workers_skills',field = 'ws_status'

,value = row.workers_skills.id )))]

Then my function for "deleting" record works but when I want to go to edit I get an error. If I put row.id then the oposite

I'm not sure I follow. What do you mean by "go to edit"? Assuming edit is a separate action/page, how could the callback URL you are using for delete affect the edit action? Can you show the code related to editing?

Anthony

[Vid Ogris]

I dont have special function for edit. When I click the icon on to edit the record I get this error. I did not wrote any special function. I am using web2py's 

--

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/3T1Qaf39wJg/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.

[Anthony]

I see the problem. When the grid involves a join, you must refer to fields within a row via the row.table.field format. However, when you view/edit a record from such a grid, only the record from a single table is shown (i.e., there is no longer a join involved), so you must now refer to fields within a row via the row.field format. Because your "link" appears in the grid as well as in the view/edit page, whichever format you use to refer to the id field, it will result in an error in one of those two contexts.

So, all you need to do is ensure your code works in both contexts. Here's a simple trick:

Instead of:

row.workers_skills.id

do:

row.get('workers_skills', row).id

The .get() method attempts to retrieves the 'workers_skills' key, but if it doesn't exist, it instead returns a default value, which in this case is just the original row. It then retrieves the "id" field of that returned object (either the original row or the row.workers_skills sub-row).

This is a tricky problem to fix in a general way. The grid could automatically set the "compact" attribute of the Rows object to False whenever the grid involves a join, even when calling the view/edit actions, which would enable a consistent interface for accessing field values. However, that would then cause problems with widgets and represent attributes defined on the database fields, which would be expecting row.field syntax.

In fact, there is a more general problem here -- any field represent attribute that accesses other fields in the row (via row.field syntax) will fail if the row is part of a join (which requires row.table.field syntax).

Anthony

On Sunday, November 1, 2015 at 1:08:04 AM UTC-4, Yebach wrote:

I dont have special function for edit. When I click the icon on to edit the record I get this error. I did not wrote any special function. I am using web2py's 

2015-11-01 11:38 GMT+08:00 Anthony:

On Friday, October 30, 2015 at 11:03:18 PM UTC-4, Yebach wrote:

The thing is IF I put links = [lambda row: A('',_class='glyphicon glyphicon glyphicon-remove-sign',

callback=URL('settings','deactivate',vars=dict(table='workers_skills',field = 'ws_status'

,value = row.workers_skills.id )))]

Then my function for "deleting" record works but when I want to go to edit I get an error. If I put row.id then the oposite

I'm not sure I follow. What do you mean by "go to edit"? Assuming edit is a separate action/page, how could the callback URL you are using for delete affect the edit action? Can you show the code related to editing?

Anthony

--
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/3T1Qaf39wJg/unsubscribe.

To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Vid Ogris]

Thank you so much. This works like a charm. 

2015-11-01 14:24 GMT+08:00 Anthony <abas...@gmail.com>:

I see the problem. When the grid involves a join, you must refer to fields within a row via the row.table.field format. However, when you view/edit a record from such a grid, only the record from a single table is shown (i.e., there is no longer a join involved), so you must now refer to fields within a row via the row.field format. Because your "link" appears in the grid as well as in the view/edit page, whichever format you use to refer to the id field, it will result in an error in one of those two contexts.

So, all you need to do is ensure your code works in both contexts. Here's a simple trick:

Instead of:

row.workers_skills.id

do:

row.get('workers_skills', row).id

The .get() method retrieves the 'workers_skills' key, but if it doesn't exist, it instead returns a default value, which in this case is just the original row. We then retrieve the "id" field of that returned object (either the original row or the row.workers_skills sub-row).

This is a tricky problem to fix in a general way. The grid could automatically set the "compact" attribute of the Rows object to False whenever the grid involves a join, even when calling the view/edit actions, which would enable a consistent interface for accessing field values. However, that would then cause problems with widgets and represent attributes defined on the database fields, which would be expecting row.field syntax.

In fact, there is a more general problem here -- any field represent attribute that accesses other fields in the row will fail if the row is part of a join.

Anthony

On Sunday, November 1, 2015 at 1:08:04 AM UTC-4, Yebach wrote:

I dont have special function for edit. When I click the icon on to edit the record I get this error. I did not wrote any special function. I am using web2py's 

2015-11-01 11:38 GMT+08:00 Anthony:

On Friday, October 30, 2015 at 11:03:18 PM UTC-4, Yebach wrote:

The thing is IF I put links = [lambda row: A('',_class='glyphicon glyphicon glyphicon-remove-sign',

callback=URL('settings','deactivate',vars=dict(table='workers_skills',field = 'ws_status'

,value = row.workers_skills.id )))]

Then my function for "deleting" record works but when I want to go to edit I get an error. If I put row.id then the oposite

I'm not sure I follow. What do you mean by "go to edit"? Assuming edit is a separate action/page, how could the callback URL you are using for delete affect the edit action? Can you show the code related to editing?

Anthony

--
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/3T1Qaf39wJg/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

--
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/3T1Qaf39wJg/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.