Remote filter populated from database

[TonyLeech]

I'm switching away from hard-coding my filter options {options: ['Count', 'Add', 'Subtract']} to having them populated from the database columns.  It's generally working but I find that it is always missing one entry...for example in the case of 'Count', 'Add', 'Subtract' I'm only getting 'Add' and 'Subtract' populated and 'Count' is not in my filter list.  What do you think might be wrong?

Incidentally I have today purchased the Pro Deluxe version and copied all the new JS and CSS files onto my server.  The table seems to be working ok and the 'Eval' notice has gone...but do you think I could have a mix up somewhere between old and new versions?

Thanks in advance for your help.

[paramvir]

Please check the syntax of response of options from server. Is it the same as ['Count', 'Add', 'Subtract'] or is it different.

Please share your code and server response ( can be gathered from network tab of the browser developer tools ) if you are still stuck and want me to check it for you.

[TonyLeech]

Thanks for your reply.  The code and responses below use 'Count', 'Into', 'Out' instead of the original mentioned 'Add' and 'Subtract' but naturally my problem is still the same.

This is the response from my server - as taken from the browser development tool (Chrome function F12)...

<div class="pq-td-div">
 <select name="transtype" class="pq-grid-hd-search-field  ui-corner-all" style="">
  <option value="">--All--</option>
  <option>Count</option>
  <option>Out</option>
 </select>
</div>

and this is my code...

column definition in var colM...

   { title: "Action", width: 100, dataIndx: "transtype", align:"center", editable: false,
      filter: { type: "select",
         condition: 'equal',
         valueIndx: "transtype",
         labelIndx: "transtype",
         prepend: { '': '--All--' },
         listeners: ['change']
      }
   },

my table definition...

                  var table = { width: 1200, height: 'flex',
                     dataModel: dataModel,
                     sortModel: sortModel,
                     colModel: colM,
                     pageModel: pageModel,
                     scrollModel: {autoFit:true},
                     title: "Transactions",
                     flex: {on:true, all:false},
            //         flexHeight: true,
            //         flexWidth: true,
                     resizable: true,
                     numberCell: { show: false, resizable: true, title: "#" },
                     filterModel: { on: true, mode: "AND", header: true, type: 'remote' },
                     freezeCols: 3,
                     toolbar: {
                        cls: 'pq-toolbar-export',
                        items: [{
                              type: 'button',
                              label: "Export to Excel",
                              icon: 'ui-icon-document',
                              listeners: [{
                                 "click": function (evt) {
                                    $("#grid_php").pqGrid("exportExcel", { url: "exporttransactions.php", sheetName: "Transactions" });
                                 }
                              }]
                        }]
                     }
                  };

and my table/filter initialising...

                  var grid = pq.grid( "#grid_php", table);
                  grid.one("load", function (evt, ui) {
                     var column = grid.getColumn({ dataIndx: "transstatus" });
                     var filter = column.filter;
                     filter.cache = null;
                     filter.options = grid.getData({ dataIndx: ["transstatus"] });

                     var column = grid.getColumn({ dataIndx: "transtype" });
                     var filter = column.filter;
                     filter.cache = null;
                     filter.options = grid.getData({ dataIndx: ["transtype"] });

                  //and apply initial filtering.
                      grid.filter({
                          oper: 'add',
                          data: [
                              { dataIndx: 'transstatus', value: 'Active' },
                              { dataIndx: 'transtype', value: 'Count' }
                          ]
                      });           
                  });


Ideally I would like the transtype filter to work as a multiple select with 'range' rather than individual selection with 'equal', but I was having even less success making that work because the prepend selection resulted in zero rows rather than all rows retrieved.

I don't know if the above code is enough to go on, but I'll keep my fingers crossed that you can see something wrong with it.

Many thanks.

[paramvir]

The options for filter dropdown are not fetched directly from remote server but are picked from the data in grid by this line of code.

Code: [Select]

filter.options = grid.getData({ dataIndx: ["transtype"] }); //returns subset of data in grid.

You need to debug your code and check the return value of grid.getData({ dataIndx: ["transtype"] })

The similar demo for reference is http://paramquery.com/pro/demos/filter_header

[TonyLeech]

Oh, I see.  So when I'm doing remote filtering and remote paging then I won't get all the options available within the database columns, only the ones from my 10 row page would be visible...am I right?  And that means my filter options are limited to the data fetched in that first 10 row page and presumably then re-fetched based on my code then changing the filter to my programmed default.

Am I also right in thinking that your example works because it is not remote paging, so all your data is fetched and then filtered locally?  I've confused myself if that's correct because your code says...

filterModel: { on: true, mode: "AND", header: true, type: 'remote' }

...so if it only populates the header after it loads then how does your header keep all its options in 'ShipVia' once you've selected something like 'Speedy Express' from the dropdown?  Is it because your filters only only populated once, immediately after first load?

If my above analysis is correct then is there a way to populate an options list directly from the server rather than picking only from the list when remote paging has already restricted the list choices?

Many thanks.

[paramvir]

That's right. That demo is based on remote filtering but without paging. So when all the data is initially loaded, it helps to fill up the options in the filter select list. grid.one("load", ... )   ensures that it's called only once during initial load.

Of course you can populate options directly from the server and that is the right thing to do when you are using remote paging. The options can be fetched by a separate ajax call to the server and filter select options can be filled up in the ajax success callback.

In this example http://paramquery.com/pro/demos/edit_select the select list options are fetched directly from server. This example is for editor select list but it can also be adapted to filter select list, the procedure and API for which is quite similar.

[TonyLeech]

Sorry for the delayed acknowledgement - I've been away from my desk for a week.  Your recommendation and supplied example look exactly right and I look forward to replicating it with my code.  Many thanks.