jQuery UI Widgets › Forums › Grid › Grid filter date type with cell format: 'MM/dd/yyyy' as string

This topic contains 12 replies, has 4 voices, and was last updated by  Hristo 2 years, 9 months ago.

Viewing 13 posts - 1 through 13 (of 13 total)
  • Author
    Grid filter date type with cell format: 'MM/dd/yyyy' as string Posts
  • February 17, 2020 at 6:49 pm Grid filter date type with cell format: 'MM/dd/yyyy' as string #108129

    dan123
    Participant

    Hi i have this jqxgrid created with date column, i am trying to make the date column being able to filter as a string.

    For example:
    Order Date:
    Type in filter-> 11

    Show filtered results that starts and contains 11

    OR like

    Type in filter-> 11/11

    Show filtered results that starts and contains 11/11

    similarly like a string input.

    Here is my jsfiddle:
    https://jseditor.io/?key=835d76d651b511eaa55c00224d6bfcd5-ver-2

    February 24, 2020 at 11:07 am Grid filter date type with cell format: 'MM/dd/yyyy' as string #108191

    Hristo
    Participant

    Hello dan123,

    Could you clarify it?
    If you make it as a string then you will lose the benefits from this type of data.
    For this purpose, you should make this datafield as a string with beforeLoadComplete callback, for example, and also you could format the cell with the cellsrenderer callback.
    Another option is to use the custom filtering and show all the years with that date.

    Best Regards,
    Hristo Hristov

    jQWidgets team
    https://www.jqwidgets.com

    February 24, 2020 at 2:11 pm Grid filter date type with cell format: 'MM/dd/yyyy' as string #108197

    dan123
    Participant

    Hristo,

    So the way ‘date’ column is stored as this format ‘YYYY-MM-DD hh:mm:ss’ which is TIMESTAMP.

    I know that if on the grid logic when i set the ‘Order Date’ column to type ‘date’, then the filtering works as equals and not contains as a string.
    So i am trying to set the ‘Order Date’ to ‘string’ and trying to convert this value to ‘MM/dd/yyyy’ using cellsrenderer.

    But what i am not sure on is how to make that custom filtering work based on what we see on the display of the grid.
    So if in Type in filter: 11/11

    Show filtered results that starts and contains 11/11

    If you have a workaround that would be very much appreciated.

    I have updated my example:
    https://jseditor.io/?key=835d76d651b511eaa55c00224d6bfcd5-ver-4

    • This reply was modified 3 years, 1 month ago by  dan123.
    February 25, 2020 at 1:14 am Grid filter date type with cell format: 'MM/dd/yyyy' as string #108206

    Eking
    Participant

    Hey Dan123,
    You will need to pass this back to the php if you are using filters.
    var tcmdataAdapter = new $.jqx.dataAdapter(tcmsource,
    {formatData: function (data) {
    if (data.filterscount) {
    filterChanged = true;
    var filterParam = “”;
    //alert(data.filterscount);
    for (var i = 0; i < data.filterscount; i++) {
    // filter’s value.
    var filterValue = data[“filtervalue” + i];
    // filter’s condition. For the filterMode=”simple” it is “CONTAINS”.
    var filterCondition = data[“filtercondition” + i];
    // filter’s data field – the filter column’s datafield value.
    var filterDataField = data[“filterdatafield” + i];
    // “and” or “or” depending on the filter expressions. When the filterMode=”simple”, the value is “or”.
    var filterOperator = data[filterDataField + “operator”];
    var startIndex = 0;

    if (filterDataField == “date” ) {
    var sedate = new Date(filterValue);
    sedata = formatDate(sedate,’yyyy-MM-dd’);
    filterParam=sedata;
    data[“filtervalue” + i]=filterParam;

    }

    }

    return data;
    }

    }
    }

    Hope this helps….

    February 28, 2020 at 11:57 am Grid filter date type with cell format: 'MM/dd/yyyy' as string #108252

    Hristo
    Participant

    Hello dan123,

    In my previous post I mentioned how you could handle this scenario and my concerns on the Client-Side.
    Instead of trying to format the dates in the cellsrenderer callback you could do this in the beforeLoadComplete callback as you prefer.
    Please, take a look at this example:

    <!DOCTYPE html>
<html lang="en">
<head>
    <title id="Description">This example shows how to auto-resize the grid columns.</title>
    <meta name="description" content="JavaScript Grid Auto Resize Grid columns" />
    <link rel="stylesheet" href="../../jqwidgets/styles/jqx.base.css" type="text/css" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
    <meta name="viewport" content="width=device-width, initial-scale=1 maximum-scale=1 minimum-scale=1" />
    <script type="text/javascript" src="../../scripts/jquery-1.12.4.min.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxcore.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxdata.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxbuttons.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxscrollbar.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxlistbox.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxdropdownlist.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxmenu.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.columnsresize.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.selection.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.filter.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxpanel.js"></script>
    <script type="text/javascript" src="../../scripts/demos.js"></script>
    <script type="text/javascript">
        $(document).ready(function ()
        {
            var source = {
                datatype: "xml",
                datafields: [
                    { name: "ShippedDate", map: "m\\:properties>d\\:ShippedDate", type: "date" },
                    { name: "quantity", map: "m\\:properties>d\\:Freight", type: "float" },
                    { name: "lastname", map: "m\\:properties>d\\:ShipName", type: "string" },
                    { name: "productname", map: "m\\:properties>d\\:ShipAddress", type: "string" },
                    { name: "price", map: "m\\:properties>d\\:ShipCity", type: "string" },
                    { name: "total", map: "m\\:properties>d\\:ShipCountry", type: "string" },
                    { name: "orderdate", type: "string" }
                ],
                root: "entry",
                record: "content",
                id: "m\\:properties>d\\:OrderID",
                url: "../sampledata/orders.xml",

                //filter: function ()
                //{
                //    // update the grid and send a request to the server.
                //    $("#jqxgrid").jqxGrid("updatebounddata", "filter");
                //},
            };

            var dataAdapter = new $.jqx.dataAdapter(source, {
                beforeLoadComplete: function (records)
                {
                    records.map((item, index) =>
                    {
                        var day = item.ShippedDate.getDate();
                        var month = item.ShippedDate.getMonth();
                        month += 1;
                        var year = item.ShippedDate.getFullYear();
                        var result = (month < 10 ? "0" + month : month) + "/" + (day < 10 ? "0" + day : day) + "/" + year;

                        item.orderdate = result
                    });

                    return records;
                }
            });

            $("#jqxgrid").jqxGrid({
                theme: "energyblue",
                altrows: true,
                width: 500,
                source: dataAdapter,
                filterable: true,
                showfilterrow: true,
                columns: [{
                    text: "Order Date",
                    datafield: "orderdate",
                    width: 200,
                    //filtertype: "default",
                    filtercondition: "starts_with",
                }, {
                    text: "Last Name",
                    datafield: "lastname",
                    width: 100
                }, {
                    text: "Product",
                    datafield: "productname",
                    width: 180
                }, {
                    text: "Quantity",
                    datafield: "quantity",
                    width: 80,
                    cellsalign: "right"
                }, {
                    text: "Unit Price",
                    datafield: "price",
                    width: 90,
                    cellsalign: "right",
                    cellsformat: "c2"
                }, {
                    text: "Total",
                    datafield: "total",
                    width: 100,
                    cellsalign: "right",
                    cellsformat: "c2"
                }]
            });
        });
    </script>
</head>
<body>
    <div id="jqxgrid"></div>
</body>
</html>

    It is based on this demo (about the data source).

    Best Regards,
    Hristo Hristov

    jQWidgets team
    https://www.jqwidgets.com

    • This reply was modified 3 years ago by  Hristo.
    • This reply was modified 3 years ago by  Hristo.
    • This reply was modified 3 years ago by  Hristo.
    • This reply was modified 3 years ago by  Hristo.
    March 2, 2020 at 2:02 pm Grid filter date type with cell format: 'MM/dd/yyyy' as string #108272

    dan123
    Participant

    Thanks Hristo and Eking. that helped out

    June 4, 2020 at 1:15 pm Grid filter date type with cell format: 'MM/dd/yyyy' as string #112138

    Singh_kiatec
    Participant

    Hi Team ,

    I have the slightly similar issue on displaying date .

    I have the filter on grid and column is date. and cellsformat formats as expected. But when the date is null the filter shows date as 01-01-1970 or sometimes . Could you please suggest me if i am missing anything. using jqwidgets version 9.1.6.

    filtertype=”checkedlist”;
    cellsformat=”dd-MM-yyyy”;
    type=”date”;
    nullable = true;
    columntype=”datetimeinput”

    Thanks in advance.

    June 5, 2020 at 12:51 pm Grid filter date type with cell format: 'MM/dd/yyyy' as string #112144

    Hristo
    Participant

    Hello Singh_kiatec,

    You should set the nullable member as this:

    {
	text: "Shipped Date",
	datafield: "ShippedDate",
	width: 30,
	cellsformat: "D",
	nullable: true
}

    I tested this in the previous example and it seems to work fine.

    Best Regards,
    Hristo Hristov

    jQWidgets team
    https://www.jqwidgets.com

    June 10, 2020 at 7:47 am Grid filter date type with cell format: 'MM/dd/yyyy' as string #112175

    Singh_kiatec
    Participant

    Dear Hristo Hristov,

    Thanks for the quick response.
    That seems to work thanks:). but now the date in filter seems does not respect the cellsformat value.

    Lets say i have the cellsformat= “dd-MM-yyyy” and nullable:true has been set . Although the date in column seems to respect the cellsformat but not in the filter. Is is because some of the dates are null?

    Screen shot for reference:
    https://ibb.co/jHNMDmB

    Thanks in advance.
    Regards
    Singh_kiatec

    June 11, 2020 at 8:34 am Grid filter date type with cell format: 'MM/dd/yyyy' as string #112180

    Hristo
    Participant

    Hello Singh_kiatec,

    Could you clarify it?
    I tested this example and it seems to work fine.

    Best Regards,
    Hristo Hristov

    jQWidgets team
    https://www.jqwidgets.com

    June 12, 2020 at 7:45 am Grid filter date type with cell format: 'MM/dd/yyyy' as string #112202

    Singh_kiatec
    Participant

    Hi Hristo Hristov,

    Thanks for the. quick response. 🙂

    I see that we can still reproduce the bug in the shared example(http://jsfiddle.net/0k2sod54/)

    Step to reproduce.

    step1. Change the cellsformat to as cellsformat: “dd-MM-yyyy”. (Column format is changed that is correct and works as expected)
    Step 2: now go to filter of the date column . we see the format of the date is different(date is showed in localdate time format not “dd-MM-yyyy”).

    Thanks
    Regards
    Singh_kiatec

    June 17, 2020 at 5:30 am Grid filter date type with cell format: 'MM/dd/yyyy' as string #112253

    Singh_kiatec
    Participant

    Hi Hristo Hristov,

    Do we have any update on this.

    Thanks in advance. 🙂

    Regards
    Singh_kiatec

    June 17, 2020 at 9:01 am Grid filter date type with cell format: 'MM/dd/yyyy' as string #112258

    Hristo
    Participant

    Hello Singh_kiatec,

    Thank you for these details.
    Please, take a look at this example:
    http://jsfiddle.net/txhi/mca7sdn9/
    I hope this workaround will help.

    Best Regards,
    Hristo Hristov

    jQWidgets team
    https://www.jqwidgets.com

  • Author
    Posts
Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic.