1. Home
  2. Forum
  3. ASP.NET MVC - EJ 2
  4. Get column aggregates total when editing

Get column aggregates total when editing

Dear all,

We have create the Grid with the following code

@Html.EJS(
        ).Grid(
          "gDailyTimeSheet"
        ).DataSource(
        ds => ds.Url(@Url.Action("TimeSheetDatasource", "Master", new { date = TempData["sheetDate"].ToString(), EMPLOYEE_ID = TempData["EMPLOYEE_ID"].ToString() }))
              .BatchUrl("/Master/TimeSheetBatchUpdate")
              .Adaptor("UrlAdaptor")
        ).AllowSorting(
        ).Columns(
          col =>
          {
              col.Field("WEEK_DATE").HeaderText("WEEK_DATE").Width("100").Visible(false).DefaultValue(TempData["sheetDate"].ToString()).Add();
              col.Field("EMPLOYEE_ID").HeaderText("EMPLOYEE_ID").Width("100").Visible(false).DefaultValue(TempData["EMPLOYEE_ID"].ToString()).Add();
              col.Field("TASK_PROJECT").HeaderText("Project").Width("300").DefaultValue(" ")
                  .EditType("dropdownedit")
                      .Edit(
                          new
                          {
                              @params = new
                              {
                                  fields = new
                                  {
                                      text = "TASK_PROJECT",
                                      value = "TASK_PROJECT"
                                  },
                                  dataSource = @ViewBag.projectList
                              }
                          }
                      ).Add();
              col.Field("TASK_ID").HeaderText("TASK").Width("300").DefaultValue(" ")
                  .EditType("dropdownedit")
                      .Edit(
                          new
                          {
                              @params = new
                              {
                                  fields = new
                                  {
                                      text = "TASK_ID",
                                      value = "TASK_ID"
                                  },
                                  dataSource = @ViewBag.taskList
                              }
                          }
                      ).Add();
              col.Field("TASK_REMARKS").HeaderText("Remarks").MinWidth("200").DefaultValue(" ").Add();
              col.Field("SUNDAY").HeaderText(ViewBag.Sunday).MinWidth("100").Format("0.0").DefaultValue("0.0").EditType("numericedit").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right
                  ).Add();
              col.Field("MONDAY").HeaderText(ViewBag.Monday).MinWidth("100").Format("0.0").DefaultValue("0.0").EditType("numericedit").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
              col.Field("TUESDAY").HeaderText(ViewBag.Tuesday).MinWidth("100").Format("0.0").DefaultValue("0.0").EditType("numericedit").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
              col.Field("WEDNESDAY").HeaderText(ViewBag.Wednesday).MinWidth("100").Format("0.0").DefaultValue("0.0").EditType("numericedit").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
              col.Field("THURSDAY").HeaderText(ViewBag.Thursday).MinWidth("100").Format("0.0").DefaultValue("0.0").EditType("numericedit").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
              col.Field("FRIDAY").HeaderText(ViewBag.Friday).MinWidth("100").Format("0.0").DefaultValue("0.0").EditType("numericedit").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
              col.Field("SATURDAY").HeaderText(ViewBag.Saturday).MinWidth("100").Format("0.0").DefaultValue("0.0").EditType("numericedit").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
              col.Field("WEEKTOTAL").HeaderText("Total").MinWidth("100").Format("0.0").DefaultValue("0.0").AllowEditing(false).TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
          }
        ).EditSettings(
          edit =>
          {
              edit.AllowAdding(true).AllowEditing(true).AllowDeleting(true).Mode(Syncfusion.EJ2.Grids.EditMode.Batch).ShowDeleteConfirmDialog(true);
          }
        ).Load(
          "load"
        ).AllowPaging(
        ).Aggregates(
          agg =>
          {
              agg.Columns(new List<Syncfusion.EJ2.Grids.GridAggregateColumn>() {
                    new Syncfusion.EJ2.Grids.GridAggregateColumn() { Field = "SUNDAY", Format ="0.0" , Type = "Sum", FooterTemplate = "Total : ${Sum}" }
                    , new Syncfusion.EJ2.Grids.GridAggregateColumn() { Field = "MONDAY", Format ="0.0" ,  Type = "Sum", FooterTemplate = "${Sum}" }
                    , new Syncfusion.EJ2.Grids.GridAggregateColumn() { Field = "TUESDAY", Format ="0.0" ,  Type = "Sum", FooterTemplate = "${Sum}" }
                    , new Syncfusion.EJ2.Grids.GridAggregateColumn() { Field = "WEDNESDAY", Format ="0.0",   Type = "Sum", FooterTemplate = "${Sum}" }
                    , new Syncfusion.EJ2.Grids.GridAggregateColumn() { Field = "THURSDAY", Format ="0.0",   Type = "Sum", FooterTemplate = "${Sum}" }
                    , new Syncfusion.EJ2.Grids.GridAggregateColumn() { Field = "FRIDAY", Format ="0.0",   Type = "Sum", FooterTemplate = "${Sum}" }
                    , new Syncfusion.EJ2.Grids.GridAggregateColumn() { Field = "SATURDAY", Format ="0.0",   Type = "Sum", FooterTemplate = "${Sum}" }
                 }).Add();
          }
        ).Toolbar(
          toolbarItems
        ).AllowTextWrap(
        true
        ).ToolbarClick("toolbarClick").Render()

We want to add a validation for the aggregates total for not larger that 8 when user input.

Please advance how to implement the above feature, thx.

KennethT 

7 Replies
MF Mohammed Farook J Syncfusion Team November 28, 2018 01:00 PM UTC

Hi Kenneth, 
 
We have validated your requirement , if you want perform Grid aggregate with custom function by using ‘customAggregate’  of Grid. Please find the documentation for your reference. 
 
 
In CustomAggregate  function you can display the aggregate based on your condition. 
 
Regards, 
J Mohammed Farook 
 
 
  


KT Kenneth Tang November 28, 2018 01:46 PM UTC

Dear Frook,

Thank for info. But we want to have a validation for total sum of the column when user input instaed of display the column aggregate total. For example, we want to check the sum of particatar colum from all of the row not more 100, please advance how could we implement by using grid control.

Thank a lot!

KennethT

MF Mohammed Farook J Syncfusion Team November 29, 2018 01:05 PM UTC

 
Hi Kenneth, 
 
We have achieved your requirement by using ‘customAggregate’ . Please find the code example. 
 
 
@Html.EJS().Grid("Grid"). Columns(col => 
{ 
    col.Field("CustomerID").HeaderText("Customer Name").Width("150").Add(); 
    col.Field("Freight").HeaderText("Freight").Width("160").Format("C2").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add(); 
     
    col.Field("ShipCountry").HeaderText("Ship Country").Width("140").Add(); 
 
}).AllowPaging().PageSettings(page => { page.PageCount(5); }).Aggregates(agg=> { agg.Columns(new List<Syncfusion.EJ2.Grids.GridAggregateColumn>() { new Syncfusion.EJ2.Grids.GridAggregateColumn() { Field = "Freight", Type = "Custom", FooterTemplate = "    Sum of Freight:${Custom}", CustomAggregate= "customAggregateFn" }  
 
 . . .. 
 
 
  
<script> 
    var sumofFreight = 0; 
    function customAggregateFn(data) { 
      data.result.filter(function (item) { 
            sumofFreight += item['Freight']        
        }); 
 
        if (sumofFreight > 100) { 
            return 'is exceed sum value more than 100' 
        } 
        else { 
            return sumofFreight; 
        } 
    } 
</script> 
 
 
In above code example we have checked the sum value of Freight is exceed more than 100 or not.   
 
This is not meet your requirement could you please share the more details about your requirement.  
 
Regards, 
J Mohammed Farook 


KT Kenneth Tang November 30, 2018 01:07 AM UTC

Dear Farook,
As we want to prevent user to input wrong data in the grid.
Could the checking sum by validation rule that we could popup a message or dialog box and focus user to change the value ?

Thank a lot!

KennethT

MS Madhu Sudhanan P Syncfusion Team December 3, 2018 12:51 PM UTC

Hi Kenneth, 

Thanks for the update. 

Query: Could the checking sum by validation rule that we could popup a message or dialog box and focus user to change the value ? 

From the query, we understood that you want to show validation error message on the cell when the aggregate calculation does meet the condition. To do so you can use the custom validation capability of the grid columns.  


@Html.EJS().Grid("gDailyTimeSheet").Load(‘load’). . ..Render(); 
 
function load() { 
   this.columns[1].validationRules = { required: true, minLength: [customFn, 'Incorrect value'] }; 
} 

function customFn() { 
    //Return true if the condition fails.     
} 


Regards, 
Madhu Sudhanan P 


KT Kenneth Tang February 4, 2019 02:50 AM UTC

Dear Madhu,

Thank for info. But could you help to show up how we could apply it with the validation rule for checking aggregate total ?
Thank a lot!

KennethT

PS Pavithra Subramaniyam Syncfusion Team February 4, 2019 07:18 AM UTC

 
Query : could apply it with the validation rule for checking aggregate total ? 
As suggested in our update dated on November 28,2018 we suggest you to use the “CustomAggregate” feature of Grid to apply validation for the aggregates in Grid. 
 
Documentation :  
 
Based on your request, we have created a sample involving both “custom column validation” and “custom aggregate validation” in Grid. We are attaching the sample for your convenience. Please download the sample from the link below, 
 
 
Please get back to us if you need further assistance. 
 
Regards, 
  
Pavithra S. 


Loader.
Up arrow icon