Remote header filter using values from another column

[brwncald]

Hi,

I am using remote header filters.
I would like to have the filter selection list display labels from its own column, but send values to the server to filter by, from another column.

So for example, in the request to the server, the pq_filter parameter currently sends:
{"mode":"AND","data":[{"dataIndx":"LocationName","dataType":"string","value":["Buena Vista, CO (V)"],"condition":"range"}]}
"Buena Vista, CO (V)" is the displayed value in that column.
I would like to send a value from a different (ideally hidden) column on the same row instead.

Cheers,
Geoff

[brwncald]

Hi,
Perhaps it might help if I were to explain a little further:

Imagine a column that displays job titles, e.g. "Developer", "Analyst", "Database Administrator".
Associated with each of those values is a job code, e.g. "050", "055", "060".
The job titles and job codes are both columns in the dataModel.
I want the header filter to display the job title, but when a value is selected I want the pq_filter parameter to contain the job code.
Is this possible?

Cheers,
Geoff

[brwncald]

Hi,

Having read through the upgrade notes, I think I understand what is going on here now.
The ability to filter by a value as opposed to the displayed label went away in version 5.2.0.

Versions prior to 5.2.0. allowed the following filter object to work:

   filter: {
      type: 'select',
      condition: 'equal',
      valueIndx: "LocationCode",
      labelIndx: "LocationName",
      listeners: ['change']
}

Thus the value sent to the controller specified in the pq_filter parameter would have been the one in the LocationCode column.
Whilst the newer versions have many more features, filtering by valueIndx instead of labelIndx seem no longer to be possible.

Is this true?
If so, is there a work-around?
Is there a plan to reinstate this ability?

Cheers,
Geoff

[paramvir]

Geoff

May I know about the column setup. I would be great if you can share the complete colModel.

[paramvir]

Geoff
 
Please use column.filter.options for your requirement: https://paramquery.com/pro/api#option-column-filter

Code: [Select]

filter: {
crules: [{condition: 'range' }],
        //options can also be callback.
options: [
{LocationCode1: LocationVal1},
{LocationCode2: LocationVal2},
{LocationCode3: LocationVal3}
                 ...
]
}

Please let me know if you need further assistance.

[brwncald]

Hi,

Here is a complete example.
Since both the data and the filter options are loaded from a remote source I can't easily create a JSFiddle. The code is at the bottom of this post.

The first two columns work fine. This is because the values and labels within the options returned by the call to buildFilterDropDown(ui, "/Home/GetBusinessUnits"); are the same:

[
  {"BusinessUnit":"","BusinessUnitName":" Select a business unit"},
  {"BusinessUnit":"Business Unit 1","BusinessUnitName":"Business Unit 1"},
  {"BusinessUnit":"Business Unit 2","BusinessUnitName":"Business Unit 2"},
  {"BusinessUnit":"Business Unit 3","BusinessUnitName":"Business Unit 3"}
]
Upon clicking the checkbox in the filter grid, the client sends the following to the server to get the filtered data:
https://localhost:99999/Home/spGetUserList?pq_datatype=JSON&pq_filter={"mode":"AND","data":[{"dataIndx":"LocationName","dataType":"string","value":["Box Elder, SD"],"condition":"range"},{"dataIndx":"BusinessUnit","dataType":"string","value":["Business Unit 1"],"condition":"range"}]}&_=1650319707694

The data is correctly filtered down to those records having BusinessUnit = "Business Unit 1"

The problem is with column 3.
The values and labels within the options returned by the call to buildFilterDropDown(ui, "/Home/GetLocations"); are different (as they would be for any traditional name value pair like state name/state code; airport name/airport code etc.):

[
 {"LocationCode":"","LocationName":" Select a location"},
 {"LocationCode":"10","LocationName":"Alexandria, VA"},
 {"LocationCode":"11","LocationName":"Atlanta, GA"},
 {"LocationCode":"12","LocationName":"Beltsville, MD"},
 {"LocationCode":"13","LocationName":"Boise, ID"},
 {"LocationCode":"14","LocationName":"Boston, MA"},
 {"LocationCode":"15","LocationName":"Box Elder, SD"},
 {"LocationCode":"16","LocationName":"Buena Vista, CO"},
 {"LocationCode":"17","LocationName":"Cape Elizabeth, ME"},
 {"LocationCode":"18","LocationName":"Carlsbad, CA"}
 ]

Upon clicking the checkbox in the filter grid, the client sends the following to the server to get the filtered data:
https://localhost:44357/Home/spGetUserList?pq_datatype=JSON&pq_filter={"mode":"AND","data":[{"dataIndx":"LocationName","dataType":"string","value":["Box Elder, SD"],"condition":"range"}]}&_=1650323597648

Our server expects the LocationCode value to be supplied instead of LocationName, but there seems to be no way to achieve this in the current version. People remember names not codes and we don't have space to include both columns. We have many such columns in the grid we use.
As I said in my previous reply, this used to be possible prior to version 5.2.0.

Cheers,
Geoff

Code: [Select]


$(function () {

    //define the grid.
    var oColModel = [
        {
            title: "Business Unit",
            dataIndx: "BusinessUnit",
            editable: false,
            width: 70,
            filter: {
                type: 'select',
                crules: [{ condition: 'range' }],
                selectGridObj: function (ui) {
                    buildFilterDropDown(ui, "/Home/GetBusinessUnits");
                },
                maxCheck: 1
            }
        },
        {
            title: "Sector",
            dataIndx: "Sector",
            editable: false,
            width: 80,
            filter: {
                type: 'select',
                crules: [{ condition: 'range' }],
                selectGridObj: function (ui) {
                    buildFilterDropDown(ui, "/Home/GetSectors");
                },
                maxCheck: 1
            }
        },
        {
            title: "Location", dataIndx: "LocationName", editable: false, width: 70, align: "center",
            filter: {
                type: 'select',
                crules: [{ condition: 'range' }],
                selectGridObj: function (ui) {
                    buildFilterDropDown(ui, "/Home/GetLocations");
                },
                maxCheck: 1
            }
        },
    ];

    //Set up ParamQuery data model
    var oDataModel = {
        dataType: "JSON",
        location: "remote",
        recIndx: "EmpCode",
        method: "GET",
        url: "/Home/spGetUserList",
        getData: function (response) {
            return { data: response };
        }
    }

    var obj = {
        height: 'flex',
        rowHt: 30,
        wrap: false,
        hwrap: false,
        editable:false,
        columnBorders: false,
        scrollModel: { autoFit: true },
        title: "<b>Header Filters</b>",
        colModel: oColModel,
        filterModel: {
            on: true,
            mode: "AND",
            header: true,
            type: "remote"
        },
        dataModel: oDataModel
    };
    var grid = pq.grid("#grid_array", obj);

    function buildFilterDropDown(ui, strControllerUrl) {
        //Gets the filter options from the supplied controller URL

        ui.obj.dataModel = {
            location: 'remote',
            url: strControllerUrl,
            getData: function (response) {
                var val = ui.column.filter.crules[0].value || [],
                    di = ui.column.dataIndx,
                    //data = response.data;
                    data = response;
                data.forEach(function (rd) {
                    if (val.indexOf(rd[di]) >= 0) {
                        rd.selected = true;
                    }
                })
                return { data: data };
            }
        }



    }
});


[paramvir]

Thanks for the details.

It can be solved by exchanging the response values ( LocationCode with text values and LocationName with number values ) from remote script and adding column.renderLabel to display text values in filter dropdown.

[brwncald]

Thanks. I will try this.
Cheers,
Geoff