Filter a column in a grid based on two fields

[Mojtaba]

I want to filter my grids having both menu and row filter option. As it is a common scenario, some columns represent a "text-value" field; for example, the category column in Products grid. For this type of columns, I would like to have a "Kendo UI Multi Select" in the menu (which filters based on the value of category), and a simple string filter in the filter row (which basically applies string filtering on the name of category). I wrote the following code to accomplish this, but apparently the grid is got confused to filter based on category' value or category's name.

Here is the template:

<kendo-grid [data]="gridData"
            [pageSize]="state.take"
            [skip]="state.skip"
            [sort]="state.sort"
            [filter]="state.filter"
            filterable="menu, row"
            [sortable]="true"
            [pageable]="true"
            (dataStateChange)="dataStateChange($event)"
            [height]="400">
  <kendo-grid-column field="ProductName" title="Product Name">
  </kendo-grid-column>
  <kendo-grid-column field="Category.CategoryName" title="Category">
    <ng-template kendoGridFilterMenuTemplate
                 let-column="column"
                 let-filter="filter"
                 let-filterService="filterService">
      <kendo-multiselect [data]="categories"
                         textField="CategoryName"
                         valueField="CategoryID"
                         [valuePrimitive]="true"
                         [value]="categoryFilters(filter)"
                         (valueChange)="categoryChange($event, filterService)">
      </kendo-multiselect>
    </ng-template>
    <ng-template kendoGridCellTemplate let-dataItem>
      {{dataItem.Category?.CategoryName}}
    </ng-template>
  </kendo-grid-column>
</kendo-grid>

and here is part of the .ts file:

01.  public filterChange(filter: CompositeFilterDescriptor): void {
02.    this.state.filter = filter;
03.  }
04.
05.  public categoryChange(values: any[], filterService: FilterService): void {
06.    filterService.filter({
07.      filters: values.map(value => ({
08.        field: 'Category.CategoryID',
09.        operator: 'eq',
10.        value
11.      })),
12.      logic: 'or'
13.    });
14.  }
15.
16.  public categoryFilters(filter: CompositeFilterDescriptor): FilterDescriptor[] {
17.    return flatten(filter).map(({ value }) => value);
18.  }
19.
20. public dataStateChange(state: DataStateChangeEvent): void {
21.    this.state = state;
22.    this.gridData = process(sampleProducts, this.state);
23.  }
24.
25.
26.  public state: State = {
27.    skip: 0,
28.    take: 6,
29.  };
30.
31.public categories: any[] = distinct(sampleProducts);
32.  public gridData: GridDataResult = process(sampleProducts, this.state);
33.
34.
35.const flatten = filter => {
36.  const filters = (filter || {}).filters;
37.  if (filters) {
38.    return filters.reduce((acc, curr) => acc.concat(curr.filters ? flatten(curr) : [curr]), []);
39.  }
40.  return [];
41.};
42.
43.const distinct = data => data
44.  .map(x => x.Category)
45.  .filter((x, idx, xs) => xs.findIndex(y => y.CategoryName === x.CategoryName) === idx);

[Mojtaba]

A quick note; that "filterChange" function does nothing. I pasted it by mistake (seems Telerik forum does not have the edit option!).

[Dimiter Topalov]

Hi Mojtaba,

When both the menu and row filtering modes are enabled, both filtering UIs will be displayed. However, the default filtering UI for the string fields (like Category.CategoryName) for the filtering row is a regular textbox that holds a primitive string value only, whereas the MultiSelect component used as a custom UI in the filter menu, has an array value (either an array of primitives or an array of objects, depending on the configuration).

Thus when filtering via the MultiSelect the filter descriptors that will be passed to the Grid need to be adapted to reflect the desired result (for example, a set of filter descriptors with "equals" operators combined in a composite filter descriptor with "or" logic so that all Grid data items that correspond to either of the values, selected in the MultiSelect should stay).

However, the same filter structure cannot be synchronized when using the MultiSelect and the regular textbox to filter the same field, as in one scenario the filter value for this field is a string, and in the other - an array, and the filters themselves are different. This is why having different types of filters, involving different filter values and filtering logic, for the same Grid field is not supported.

Regards,
Dimiter Topalov
Progress Telerik

[Mojtaba]

Hi Dimiter,

Thanks for your reply.

[quote]
However, the same filter structure cannot be synchronized when using the MultiSelect and the regular textbox to filter the same field, as in one scenario the filter value for this field is a string, and in the other - an array, and the filters themselves are different.

[/quote]

Theoretically, it is possible to synchronise MultiSelect and the regular textbox to filter the same filed; e.g., I would like to see categories that are equal to 'Cat1' or equal to 'Cat2' (from MultiSelect) or contains 'ab' (from regular textbox). This is the desirable behaviour that I am looking for. 

If that scenario is not possible in Kendo UI, the other acceptable behaviour is that once the user goes with MultiSelect filter, the regular textbox filtering is cleared and when the user goes with the textbox, MultiSelect filter is cleared; i.e., only one of the filters can be applied at the same time.

Do you know a way to implement any of those two solutions above? I assume the second one might be easier.

Best regards,

Mojtab

[Dimiter Topalov]

Hi Mojtaba,

Thank you for explaining the desired behavior in further details. When the textbox and the MultiSelect do not need to represent the same filter conditions (which is not possible), you can modify the logic of the MultiSelect valueChange event handler to include all existing filters for the Category.CategoryName field (the Grid needs to be bound to a string field to enable textbox filtering with "contains" operator).

Filters should be modified in accordance with the desired result and behavior both in the regular filterChange event, and the valueChange event of the MultiSelect, something like:

https://stackblitz.com/edit/angular-f4osho-8qu1fb?file=app/app.component.ts

Alternatively, you can "disconnect" the MultiSelect in the FilterMenu template from the built-in Grid filtering functionality altogether, and apply some custom logic for filtering the Grid data when the value of the MultiSelect is changed. You can also intercept the incoming textbox value from the filter row in the filterChange event, and process the data based on both the MultiSelect and textbox values as you see fit, e.g.:

https://stackblitz.com/edit/angular-f4osho-ebr9ey?file=app/app.component.ts (choose Beverages and Condiments from the MultiSelect, type "me" in the textbox - all products from the Beverages, Condiments and Meat&Poultry categories are displayed in the Grid).

Regards,
Dimiter Topalov
Progress Telerik

[Mojtaba]

Hi Dimiter,

Thank you for the reply, in particular your examples; they opened up a new perspective for me, though they are a little bit buggy.

I used a hack to filter the column based on Category.CategoryName for the regular textbox, and based on Category.CategoryID for MultiSelect. Here is my code for the column:

<kendo-grid-column field="Category.CategoryName" title="Category Name">
      <ng-template kendoGridFilterMenuTemplate
                   let-column="column"
                   let-filter="filter"
                   let-filterService="filterService">
        <kendo-multiselect [data]="Categories"
                           textField="CategoryName"
                           valueField="CategoryValue"
                           [valuePrimitive]="true"
                           [(value)]="selectedValues"
                           (valueChange)="categoryChange($event, filterService,filter)">
        </kendo-multiselect>
      </ng-template>

      <ng-template kendoGridCellTemplate let-dataItem>
        {{dataItem.Category.CategoryName}}
         </ng-template>
    </kendo-grid-column>

And this is part of my .ts code:

private selectedValues: any[] = [];

public categoryChange(values: any[], filterService: FilterService, filter: CompositeFilterDescriptor): void {

    if (values.length != 0) {
      filterService.filter({//the condition below is always true for any string, I use it to fool the grid!
        filters: [{
          field: 'Category.CategoryName',
          operator: 'contains',
          value: '\u2063'//this is an invisible char
        }
        ],
        logic: 'and'
      });
    }
    else {
      filterService.filter(filter);
    }

  }

public dataStateChange(state: DataStateChangeEvent): void {
    let itemsAdded = 0;

    if (state.filter) {
      let allFilters: FilterDescriptor[] = flatten(state.filter);
      if (allFilters.some(f => f.field == "Category.CategoryName" && f.value == '\u2063')) {
        itemsAdded++;
        state.filter.filters.push({
          filters: this.componentTypeFilterBoxValues.map(value => ({
            field: 'Category.CategoryID',
            operator: 'eq',
            value
          })),
          logic: 'or'
        });
      }
      else {
        this.selectedValues= [];
      }
    }

    this.state = state;

    this.loadData();//this function loads the data with respect to 'this.state'

    for (var i = 0; i < itemsAdded; i++) {
      this.state.filter.filters.pop();
    }
  }

Best regards,

Mojtaba

[Svet]

Hi Mojtaba,

Indeed, the provided examples may not be fully functional as they are not completely tested, because they demonstrate the implementation of some custom logic. In general, we try to provide workarounds and custom implementations for requirements, that are not available as built in features. But this is not always possible or it requires effort, that falls out of the scope of the support service. 

I hope the provided examples pointed you in the right direction of achieving the desired custom functionality. 

Regards,
Svetlin
Progress Telerik

[ixen]

Hi

Do you have sample with tow DropdownList on two differents fields ?

 

Regards

 

[Yanmario]

Hi Ixen,

If the DropDownList component is used for filtering in the Grid, then please check the following article from our documentation about reusable custom filters:

https://www.telerik.com/kendo-angular-ui/components/grid/filtering/reusable-filter/

If instead the DropDownList component is desired in the cell of the Grid then an answer was provided in the following forum thread:

https://www.telerik.com/forums/search-grid-sample-with-two-or-three-dropdown-component

I hope this helps.

Regards,
Yanmario Menev
Progress Telerik