topic Re: What makes the formula evaluate over and over? in Discussions

What makes the formula evaluate over and over?

hogi — Tue, 07 Nov 2023 19:11:24 GMT

I noticed an unexpected behavior in Jmp:

After creating a Formula column with a Custom Formula, another Formula Column gets evaluated every time I select a cell in the data table.

Why is the formula evaluated so many times?

removing the myX =1; myX fro the first formula stops the behavior
removing the custom formula from the second formula stops the behavior

Jmp 17.2

Names Default To Here( 1 );

row_count = 10;
dt = New Table( "myTable", Add Rows( row_count ), New Column( "X", set each value( Row() ) ) );


dt << New Column( "Formula As Constant",
	Formula(
		if(row()==1,write("\!N"));
		Write("+1 ");
		myX = 1;
		myX
	)
);

new namespace("test");

Add Custom Functions( New Custom Function( "test", "myFunc", Function( {}, 1 ) ) );

dt << New Column( "Formula via Custom Formula", Formula( test:myFunc() ) );

Re: What makes the formula evaluate over and over?

jthi — Tue, 07 Nov 2023 19:48:16 GMT

Maybe JMP is for some reason forced to perform re-evaluation if formula columns with custom functions are used.

Using JMP's example custom formula and Craige's example from https://community.jmp.com/t5/Discussions/Why-does-selecting-deselecting-rows-trigger-column-formulas-to/m-p/188823/highlight/true#M40750 . Run the script, select one row, check lo, run rest of the script after stop, click row again.

Names Default To Here(1);

dt = New Table("Untitled",
	add rows(3), 
	// EVERYTHINGCOL depends on everything. it evaluates last.
	New Column("EVERYTHINGCOL", Formula(
			Write("\!nEVERYTHINGCOL,row" || Char(Row()));
			:ROWSTATECOL + :INDEPCOL + :DEPONINDEP;
		)
	), 
	// DEPONINDEP depends on INDEPCOL, but not ROWSTATECOL.
	New Column("DEPONINDEP",
		Formula(
			Write("\!nDEPONINDEP,row" || Char(Row()));
			:INDEPCOL + 1;
		)
	), 
	// INDEPCOL is independent. It evaluates early, maybe first.
	New Column("INDEPCOL",
		Formula(
			Write("\!nINDEPCOL,row" || Char(Row()));
			17;
		)
	), 
	// ROWSTATECOL is directly dependent on row state information. It evaluates early, maybe first. 
	New Column("ROWSTATECOL",
		Formula(
			Write("\!nROWSTATECOL,row" || Char(Row()));
			Selected(Row State());
		)
	)
);

dt << Run Formulas;
Write("\!N -------------------- INITIAL PRINTS----------------\!N");
// Clear Log();
dt << select rows(1);
dt << Run Formulas;
Write("\!N -------------------- SEL ROW PRINTS 1----------------\!N");

funcDef = Function({x, y = 10}, x + y);
newAdd = New Custom Function("myNamespace", "Add Ten", funcDef);
newAdd << Formula Category("NumberStuff");
Add Custom Functions(newAdd);

new_col = dt << New Column("AddTen", Formula(
	Write("\!nAddTen,row" || Char(Row()));
	myNamespace:Add Ten(Row(), 1))
);
dt << select rows(2);
dt << Run Formulas;

Write("\!N -------------------- SEL ROW PRINTS 2----------------\!N");
// new_col << Suppress Eval(1);

You can also see this behavior if you just rerun << Run Formulas. If you don't have the custom formula there, nothing is printed (except for Scriptable[]) and if you add the formula using custom function, all the formulas will be rerun on each << Run Formulas (basically behaves like << rerun formulas, all formulas "must be" re-evaluated).

Re: What makes the formula evaluate over and over?

hogi — Wed, 08 Nov 2023 20:59:44 GMT

@jthi wrote:
Maybe JMP is for some reason forced to perform re-evaluation if formula columns with custom functions are used.

Does that mean that customs functions in formula columns are even more "nasty" than formula columns with rowstates in the formula ... because they trigger the evaluation of All Formula columns, not just the dependent ones?

Ouch.

-> If your tables are > 100k rows and you have some formula columns - think twice before using Custom Functions?

Chris' problem also immediately came to my mind when I observed this behavior today. I had no time yet to ask:
@Chris_Rodrigues , did you use some custom functions in you table [edit - or namespaces ... see below] ?

@Craige_Hales , fyi.

Re: What makes the formula evaluate over and over?

hogi — Wed, 08 Nov 2023 11:49:52 GMT

When manually selecting cells ...

Interesting #1:

(concerning columns like c3 without variables) just the last selected row and the newly selected row gets updated (track the timestamps). Maybe with the idea: These are the 2 rows with a potential manual change?

Hm.

Interesting #2:

For formula column with a variable, like c1, the whole column is evaluated again.

Maybe with the idea: The variable could have changed, so let's be sure and update all values.

But on the other hand, changing the variable via JSL (i=-1000) doesn't trigger an update (?)

(view in My Videos)

Names Default To Here( 1 );

dt = New Table( "myTable", Add Rows( 5 ) );

i=0;

// watch(i,j);

dt << New Column( "c1",	Formula(if(row()==1,i++;write("\!n"),write("c1 "));i));
write("\!n");
dt << New Column( "c3",	Formula( write("c3 " );today() ));
write("\!n");

Add Custom Functions( New Custom Function( "myNS", "myFunc", Function( {}, 1 ) ) );
dt << New Column( "trigger", Formula( myNS:myFunc() ) );

i=-1000;

Re: What makes the formula evaluate over and over?

Craige_Hales — Wed, 08 Nov 2023 13:20:06 GMT

I don't think the formula evaluator plays well with namespaces. Looks like a bug. @Audrey_Shull

Run this up to the ok so far comment, click some rows, watch the log...it is quiet. Then run the two lines from the work-around section, and the log is still quiet when clicking rows. But start over and use the other two lines, the log is noisy.

row_count = 10;
dt = New Table( "myTable", Add Rows( row_count ), New Column( "X", set each value( Row() ) ) );

dt << New Column( "aaa",
	Formula(
		if(row()==1,write("\!N"));
		Write("a");
		myX = x+1;
		myX
	)
);

new namespace("test");
test:xyzzy = 42;

Add Custom Functions( New Custom Function( "test", "myFuncB", 
	Function( {}, Write("b");x=randominteger(1,9);1 ) ) );

wrapperB=function({},test:myFuncB());

dt << New Column( "bbb", Formula( wrapperB() ) );
//
// ok so far. Either of the following trigger the issue.
// apparently using a namespace in a formula has something to do with it.
//
dt << New Column( "bbbNameSpace", Formula( test:myFuncB() ) );
dt<<newcolumn("xyzzyNameSpace",formula(test:xyzzy));
//
// you can use a workaround like this to prevent the formula
// evaluator from seeing the namespace
//
dt << New Column( "bbbNameSpace2", Formula( ttt=namespace("test")["myFuncB"];ttt() ) );
dt<<newcolumn("xyzzyNameSpace",formula(namespace("test")["xyzzy"]));

Re: What makes the formula evaluate over and over?

hogi — Wed, 08 Nov 2023 21:22:21 GMT

conclusive story :)

thank you for the workaround ...

Re: What makes the formula evaluate over and over?

Audrey_Shull — Wed, 08 Nov 2023 20:13:54 GMT

Thanks @Craige_Hales for the great example - i submitted it to Development.

Re: What makes the formula evaluate over and over?

hogi — Sun, 21 Jan 2024 19:21:56 GMT

Hi @Audrey_Shull , do I have to submit a wish to the wish list - or do you think that the bug gets fixed automatically?

Re: What makes the formula evaluate over and over?

Audrey_Shull — Wed, 24 Jan 2024 16:51:55 GMT

Hi @hogi - Wish List items are generally used for future functionality requests. Bugs should be reported through Technical Support, or, when they are reported here, sometimes staff monitoring the forums will report them to Development on your behalf, as i did in this example. A duplicate Wish List item should not be necessary.