SQLFORM grid search : Get ids of filtered records

[Madhavi]

Hi,

I am new to web2py and am working on application with a SQLFORM grid with the search feature along with selectable option. I added extra buttons to my grid to set a boolean value for selected records using selectable feature, and added another button to set a boolean value for all the records in the table. Now, I also want to add a button the set this value for only filtered records using the search feature. Is there a way using which I can refer to the ids of only those records which are filtered through the search widget to perform this manipulation only on those records? To explain better, I have the below buttons in place:

1. Set true for all selected records

2. Set true for all records

I also want to add an option to:

3. Set true for filtered records (filtered records refer to the ones displayed on performing the search in the grid).

Please let me know if this is possible,

Thanks a ton,

Madhavi

[Anthony]

Note, the "searchable" argument can be a custom callable -- so, you can create a custom function that first calls the default internal query builder to get the query that filters the records, then run the update using that query, and then simply return the query for use by the grid. Here is an example:

def index():
    grid_query = db.mytable.id > 0
    selectable=[('Set Selected True', selected_true_callback),
                ('Set All True', all_true_callback)]
    if 'keywords' in request.get_vars:
        selectable.append(('Set Filtered True', lambda r: None))

    def update_filtered(sfields, keywords):
        search_query = SQLFORM.build_query(sfields, keywords)
        if 'submit_2' in request.post_vars:
            db(grid_query & search_query).update(myboolean=True)
        return search_query

    grid = SQLFORM.grid(grid_query, selectable=selectable,
                        searchable=update_filtered)
    return dict(grid=grid)

In the above, "selectable" is a list of tuples defining two or three buttons corresponding to updating selected, all, or (optionally) filtered records. The grid will name each button "submit_0", "submit_1", and "submit_2", respectively. Whenever a search request comes in, the grid will call update_filtered (passing in the search fields and keywords). This function first simply calls the built-in query builder, just as the grid normally would, generating a DAL query for the search. The code determines if the "Set Filtered True" button was clicked by checking for "submit_2" in request.post_vars. In that case, it defines the filtered set of records by applying the main query for the grid in addition to the search query, and then updates that set of records (note, if your first argument to SQLFORM.grid is just a table rather than a query, you can skip the grid_query part of the code). Finally, it returns the search query for the grid to use as usual.

Note, in "selectable", the callback function for "Set Filtered True" is simply a do-nothing lambda function, as there won't actually be any selected records in this case -- we're just using "selectable" as a convenient way to add an additional submit button to the grid.

Anthony

[Anthony Smith]

Hi Anthony,

I thought I would try this, but getting the following error:
<type 'exceptions.NameError'>(global name 'selected_true_callback' is not defined)
It would be great if you could explain what I am doing wrong

thanks
Anthony


[Anthony]

On Monday, November 14, 2016 at 2:07:02 AM UTC-5, Anthony Smith wrote:

Hi Anthony,

I thought I would try this, but getting the following error:
<type 'exceptions.NameError'>(global name 'selected_true_callback' is not defined)
It would be great if you could explain what I am doing wrong


You said you already had buttons to handle the "all records" and "all selected records" cases, so I assume you already have callbacks to handle those cases. You did not show any code, so I just named those callbacks with placeholders -- simply fill those placeholders with your actual callbacks.

Anthony

[Madhavi]

Hi,

The code I am using is as below - I have 'Approve Selected Corporates', 'Reject Selected Corporates', 'Approve All Corporates' and 'Reject All Corporates' buttons working in the selectable list. I am changing corp_approved boolean column to True or False as per the button on which user clicks. I want to add 'Approve Filtered Corporates' and 'Reject Filtered Corporates' buttons to this which will change the status of only those records which are displayed on applying the search functionality. Please let me know how this can be done:

@auth.requires_login()

def ps_corpapproval():

    request_id = 10

    i = 1

    def approve_corp(ids):

        if not ids:

            response.flash = 'Please select a corporate to approve'

        else:

            for row in ids:

                db(db.ps_corporate_list.id == row).update(corp_approved = True)

        return ''

    def reject_corp(ids):

        if not ids:

                response.flash = 'Please select a corporate to reject'

        else:

            for row in ids:

                db(db.ps_corporate_list.id == row).update(corp_approved = False)

        return ''

    def approve_all(ids):

        db((db.ps_corporate_list.request_id == request_id) & (db.ps_corporate_list.merchant_id == i)).update(corp_approved = True)

        return ''

    def reject_all(ids):

        db((db.ps_corporate_list.request_id == request_id) & (db.ps_corporate_list.merchant_id == i)).update(corp_approved = False)

        return ''

    corp_form = SQLFORM.grid((db.ps_corporate_list.request_id==request_id)&(db.ps_corporate_list.merchant_id==i), deletable = False, create = False, editable = False, csv = False, paginate = False, selectable = [('Approve Selected Corporates', lambda ids: approve_corp(ids)),('Reject Selected Corporates', lambda ids: reject_corp(ids)), ('Approve All Corporates', lambda ids: approve_all(ids)), ('Reject All Corporates', lambda ids: reject_all(ids))], orderby = ~db.ps_corporate_list.spends, maxtextlength = 200)

    return dict(corp_form = corp_form)

Thanks,

Madhavi

[Anthony]

See the answer at https://groups.google.com/d/msg/web2py/i01tO7LQVYE/APWn-NPoCAAJ.

The only difference is you need to conditionally set the values to True or False depending on which button was clicked. Assuming the approve/reject filtered buttons are your 5th and 6th buttons, respectively, it would be something like:

        if 'submit_4' in request.post_vars or 'submit_5' in request.post_vars:
            myboolean = 'submit_4' in request.post_vars # True if "approved" was clicked, else False.
            db(grid_query & search_query).update(myboolean=myboolean)

Anthony