jQWidgets Forums

jQuery UI Widgets Forums Grid Add Filtering to Date Column – as automatic filtering no longer works

This topic contains 5 replies, has 2 voices, and was last updated by  pollyanna888 7 years, 9 months ago.

Viewing 6 posts - 1 through 6 (of 6 total)
  • Author

  • pollyanna888
    Participant

    Hi,
    I had to format my date column and as a result, the automatic filtering no longer works.

    Is there a way that I can get it to work again – with the minimum effort please?

    Do I have to create a custom filter as the only way round this? If so, do you have any examples of custom filters with Date Types?

    I really appreciate your help. Thank you.

    Regards,

    Polly


    Peter Stoev
    Keymaster

    Use a Date Filter for filtering Date columns. That way, it depends on the Date object, not on a String you enter.

    Best Regards,
    Peter Stoev

    jQWidgets Team
    https://www.jqwidgets.com/


    pollyanna888
    Participant

    Hi Peter,
    thank you for your reply. I probably did not explain myself clearly.

    I have used the cellrendering to render the date column.

    After rendering the automatic filtering for the date column no longer works.

    Below is an example of my code.

    I really appreciate your help.

    Thank you.

    Kind regards,

    Polly

    1/ The Grid Source – showing CreateTime as a date

    const source: jqwidgets.GridSource = {
    datatype: “json”,
    datafields: [

    { name: “CreateTime”, type: “date”, format: “yyyy-MM-dd” },

    ],
    id: “BlobId”,
    url: theUrl,
    data: {
    entityId: this.page.entityId,
    folderId: this.page.selectedFolderId,

    }
    };

    2/ The Grid Columns – showing CreateTime with cellrendering applied

    const columns: jqwidgets.GridColumn[] = [

    { text: ‘Date Created’, width: “33%”, datafield: ‘CreateTime’, filtertype: ‘range’, cellsformat: ‘yyyy-MM-dd HH:mm’, renderer: columnsrendererLeftPadding, cellsrenderer: cellrendererDateTimeOffset },

    ];

    3/ The Cellrender function for the grid column above

    var cellrendererDateTimeOffset = function (row, column, value) {

    var theOffset = parseInt(FilesAndFoldersGrid._offset);

    var newDate = new Date(value.getTime() + (theOffset * 60 * 1000));

    // format the new date
    var newYear = newDate.getFullYear();

    var newMonth = newDate.getMonth() + 1;
    var newMonthStr = displayTwoDigits(newMonth);

    var newDay = newDate.getDate();
    var newDayStr = displayTwoDigits(newDay);

    var newHours = newDate.getHours();
    var newHoursStr = displayTwoDigits(newHours);

    var newMinutes = newDate.getMinutes();
    var newMinutesStr = displayTwoDigits(newMinutes);

    const newDateString = newYear + “-” + newMonthStr + “-” + newDayStr + ” ” + newHoursStr + “:” + newMinutesStr;

    return ‘<div style=”margin-top: 5px; padding-left: 10px;” > ‘ + newDateString + ‘ </div>’;

    // return ‘<div style=”margin-top: 5px; padding-left: 10px;” > ‘ + newDate + ‘ </div>’;

    }; // end of cellrendererDateTimeOffset

    4/ The Grid settings – showing the automatic filtering set up

    const settings: jqwidgets.GridOptions = {
    source: this.createDataAdapter(),
    width: “100%”,
    height: 705,

    columns: columns,
    columnsresize: true,
    pagermode: “simple”,
    pagesize: 20,
    pageable: true,
    filterable: true,
    showfilterrow: true,
    sortable: true,
    sorttogglestates: “1”,

    };

    return jqwidgets.createInstance(grid.selector, “jqxGrid”, settings);


    Peter Stoev
    Keymaster

    Hi pollyanna888,

    Looking at the cellsrenderer implementation, I think that the date displayed may be is different than the cell’s value. That is probably the reason why you think the filtering is not working. However, filtering with the DateTimeInput filter will always filter by Date objects and these Date objects are the cell values, not the cell labels i.e in your Date column, with custom cellsrenderer, you can display “hello”, but it will still filter only by the cell value which is 2017-10-04 for example.

    Best Regards,
    Peter Stoev

    jQWidgets Team
    https://www.jqwidgets.com/


    pollyanna888
    Participant

    Hi Peter,

    thank you for that explanation. That makes sense.

    Basically what I am doing is converting the date obtained from the Cell Value (which happens to be UTC date) to the user’s local date and then converting that to a string for display purposes. Then when the user uses the automatic date filtering, the results are as you say not what we would expect, in fact, it shows no records at all.

    Please would you let me know what I must do to get the filtering for the date column to work so that it filters by the new string date displayed rather than the cell value.

    I appreciate your help.

    Thank you.

    Regards,

    Polly


    pollyanna888
    Participant

    Hi Peter,

    I have finally worked it out and I thought it would be nice to add my code here so that others could benefit.

    I have 2 scenarios, I formatted a number field and I formatted a date field using the cell renderer.

    Then when the automatic filtering took place, it was looking only at the user’s filter value (based on the formatted data) and comparing it with the real data cell value.

    I therefore had to intercept the filtering and add my logic.

    So here goes

    for the number field – called Size, these are my cell rendering functions –

    var cellrendererFileSize = function (row, column, value) {

    var newValue = displayFileSize(value);

    return ‘<div style=”margin-top: 5px; text-align: right; margin-top: 5px; padding-right: 12px;” > ‘ + newValue + ‘ </div>’;

    }; // end of cellrendererFileSize

    var displayFileSize = function (cellValue): string {

    // this function converts the cell Value being in bytes
    // to a string display that is either in Bytes, KB, or MB

    var intValue = parseInt(cellValue);
    var newValue = “”;
    var result;

    if (intValue < 1024) {
    newValue = intValue + ” Bytes”;
    } else if (intValue < 1024000) {
    result = (intValue / 1024.00).toFixed(2);

    newValue = result + “KB”;
    } else {

    result = (intValue / (1024.00 * 1024.00)).toFixed(2);
    newValue = result + “MB”;
    }

    return newValue;

    }; // end of displayFileSize

    where the columns are shown here –

    const columns: jqwidgets.GridColumn[] = [

    //{ text: “Blob “, datafield: “BlobId”, width: “20%” },
    { text: “Name”, datafield: “Name”, width: “40%”, renderer: columnsrendererLeftPadding, cellsrenderer: cellsrendererLeftPadding },
    { text: “Ext”, datafield: “Ext”, width: “5%”, renderer: columnsrendererLeftPadding, cellsrenderer: cellsrendererLeftPadding },
    {
    text: ‘Date Created (‘ + this.page.timeZoneAbbr + ‘)’, width: “16%”, datafield: ‘CreateTime’,
    filtertype: ‘range’,
    cellsformat: ‘yyyy-MM-dd HH:mm’,
    renderer: columnsrendererLeftPadding, // this renders the columns
    cellsrenderer: cellrendererDateTimeOffset // this renders the cell value
    },
    {
    text: “Size as Bytes”, datafield: “Size”,
    width: “12%”,
    renderer: columnsrendererRight, // this renders the columns
    cellsrenderer: cellrendererFileSize // this renders the cell value
    },

    I then created a filtering function, which is linked in the grid settings –

    //initialise
    const settings: jqwidgets.GridOptions = {
    source: this.createDataAdapter(),
    width: “100%”,
    height: 705,
    theme: “virsaenoheader”,
    autoshowloadelement: false,
    enabletooltips: true,
    columns: columns,
    columnsresize: true,
    pagermode: “simple”,
    pagesize: 20,
    pageable: true,
    groupable: true,
    groupsexpandedbydefault: true,
    filterable: true,
    showfilterrow: true,
    sortable: true,
    sorttogglestates: “1”,
    filter: filterfunction

    };

    return jqwidgets.createInstance(grid.selector, “jqxGrid”, settings);

    And here is the filter function

    The filter function has multiple sub filters. For the date we have 2 filters, the top date range and the bottom date range.
    For the size column we only have one sub filter – being the equality operator, therefore we just check whether the value exists.

    The CellValue is the real data in the cell, before we have applied any formatting etc. I have therefore applied the formatting to it so that it is identical to what the user sees in the grid.
    The FilterValue is the value that the user has typed into the filter box. I can then compare the FilterValue with the CellValue after I have transformed it to ‘DisplayValue’
    Filter the Size Column

    Below is the code for the Size data field

    var filterfunction = function (cellValue, rowData, dataField, filterGroup, defaultFilterResult)
    {
    debugger;

    if (dataField === “Size”) {

    // Change the cell value to the display value using the same function
    // as I used to change the display of the cell value in the grid
    var displayValue = displayFileSize(cellValue).toLowerCase();

    var sizeFilters= filterGroup.getfilters();

    for (var j = 0; j < sizeFilters.length; j++) {

    var theUsersFilterValue = sizeFilters[j].value.toLowerCase();

    // we need to see whether the display value contains the filter value
    var exists = displayValue.indexOf(theUsersFilterValue);

    if (exists > -1) {
    return true;
    }

    }
    return false;

    }

    return true;

    };

    Filter the Date Column

    This was trickier as I converted the user’s Filter value to UTC using function

    var filterUtCvalue = convertToUtcTime(filterValue);

    There were then 2 sub filters, was the date greater than the user’s filterValue and was the date less than the user’s filter value.

    IF any of these conditions was false, it would return false and not bother checking the next sub-filter.

    IF the value was true, I then checked ‘result’ and returned it. The ‘result’ could only ever be true, therefore I don’t even need to have it, I could just return true, because it would only get there if there had been no false values.

    See all the filter function code –

    var filterfunction = function (cellValue, rowData, dataField, filterGroup, defaultFilterResult)
    {
    debugger;

    // implements a custom filter for the “name” field.
    if (dataField === “CreateTime”) {

    debugger;

    var dateFilters = filterGroup.getfilters();

    // We must have true for ALL filters
    var result = false;

    for (var i = 0; i < dateFilters.length; i++) {

    var filter = dateFilters[i];
    var filterValue = dateFilters[i].value;
    var filterCondition = filter.condition;
    var filterType = filter.type;

    // Change the filter value to UTC like the cell value
    var filterUtCvalue = convertToUtcTime(filterValue);

    var dateValueOfCell = new Date(cellValue.getTime());

    if (filterCondition === “GREATER_THAN_OR_EQUAL”) {

    if (cellValue >= filterUtCvalue) {
    debugger;
    result = true;
    } else {

    return false;

    }

    }

    if (filterCondition === “LESS_THAN_OR_EQUAL”) {

    if (cellValue <= filterUtCvalue) {
    debugger;
    result = true;
    } else {
    return false;
    }

    }

    if (i === dateFilters.length – 1) {

    // Only return the entire result after all the filters have finished
    // IF the the filter condition is false for any value, it will return false
    // and never search the remaining filters
    // therefore in theory you don’t need the ‘result’ variable, just return true
    return result;

    }

    }
    return false;
    }

    return true;

    };

    I hope this helps someone else.

    Kind regards,

    Polly

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic.