1. Home
  2. Forum
  3. ASP.NET MVC
  4. Sort and put null values of date column at the end

Sort and put null values of date column at the end

I have a dynamic grid. The one column is of date type which has some null values also.

When  sorted by DESC order the rows with null values come on top and rest rows are sorted properly.
And when data is exported to excel, it always comes in the bottom as expected. (But not grid.)

Can I sort in grid such a way that , always null values will be at the end even though I sort ASC/DESC order?

DESC:
28-12-2018 Normal 9
28-12-2018 Summe 9
17-12-2018 Normal 4
17-12-2018 Summe 4
  Summe all Tage 246



ASC:

17-12-2018 Normal 4
17-12-2018 Summe 4
28-12-2018 Normal 9
28-12-2018 Summe 9
  Summe all Tage 246



Please help me out with this.

7 Replies
MS Madhu Sudhanan P Syncfusion Team February 14, 2019 12:20 PM UTC

Hi Vaibhav, 

Thanks for contacting Syncfusion support. 

From your query we found that you want to display the null date values always at bottom of the Grid. We suggest to customize the Grid sorting action by using SortComparer method of the Grid. Please refer the following code snippet to achieve this requirement, 

@(Html.EJS().Grid<EJ2Grid.Controllers.HomeController.Orders>("Grid").DataSource((IEnumerable<object>)ViewBag.data). AllowPaging(true).Columns(col => 
{ 
   col.Field("OrderDate").HeaderText("Order Date").SortComparer("sortComparer").Format("yMd").Width("120").Add(); 
}).AllowSorting(true).ActionBegin("actionBegin").AllowExcelExport(true).Toolbar(new List<string>() { "ExcelExport" }).Render()) 
 
<script type="text/javascript"> 
    var action; 
 
    function actionBegin(args) { 
        if (args.requestType == "sorting") { 
            action = args.direction; 
        } 
    } 
 
    function sortComparer(reference, comparer) { 
        var sortAsc = action === "Ascending" ? true : false; 
        if (sortAsc && reference === null) { 
            return 1; 
        } 
        else if (sortAsc && comparer === null) { 
            return -1; 
        } 
        else if (!sortAsc && reference === null) { 
            return -1; 
        } 
        else if (!sortAsc && comparer === null) { 
            return 1; 
        } else { 
            return reference - comparer; 
        } 
    } 
</script> 

Also, we have prepared the sample with this requirement and that can be download from the below link, 


Refer the below link to know about sortComparer method of the Grid, 


If we misunderstood your query please provide more details about your requirement for further assistance. 

Regards, 
Madhu Sudhanan P 


VM Vaibhav More February 15, 2019 11:14 AM UTC

Thanks for your reply.
I am using lower version of EJ grid. (Html.EJ())
So, I am not able to find the SortComparer method.

Is there alternative for it?

VN Vignesh Natarajan Syncfusion Team February 19, 2019 09:36 AM UTC

Hi Vaibhav, 
 
Thanks for the update. 
 
We have achieved your requirement by overriding the default functionality of the ascending and descending functions of the datamanaer.  
 
Refer the below code example 
 
 
 
<script type="text/javascript"> 
    ej.pvt.fnAscending = function (x, y) { 
        if (ej.isNullOrUndefined(y) && ej.isNullOrUndefined(x)) 
            return -1; 
 
        if (y === null || y === undefined) 
            return -1; 
 
        if (typeof x === "string") 
            return x.localeCompare(y); 
 
        if (x === null || x === undefined) 
            return 1; 
 
        return x - y; 
    } 
 
        ej.pvt.fnDescending = function (x, y) { 
            if (ej.isNullOrUndefined(y) && ej.isNullOrUndefined(x)) 
                return -1; 
 
            if (y === null || y === undefined) 
                return -1; 
 
            if (typeof x === "string") 
                return x.localeCompare(y) * -1; 
 
            if (x === null || x === undefined) 
                return 1; 
 
            return y - x; 
        } 
 
</script> 
 
@(Html.EJ().Grid<OrdersView>("ForeignKey") 
            .Datasource((IEnumerable<object>)ViewBag.datasource) 
            .AllowPaging() 
            .AllowSorting() 
            .Columns(col => 
            { 
                col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).TextAlign(TextAlign.Right).Width(90).Add(); 
                col.Field("CustomerID").HeaderText("Customer ID").Width(80).Add(); 
                col.Field("EmployeeID").HeaderText("Employee Name").Width(90).ValidationRules(v => v.AddRule("Compare", true)).Add(); 
                 
                      ------- 
 
           }) 
) 
 
We have prepared a sample for your reference. Please refer the below link for the sample. 
 
 
Please get back to us if you have further queries. 
 
Regards, 
Vignesh Natarajan 
 


VM Vaibhav More February 20, 2019 06:24 AM UTC

Thanks for suggestion but I couldn't try it because,

I am adding the columns dynamically in controller.
I want to put the rules in below format.
Please can you suggest way to overcome this.

new Column() { Field = "Eventdatum", HeaderText = "Eventdatum",Format ="{0:dd/MM/yyyy}",
                               TextAlign =Syncfusion.JavaScript.TextAlign.Left,Type="date", AllowSorting=true,
                               ValidationRules=.........................
                           },

VN Vignesh Natarajan Syncfusion Team February 21, 2019 03:49 AM UTC

Hi Vaibhav,  
 
Query1 :- Thanks for suggestion but I couldn't try it because,I am adding the columns dynamically in controller. 
 
Solution provided in the previous update works for dynamic columns as well. So you can use the solution. 
 
Query#2:- I want to put the rules in below format. 
 
From your query we understand that you need to define the validation rules in the server side. We have achieved your requirement by defining the validation rules as dictionary object.  Refer the below code example. 
 
     public ActionResult Index() 
        { 
            BindDataSource(); 
            GridProperties grid = new GridProperties(); 
            List<Column> colList = new List<Column>(); 
            colList.Add(new Column() { Field = "OrderID", IsPrimaryKey = true, HeaderText = "Order ID", TextAlign = Syncfusion.JavaScript.TextAlign.Right, Width = 75, ValidationRules = new Dictionary<string, object>() { { "required", true } } }); 
            colList.Add(new Column() { Field = "OrderDate", HeaderText = "OrderDate", Format = "{0:MM/dd/yyyy }", Width = 100, ValidationRules = new Dictionary<string, object>() { {"required",true} } });                         
             grid.Columns = colList; 
             grid.DataSource = order.ToList(); 
            return View(grid); 
        } 
 
 
For your convenience we have prepared a sample which can be downloaded from below link 
 
 
Please get back to us if you have further queries.  
 
Regards, 
Vignesh Natarajan. 
 


VM Vaibhav More February 21, 2019 04:19 AM UTC

Thanks.
It works fine for me.

VN Vignesh Natarajan Syncfusion Team February 22, 2019 05:16 AM UTC

Hi Vaibhav, 
 
Thanks for the update. 
 
We are glad to hear that your query has been resolved by our solution.  
 
Please get back to us if you have further queries. 
 
Regards, 
Vignesh Natarajan 


Loader.
Up arrow icon