Get column aggregates total when editing

[Kenneth Tang]

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 

[Mohammed Farook J]

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. 

 

https://ej2.syncfusion.com/aspnetmvc/documentation/grid/aggregates/#custom-aggregate 

 

In CustomAggregate  function you can display the aggregate based on your condition. 

 

Regards, 

J Mohammed Farook 

 

 

  

[Kenneth Tang]

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

[Mohammed Farook J]

 

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 

[Kenneth Tang]

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

[Madhu Sudhanan P]

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 

[Kenneth Tang]

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

[Pavithra Subramaniyam]

Hi Kenneth, 

 

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 :  

https://ej2.syncfusion.com/aspnetmvc/documentation/grid/aggregates/#custom-aggregate  

https://ej2.syncfusion.com/aspnetmvc/documentation/grid/edit/#custom-validation 

https://ej2.syncfusion.com/aspnetmvc/documentation/grid/aggregates/#refresh-aggregates-in-inline-edit-mode  

 

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, 

 

Sample : http://www.syncfusion.com/downloads/support/directtrac/general/ze/gridmvclocalization603265617 

 

Please get back to us if you need further assistance. 

 

Regards, 

  

Pavithra S.