Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
bobmorrane
Level IV

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 ?

 

 

 

Another day, another question :)
1 REPLY 1
Highlighted

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
		)
	)
);
Learn it once, use it forever!
Article Labels

    There are no labels assigned to this post.