Control Limit refreshing for Control Charts

WFzw

I am very novice when it comes to JMP pro, JMP live and JSL. Here is my issue, I have a refresh script that successfully pulls spec limits from a database in ssms and applies them to the properties of the columns; I want to start pulling from another table to apply individual control limits for all process variables in the data set. When I add what I believe to be the correct blocks to accomplish this it still sets the spec limits, but the control limits do not make it through. No errors or anything in the log so I know it's silently ignoring the new blocks but that in itself I have no idea what it means.  

If anyone can point me in the right direction that would be greatly appreciated!

sql_query_main = " SELECT * FROM jmpTop30WideView WHERE Product LIKE '1/2 RS' AND TestType IN ('Retest', 'Hot')";
sql_query_limits = "SELECT Property AS Variable, LSL, Target, USL FROM ProductSpecsLongView WHERE [Product] LIKE '1/2 RS' ";
sql_query_Control = "SELECT Property AS Variable, Avg, LCL, UCL FROM ControlLimitsTable WHERE [Product] LIKE '1/2 RS' ";

main = Open Database(
	"DATABASE=XXXXXXXXXXXXXX;
DRIVER={SQL Server};
PWD=%_PWD_%;
SERVER=XXX-XXXXX;
TRUSTSERVERCERTIFICATE=Yes;
UID=%_UID_%;",
	sql_query_main
);

limits = Open Database(
	"DATABASE=XXXXXXXXXXXXXX;
DRIVER={SQL Server};
PWD=%_PWD_%;
SERVER=XXX-XXX-XXXXX;;
TRUSTSERVERCERTIFICATE=Yes;
UID=%_UID_%;",
	sql_query_limits
);

Control = Open Database(
	"DATABASE=XXXXXXXXXXXXXX;
DRIVER={SQL Server};
PWD=%_PWD_%;
SERVER=XXX-XXXXXX;;
TRUSTSERVERCERTIFICATE=Yes;
UID=%_UID_%;",
	sql_query_Control
);

Column(main, "DateTimeStampUnique") << Set Property("Label", 1);
main << Set Label Columns(:DateTimeStampUnique);

// References
dtMain = main;
dtLimits = limits;
dtControl = Control;

For Each Row(
	dtLimits,
	variable = dtLimits:Variable[];
	lsl = dtLimits:LSL[];
	target = dtLimits:Target[];
	usl = dtLimits:USL[];

	Eval(Eval Expr(Column(dtMain, variable) << Set Property("Spec Limits", {LSL(Expr(lsl)), Target(Expr(target)), USL(Expr(usl)), Show Limits(1)})));

	If(Contains(dtMain << Get Column Names, variable),
		Column(dtMain, variable) << Set Property("Spec Limits", {LSL(lsl), Target(target), USL(usl)})
	);
);

For Each Row(
	dtControl,
	variable = dtControl:Variable[];
	Avg = dtControl:Avg[];
	LCL = dtControl:LCL[];
	UCL = dtControl:UCL[];

	Eval(Eval Expr(Column(dtMain, variable) << Set Property("Control Limits", {Avg(Expr(Avg)), LCL(Expr(LCL)), UCL(Expr(UCL)), Show Limits(1)})));

	If(Contains(dtMain << Get Column Names, variable),
		Column(dtMain, variable) << Set Property(
			"Control Limits",
			{AVG(), LCL(), UCL()}

		)
	);
);

dtMain;

Edit (jthi): Added jsl formatting (I left original script in spoiler block as there were many missing brackets which I had to fix)

View more...

<jsl>

sql_query_main = " SELECT * FROM jmpTop30WideView WHERE Product LIKE '1/2 RS' AND TestType IN ('Retest', 'Hot')";
sql_query_limits = "SELECT Property AS Variable, LSL, Target, USL FROM ProductSpecsLongView WHERE [Product] LIKE '1/2 RS' ";
sql_query_Control = "SELECT Property AS Variable, Avg, LCL, UCL FROM ControlLimitsTable WHERE [Product] LIKE '1/2 RS' ";

main = Open Database(
"DATABASE=XXXXXXXXXXXXXX;
DRIVER={SQL Server};
PWD=%_PWD_%;
SERVER=XXX-XXXXX;
TRUSTSERVERCERTIFICATE=Yes;
UID=%_UID_%;",
sql_query_main
);

limits = Open Database(
"DATABASE=XXXXXXXXXXXXXX;
DRIVER={SQL Server};
PWD=%_PWD_%;
SERVER=XXX-XXX-XXXXX;;
TRUSTSERVERCERTIFICATE=Yes;
UID=%_UID_%;",
sql_query_limits
);

Control = Open Database(
"DATABASE=XXXXXXXXXXXXXX;
DRIVER={SQL Server};
PWD=%_PWD_%;
SERVER=XXX-XXXXXX;;
TRUSTSERVERCERTIFICATE=Yes;
UID=%_UID_%;",
sql_query_Control
);

Column( main, "DateTimeStampUnique" ) << Set Property( "Label", 1 );
main << Set Label Columns( :DateTimeStampUnique );

// References
dtMain = main;
dtLimits = limits;
dtControl = Control;

For Each Row( dtLimits,
variable = dtLimits:Variable[];
lsl = dtLimits:LSL[];
target = dtLimits:Target[];
usl = dtLimits:USL[];

Eval( Eval Expr(
Column( dtMain, variable ) <<
Set Property(
"Spec Limits",
{LSL( Expr( lsl ) ), Target( Expr( target ) ), USL( Expr( usl ) ), Show Limits( 1 )}
)
));

If(
Contains( dtMain << Get Column Names, variable ),
Column( dtMain, variable ) << Set Property(
"Spec Limits",
{LSL( lsl ), Target( target ), USL( usl )}
)

For Each Row( dtControl,
variable = dtControl:Variable[];
Avg = dtControl:Avg[];
LCL = dtControl:LCL[];
UCL = dtControl:UCL[];

Eval( Eval Expr(
Column( dtMain, variable ) <<
Set Property(
"Control Limits",
{Avg( Expr( Avg ) ), LCL( Expr( LCL ) ), UCL( Expr( UCL ) ), Show Limits( 1 )}
)
));

If(
Contains( dtMain << Get Column Names, variable ),
Column( dtMain, variable ) << Set Property(
"Control Limits",
{AVG(), LCL(), UCL()}

)
);
);

dtMain;

mmarchandFSLR

I'm betting this would work if you specify the Control Chart type, so instead of

 << Set Property( "Control Limits", {Avg( Expr( Avg ) ), LCL( Expr( LCL ) ), UCL( Expr( UCL ) ), Show Limits( 1 )} )t

try

<< Set Property( "Control Limits", {XBar( Avg( Expr( Avg ) ), LCL( Expr( LCL ) ), UCL( Expr( UCL ) ), Show Limits( 1 ) )} )

(replacing "XBar" with your intended Control Chart type)

 

Also, I think you should try commenting out your redundant If() statements that attempt to assign the Spec Limits and Control Limits after you already did it.  See if it works the way you'd like it to.