cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
bobmorrane
Level V

Formulas in data table not re-evaluating

Howdy,

 

So today I have an issue with formulas not updating in my data table.

 

I have a large table with a bunch of measurements. There is a script that sets columns with formulas to evaluate specifications contained in data table variables. These formulas are generated in the following way :

 

Eval( Eval Expr(Column(Concat(columnName," Spec"))<< Set Formula(

If( Column(expr(columnName))[]>Expr(USL),							
 Column(expr(Concat(expr(columnName)," Spec")))[]="High",
											
If( Column(expr(columnName))[]<Expr(LSL),
Column(expr(Concat(expr(columnName)," Spec")))[]="Low",	
Column(expr(Concat(expr(columnName)," Spec")))[]="OK"; ) ) )));

These formulas work fine but they only evaluate when they are set. When I try to change a value in a column, the specification is not evaluated. I checked in the column properties and the formula evaluations are enabled. The formulas do re-evaluate I check then uncheck the "suppress formula evaluation".

 

I tried to modify this script by replacing "Columns" with "As Columns" and removing the []. This did not solve the  issue and made the implementation of these formulas 50% slower.

 

I tried to manually set formulas with the same template but using the actual column names (:Column instead of Column (expr (ColumnName )  and they do work correctly. e.g, whenever I change a value in one of the columns, the formula is evaluated straight away.

 

So, is there a way to force re-evaluation upon modifying cells? ** NOT clicking the red button and re-evaluating all the formulas of the data table **

 

Or perhaps another work around would be to have a script that writes the actual column names (e.g. :Column ) instead of having the Eval(expr(column (columname))).

 

So it looks like this:

Capture22.PNG

instead of looking like this:

Capture.PNG

 
 

Any ideas ?

 

 

 

~~Bob~~
1 REPLY 1

Re: Formulas in data table not re-evaluating

I cannot test it but I think that this approach might work:

 

Eval(
	Substitute(
		Expr(
			nnn << Set Formula(
				If(
					ccc > uuu,
						"High", 
					ccc < lll,
						"Low",
					"OK"
				)
			)
		),
		Expr( nnn ), Column( Concat( columnName, " Spec" ) ),
		Expr( ccc ), Column( columnName ),
		Expr( lll ), LSL,
		Expr( uuu ), USL
		)
	)
);