Updating the value of one cell based on another cell value and vice versa in iggrid?

I’m using an ASP.Net MVC igGrid and i have created the grid like below

@(Html.Infragistics().Grid(this.Model.lstMilestoneDetails).PrimaryKey(“intMileStoneNo”).AutoCommit(false).AutoGenerateColumns(false).ID(“MileStoneGrid”).Columns(column =>
{
column.For(x => x.intMileStoneNo).Hidden(true); //MilestoneNo
column.For(x => x.txtDescription).HeaderText(“Description”); //Description
column.For(x => x.dtDate).Format(“dd-MMM-yyyy”).HeaderText(“Date”).Width(“15%”);
column.For(x => x.fltPercentage).HeaderText(“Percentage”); //Percentage
column.For(x => x.fltAmount).HeaderText(“Amount”); //Amount
column.For(x => x.MilestonePercentage).HeaderText(“Milestone%”);
}).Features(features =>
{
features.Updating().ColumnSettings(cs =>
{
cs.ColumnSetting().ColumnKey(“txtDescription”).EditorType(ColumnEditorType.Text).Required(true);
cs.ColumnSetting().ColumnKey(“dtDate”).EditorType(ColumnEditorType.DatePicker).Required(true);
cs.ColumnSetting().ColumnKey(“fltPercentage”).EditorType(ColumnEditorType.Numeric).DefaultValue(0).Required(true).NumericEditorOptions(opts => opts.MaxValue(100).MinValue(0));
cs.ColumnSetting().ColumnKey(“fltAmount”).EditorType(ColumnEditorType.Numeric).DefaultValue(0).Required(true);
cs.ColumnSetting().ColumnKey(“MilestonePercentage”).ReadOnly(true);
});
features.Summaries().ColumnSettings(cs =>
{
cs.ColumnSetting().ColumnKey(“fltPercentage”).AllowSummaries(true).SummaryOperands(so => { so.SummaryOperand().RowDisplayLabel(“Sum”).Type(SummaryFunction.Sum).Active(true); });
cs.ColumnSetting().ColumnKey(“fltAmount”).AllowSummaries(true).SummaryOperands(so => { so.SummaryOperand().RowDisplayLabel(“Sum”).Type(SummaryFunction.Sum).Active(true); });
});
features.Resizing();
features.ColumnMoving().AddMovingDropdown(false);
features.Sorting().ApplyColumnCss(false);
features.Selection().MultipleSelection(true);
features.RowSelectors().EnableSelectAllForPaging(true).EnableCheckBoxes(true).EnableRowNumbering(false);
features.Paging().PageSize(5).PageSizeDropDownLocation(“inpager”).AddClientEvent(“pageIndexChanged”, “fnPagination”).AddClientEvent(“pageSizeChanged”, “fnPageSizeChange”);
})
.AutoCommit(true)
.DataBind().Render()
)

I want to calculate a value of cell “Amount” when I change value of “Percentage” and vice versa . I need to change the values either on blur of that particular cell or when the value is changed.

Like When i enter percentage as 15% i want to update the amount value like 15 * some predefined value.

How can I do that? Can you kindly provide it with an example?

Hello Kumar,

Thank you for posting in our community.

What I can suggest for achieving your requirement is using formatter option of igGrid columns. This is a reference to a function which will be used for formatting cell values. This function has two arguments, val – that holds current cell value and record – that holds the whole record values. My idea is to create a formatter function for Amount column where you will calculate the value based on the "Percentage" column value. When editing if you update the Percentage column`s value formatter function will reflect that in the Amount column`s value. For example:

column.For(x => x.fltAmount).HeaderText("Amount").FormatterFunction("formatVal"); //Amount

//define formatVal function

<script>

function formatVals(val, record) {

var formattedVal = record.Percentage * predefined value;

return formattedVal;

}

</script>

Keep in mind that formatting operates at grid rendering phase and does not affect the data in the underlying data source. Some further reference about formatting can be found here.

Please test this approach on your side and let me know if it helps you achieve your requirement.

Regards,Vasya Kacheshmarova
Associate Software DeveloperInfragistics

Hi Vasya,

Thanks for the reply. This solution works when i change the percentage,the amount gets calculated correctly.

But how can i try the vice versa of it. When i tried adding another formatter function for percentage..it goes in a loop and the values are coming out wrong.

When i have two formatter function each for Amount and Percentage like below example

Eg: Old Percentage :25 Amount:(25(percent)*5000(predefined value)) * 100 = 1250

New Percentage : 50 Amount–> (50 * 5000) *100 =2500

When i change the amount to 4000 then it picks the old percentage instead of the updated percentage for calculation.and the values are coming out wrong.

How can i get the new updated value to perform the vice versa.

Thanks,

Kumar G

Hello Kumar,

Thank you foe getting back to me.

What I can suggest for having the vice versa dependency of these cells is handling editCellEnding event of the Updating feature. In this event you can check which is the cell that triggered it and if it as any of the Amount or Perecntage cells to update the other one accordingly using setCellValue method. For example:

$(document).delegate("#grid", "iggridupdatingeditcellending", function (evt, ui) {
                if (ui.columnKey === "Amount") {
                    var value = ui.value * 2; //make your calculcations here
                    $("#grid").igGridUpdating("setCellValue", ui.rowID, "Percentage", value);

                }
                if (ui.columnKey === "Percentage") {
                    var value = ui.value / 2; //make your own calculations here
                    $("#grid").igGridUpdating("setCellValue", ui.rowID, "Amount", value);
                }
            });

Please keep in mind that this approach will work as expected for editMode set to "cell". If you have a requirement for "row" editMode please let me know.

Regards,
Vasya Kacheshmarova
Associate Software Developer
Infragistics

Hi Vasya,

Thanks for the reply.

I need the above requirement for the "row" editMode. Can you please provide an example for that as well.

Thanks,

Kumar G

Hello Kumar,

My suggestion is to handle igNumericEditor`s valueChanged event. In this event the value of the other depended editor can be set according to your formula. For example:

1) Define the valueChanged event in Updating column settings collection:

  .Features(f =>
            {
                f.Updating()
                    .EnableAddRow(false)
                    .EditMode(GridEditMode.Row)
                    .ColumnSettings(cs =>
                    {
                        cs.ColumnSetting().ColumnKey("Id").ReadOnly(true);
                        cs.ColumnSetting().ColumnKey("Amount").NumericEditorOptions(opts=> {
                            opts.AddClientEvent("valueChanged", "amountValChanged");
                        });
                        cs.ColumnSetting().ColumnKey("Percentage").NumericEditorOptions(opts =>
                        {
                            opts.AddClientEvent("valueChanged", "percentageValChanged");
                        });
                    });
            })

2) Handle valued in valueChanged event hadnlers:

    <script>
        $(function () {
            window.amountValChanged = function(evt, ui) {
                $("#grid").igGridUpdating("editorForKey", "Percentage").igNumericEditor("value", ui.newValue * 2);
            }
            window.percentageValChanged = function (evt, ui) {
                $("#grid").igGridUpdating("editorForKey", "Amount").igNumericEditor("value", ui.newValue / 2);
            }
        });
    </script>

Please test this suggestion on your side and let me know if you have any additional questions afterwards.

Hello Kumar,

I am glad that you find my suggestion helpful!

Thank you for using Infragistics components.