Handsontable afterChange, do calculation, setdatacell stuck in loop

Tags: #<Tag:0x00007f8b188e2a60>
#1

I have a handsontable, when a cell is changed I want to recalculate the current row values.

I am doing this by using the afterChange hook, doing the calculations and then setting the new cell value using setDataAtCell.

The issue is I am getting stuck in an infinite loop because the setDataAtCell is triggering the afterChange it is currently using.

I have tried adding a false variable and setting this to true if it has been run to get around this but this refuses to work. Even if it did work then in my scenario of calculating the data it wouldn’t work for me because it would mean if the same data was changed a second time then the recalculation wouldn’t occur.

Any ideas on how to solve?

				var hot = new Handsontable(container, {
					data: data,
					stretchH: "all",
					rowHeaders: true,
					colHeaders: true,
					filters: true,
					dropdownMenu: true,
					formulas: true,
					afterChange: function( changes, source ) {

						if( arguments[1] != "loadData" ) {

							if( !changes ) {
								return;
							}

							changedRowStartingZero = changes[0][0];
							changedRow = changedRowStartingZero;

							console.log( '-------' );

							var currentRow = changedRow;


							var net = hot.getDataAtCell( currentRow, 7 );
							var netTrade = hot.getDataAtCell( currentRow, 8 );
							var deliveryCost = hot.getDataAtCell( currentRow, 10 );
							var fees = hot.getDataAtCell( currentRow, 11 );
							var totalCost = netTrade + deliveryCost + fees;
							var netProfit = net - totalCost;
							var percentProfit = netProfit / net ;


							hot.setDataAtCell( currentRow, 12, totalCost );


							hot.setDataAtCell( currentRow, 13, netProfit );


							hot.setDataAtCell( currentRow, 14, percentProfit );

						}

					},
How to populate cells with changing values by autofill
#2

Hi @davidboom

as can be replicated here http://jsfiddle.net/s6ap7zuo/ calling setDataAtCell calls the afterChange hook. That is why you are getting the infinite loop.

If you wish to use the setDataAtCell inside the afterChange hook you can use a helper.

Here is an example http://jsfiddle.net/fs2nspys/, where I am using a ‘setter’ variable that tells the ‘afterChange’ when it should not, can the setDataAtCell.

I think that it would be a good choice to extend the source (2nd) parameter to determine if a change has been made programmatically or by the user, like mentioned in this topic https://github.com/handsontable/handsontable/issues/4388

1 Like
#3

Hi,

Thanks for the information, this has helped me to get a little further but I am still having an issue.

I’ve now put a demo together here: https://jsfiddle.net/htdq26Lo/2/

That code is a combination of your setter method and my original code. If you change a row cell value it will hit an infinite loop (see console log).

If I remove the ‘else’ condition which sets the setter back to false this stops the infinite loop - BUT it means the row values can only be changed once, if you try to edit them again the values don’t recalculate.

#4

I did a bit more on this and came up with: https://jsfiddle.net/htdq26Lo/3/

Its really not that elegant though as it just resets the setter variable to false every second. New cell changes then get the false setter so can be changed, the setdata functions seem to still see the true setting so those are applied too.

There must be a better way.

#5

Sorry but I got a little bit lost. Can you precisely tell me what is the scenario and the bug?

#6