Script to Colour Passing or Failing Cells

[#1]

Anyone ever generate a script to colour cells that pass

dt = Current Data Table();

// Get all numeric columns
colList = dt << get column names( numeric, string );

foundCols = "";

// Loop across the columns and generate the test pass/fails
Try(For( i = 1, i <= N Items( colList ), i++,
	spec = Column( dt, colList[i] ) << get property( "Spec Limits" );
	//Establish the Notes Value from the Parent Column
	Notes = Column( dt, colList[i] ) << get property( "Notes" );
	dt << clear select;
	If( Is Empty( spec ) == 0,
		// Set Property("Notes", Notes), in the next line reattaches the Notes to the Child Column
		dt << New Column( (Column( dt, colList[i] ) << get name) || " Pass/Fail", Set Property("Notes", Notes) );
		
		// Select out of spec rows
		
		If( Is Missing( Try( spec["LSL"], . ) ) == 0,
			dt << select where( As Column( dt, colList[i] ) < spec["LSL"] );
			Try( Column( dt, N Cols( dt ) )[dt << get selected rows] = 0 );
		);
		If( Is Missing( Try( spec["USL"], . ) ) == 0,
			dt << select where( As Column( dt, colList[i] ) > spec["USL"], current selection( "extend" ) );
			Try( Column( dt, N Cols( dt ) )[dt << get selected rows] = 0 );
		);
			
		// Throw net to include missing cells
		
		dt << select where( Is Missing( As Column( dt, colList[i] ) ), current selection( "extend" ) );
				
		// Invert Selection
		
		dt << invert row selection;
		
		Try( Column( dt, N Cols( dt ) )[dt << get selected rows] = 1 );
		
		Column( dt, N Cols( dt ) ) << set property( "Value Labels", {0 = "Fail", 1 = "Pass"} );
		If( foundCols == "",
			foundCols = ":Name(\!"" || (Column( dt, colList[i] ) << get name) || " Pass/Fail\!")",
			foundCols = foundCols || ", " || ":Name(\!"" || (Column( dt, colList[i] ) << get name) || " Pass/Fail\!")"
		);
	);
););

// Create the Row Pass/Fail
Eval( Parse( "dt << New Column( \!"Row Pass/Fail\!", formula( If( Min(" || foundCols || " ) == 1, 1, 0 ) ))" ) );

dt = Current Data Table();

Grps = dt << Get Column Groups Names;

For( j = 1, j <= N Items( Grps ), j++,
colrefs={};
colrefs = dt << get column group(Grps[j]);

colnames = Transform Each({colref}, colrefs,
	 Head Name( As Namespace( colref )) ||" Pass/Fail"
);

show(colrefs, colnames);

Group = dt << Group Columns(colnames);
dt << rename column group( Group, Grps[j]||" Pass Fail" ););


Eval( Parse( "dt << New Column( \!"Count Of Tests\!", formula( Number(" || foundCols || " ) ) ))" ) );
Eval( Parse( "dt << New Column( \!"% Yield\!", formula( 100* Sum(" || foundCols || " ) / Number(" || foundCols || " ) ) ))" ) );
Eval(EvalExpr(dt << New Column( "Process Script", Character, "Nominal", Formula(Expr(Char(Process_Script))))));


dt = Current Data Table();

colList = dt << Get Column Names( String );

 
For( i = 1, i <= N Cols( dt ), i++,
	If( Contains( colList[i], "Pass/Fail" ), /* select only columns with Pass/Fail
	in the name */
		Column( colList[i] ) << Set Property( "Value Colors", {0 = -13912408, 1 = 20} ) << Color Cell by Value( 1 )
	)
);

spec vs fail?  I have a script that generates new grading columns but I wanted to affect the native columns.

[#2]

There are plenty of examples in community where spec limits are used to color cells. There are also JMP platforms which can do the coloring based on spec limit column property.

[#3 · Accepted solution]

Couldn't find any examples.  Not happy with the Manage Spec Limits colour scheme.  Did this instead.

dt = Current Data Table();

colList = dt << get column names( numeric, string );

For( i = 1, i <= N Items( colList ), i++,
spec = Column( dt, colList[i] ) << get property( "Spec Limits" );
fails = As List( dt << get rows where( As Column(Column( dt, colList[i] )) < spec["LSL"] | As Column(Column( dt, colList[i] )) > spec["USL"] ) );
Column( dt, colList[i] ) << color cells( "Red", fails );
passes = As List( dt << get rows where( As Column(Column( dt, colList[i] )) > spec["LSL"] & As Column(Column( dt, colList[i] )) < spec["USL"] ) );
Column( dt, colList[i] ) << color cells( "Green", passes );
);

[#4]

With🙏 is in spec (value)  it will get even easier to do the job

[#5]

hogi

For sure.  Meantime the script above works and is easy to do surgery on to repurpose for accounting or whatever.