Grid filtering in Javascript - Not Equal to null

[Jillian]

I am trying to do some filtering on a grid, and I want to filter where a field is not equal to null.  I have the following javascript:

var grid = $("#Grid").data("kendoGrid");
$filter = new Array();
if (contact == true) {
   $filter.push({ field: "ContactDate", operator: "neq", value: null });
grid.dataSource.filter($filter);

This doesn't work though. I get a 500 error: System.ArgumentException: Invalid property or field - 'null' for type: 'MyView'.  How do I filter on my date field not being null?

[Jillian]

I solved this by changing my date to be greater than a date, using a very old date from the past.

$filter.push({ field: "ContactDate", operator: "gt", value: new Date(1800, 1, 1) });

[Shawn]

I'd call that a workaround...not a solution.  I have a situation where I need to filter for null dates.  I'm getting the same " Invalid property or field - 'null' for type: " error.  I need to be able to filter those records where the date is null. How do I achieve that?

[Daniel]

Hello,

The server API does not support using null because the Grid filters do not support filtering on null value. This functionality was previously requested. You can vote for it and follow its progress in our user voice forum on this page. For now it is possible to filter on null when using server operations by setting null as string:

$filter.push({ field: "ContactDate", operator: "neq", value: "null" });

Regards,
Daniel
Telerik

[Mitch Thraves]

Hi guys,

Our clients are desperate for this functionality too which they previously had when using the DevExpress Grid. I don't think it's too unusual to want to filter for nulls or empty strings.

Many clients use this functionality to find out where data is missing, i.e.filter all clients where Gender is null. That way they can easily create a short list of clients with data missing that needs further investigation.

In a date range scenario where a client has an account open and closed date, it's sometimes useful to search for all clients whose account closed date is null, that way you have  a list of all clients with open accounts.

None of the above situations are catered for with the current Kendo UI grid filter.

A major and rather obvious omission to an otherwise brilliant grid control.

Hopefully this will be fixed soon.

Mitch

[Colin]

It's now 2015.  When are the new Filters going to be available and where can we vote on them?  I've done very advanced filtering by using a custom filtering control (which works independently of the grid, so in theory it could be used to filter any data source, not just specifically the grid).  We were also able to make it query child-data (using 'EXISTS' operator) when you wanted to query records from your data source that only had child data which matched certain conditions (ex. "Show me all Contacts who have Donated $100 or more in the last month" - a query which is absolutely impossible with Telerik's implementation).

It would be way to lengthy a post to show everything, but here are the Comparison Operators we use.

 The enum:

/// <summary>
/// Enumeration of comparison operators
/// </summary>
[Flags]
public enum CompareOperators
{
    #region Values

    /// <summary />
    Null = 0x0,
    /// <summary />
    NotNull = 0x1,                    // Combination of Not | Null (0x0 | 0x1)
    /// <summary />
    Exists = 0x2,
    /// <summary />
    NotExists = 0x3,                // Not | Exists (0x1 | 0x2)
    /// <summary />
    Equal = 0x4,
    /// <summary />
    NotEqual = 0x5,                    // Not | Equal (0x4 | 0x1)
    /// <summary />
    GreaterThan = 0x8,
    /// <summary />
    NotGreaterThan = 0x9,            // Not | GreaterThan (0x8 | 0x1)
    /// <summary />
    GreaterThanOrEqual = 0xC,        // GreaterThan | Equal (0x8 | 0x4)
    /// <summary />
    NotGreaterThanOrEqual = 0xD,    // Not | GreaterThan | Equal (0x8 | 0x4 | 0x1)
    /// <summary />
    LessThan = 0x10,
    /// <summary />
    NotLessThan = 0x11,                // Not | LessThan (0x10 | 0x1)
    /// <summary />
    LessThanOrEqual = 0x14,            // LessThan | Equal (0x10 | 0x4)
    /// <summary />
    NotLessThanOrEqual = 0x15,        // Not | LessThan | Equal (0x10 | 0x4 | 0x1)
    /// <summary />
    Between = 0x20,
    /// <summary />
    NotBetween = 0x21,                // Not | Between (0x20 | 0x1)
    /// <summary />
    In = 0x40,
    /// <summary />
    NotIn = 0x41,                    // Not | In (0x40 | 0x1)

    // NOTE: A 'Like' operator is too simplistic (or complicated depending on how you look at it)
    // So, it is broken down into it's component values: StartsWith, EndsWith, and Contains.

    /// <summary />
    StartsWith = 0x80,
    /// <summary />
    NotStartsWith = 0x81,            // Not | StartsWith (0x80 | 0x1)
    /// <summary />
    EndsWith = 0x100,
    /// <summary />
    NotEndsWith = 0x101,            // Not | EndsWith (0x100 | 0x1)
    /// <summary />
    Contains = 0x180,                // StartsWith | EndsWith (0x80 | 0x100)
    /// <summary />
    NotContains = 0x181,            // Not | StartsWith | EndsWith (0x80 | 0x100 | 0x1)
    /// <summary />
    Blank = 0x200,
    /// <summary />
    NotBlank = 0x201,

    // String Functions
    LengthEquals = 0x400,
    LengthNotEquals = 0x401,            // LengthEquals | NotNull (0x400 | 0x1)
    LengthLessThan = 0x800,
    LengthLessThanOrEqual = 0xC00,        // LengthEquals | LengthLessThan (0x400 | 0x800)
    LengthGreaterThan = 0x1000,
    LengthGreaterThanOrEqual = 0x1400,    // LengthEquals | LengthGreaterThan (0x400 | 0x1000)
    LengthBetween = 0x2000,

    #endregion
}

 

 

[Morten]

I've also run into this problem.

Pretty sad that a lot of "ofc they have this feature, how could they not" features are missing. And I don't have enough uservoice vote to vote on all the stuff I'm missing...

[Vladimir]

You can use the workaround with grid's filterMenuInit event:

filterMenuInit: function (e) {
    if(e.field == "exportedDate"){
        var filterButton = e.container.find("button:eq(0)");
        filterButton.click(function(){
            var firstInputValue = e.container.find("input:eq(0)").val();
            if(firstInputValue === ""){
                var filter  = e.sender.dataSource.filter();
                // remove the previous filter
                filter.filters = $.grep(filter.filters, function(f){
                    return f.field === "exportedDate";
                }, true);

                var firstValueDropDown = e.container.find("select:eq(0)").data("kendoDropDownList");
                filter.filters.push({ field: "exportedDate", operator: firstValueDropDown.value(), value: null });
                e.sender.dataSource.filter(filter);
                e.container.hide();
                return false;
            }
        });
    }
}