topic Conditional Formatting Columns in a Table Box in Discussions

Conditional Formatting Columns in a Table Box

DMR — Sun, 04 Jun 2017 10:46:36 GMT

Hi - I want to apply a single conditional formatting rule to two columns of data in a Table Box. I've set up below an example using the Big Class data set which illustrates the problem, in which I can easily do what I want within a data table, but producing the same effect within a Table Box seems to be a bit more tricky.

The mechanism for creating the conditional format for a column of a Table Box is completely different to that needed for a data table, and requires that I set up a rule within my Preferences as shown. This part is straightforward enough, but I can't see a way then to apply that rule to a column other than the one to which the rule directly relates. Can it be done?

dt = Open( "$SAMPLE_DATA\Big Class.jmp" );

// Color-code blue all pupils whose height is at least 60, with everybody else purple;

// First color every cell in both the name and height columns purple;

Column( dt, "name" ) << color cells( RGB Color( 255, 0, 255 ), Index( 1, N Row( dt ) ) );
Column( dt, "height" ) << color cells( RGB Color( 255, 0, 255 ), Index( 1, N Row( dt ) ) );

// Now find everybody whose height is at least 60, and color them blue;

CellsToPaint = dt << get rows where( :Height >= 60 );

Show( CellsToPaint );

// Color them all blue;

Column( dt, "name" ) << color cells( RGB Color( 100, 100, 255 ), CellsToPaint );
Column( dt, "height" ) << color cells( RGB Color( 100, 100, 255 ), CellsToPaint );

// Now I'll try to produce the same effect with those two columns in a table box;

// Create a copy of the Big Class data table in a table box;

My_TB = Table Box( <<Set Shade Alternate Rows( 1 ), <<Set Shade Headings( 0 ), <<Set Column Borders( 1 ), <<Border( 1 ) );

For( i = 1, i <= N Col( dt ), i++,
	Heading = Column( dt, i ) << get name;
	Contents = Column( dt, i ) << get values;
	If( (Column( dt, i ) << get data type) == "Numeric",
		My_TB << append( Number Col Box( Heading, Contents ) ),
		My_TB << append( String Col Box( Heading, Contents ) )
	);
);

// Create a conditional formatting rule called "Tall";

Preferences(
	Conditional Formatting Rules(
		RuleSet(
			RuleName( "Tall" ),
			GreaterThan( Value( 60 ), Inclusive( 1 ), Format( Back Color( {100, 100, 255} ) ) ),
			LessThan( Value( 60 ), Inclusive( 0 ), Format( Back Color( {255, 0, 255} ) ) )
		)
	)
);

// Make sure it's been included in the Preferences;
	
Show Preferences();

// Apply my newly-created "Tall" rule to the Height column;

My_TB[4] << set conditional format( "Tall" );

// And finally show the result of applying it;

New Window( "My Window", My_TB );
	
// But can I apply that same color scheme to the "Name" column as well?  If so, how?

Re: Conditional Formatting Columns in a Table Box

txnelson — Sun, 04 Jun 2017 13:14:31 GMT

I do not have a solution that will create a Conditional Formatting Rule. However, I have accomplished what you want, by moving the colored data table into a journal, and then appending TableBox(1) from that journal into the application that I am creating

Re: Conditional Formatting Columns in a Table Box

DMR — Sun, 04 Jun 2017 14:24:38 GMT

Many thanks Jim - that's ingenious!

Just for the record, I'm adding below a copy of a modified version of my original script which incorporates your recommendation as to how to resolve this. Thanks once again - this is going to be phenomenally useful to me.

Clear Log();

dt = Open( "$SAMPLE_DATA\Big Class.jmp" );

// Color-code blue all pupils whose height is at least 60, with everybody else purple;

// First color every cell in both the name and height columns purple;

Column( dt, "name" ) << color cells( RGB Color( 255, 0, 255 ), Index( 1, N Row( dt ) ) );
Column( dt, "height" ) << color cells( RGB Color( 255, 0, 255 ), Index( 1, N Row( dt ) ) );

// Now find everybody whose height is at least 60, and color them blue;

CellsToPaint = dt << get rows where( :Height >= 60 );

Show( CellsToPaint );

// Color them all blue;

Column( dt, "name" ) << color cells( RGB Color( 100, 100, 255 ), CellsToPaint );
Column( dt, "height" ) << color cells( RGB Color( 100, 100, 255 ), CellsToPaint );

// Now I'll produce the same effect with those two columns in a table box using Jim's suggestion;

// Export it to the journal as a data table, which converts it into a TableBox in the process;

dt << journal;

// Pull it back again as a TableBox, putting into a window with any other objects that I might want to incorporate;

nw = New Window( "My Window",
	Lineup Box( N Col( 1 ),
		Text Box( "Clever, huh?" ),
		Border Box( Current Journal()[Table Box( 1 )], <<sides( 15 ), <<background color( {0.8, 0.8, 1} ) ),
		<<padding( 10 )
	)
);

// Now I've got a copy of it in the window I can throw away the journal;
	
Close All( journals );

// And I can inspect its properties - and modify them if I want to - by just referencing nw[TableBox(1)];

Show Properties( nw[Table Box( 1 )] );

// Tidy it up a bit for the final display;

nw[Table Box( 1 )] << Set Shade Alternate Rows( 1 );
nw[Table Box( 1 )] << Set Shade Headings( 0 );
nw[Table Box( 1 )] << Set Column Borders( 1 );
nw[Table Box( 1 )] << Border( 1 );

// End of script;