Control Limit refreshing for Control Charts

[#1]

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;

[#2 · Accepted solution]

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.

[#3]

This on top of another reply has helped. Now the warning triage in JMP live is still throwing a fit saying the limits need to be static but they are set as user defined.

[#4]

The JSL you provided has syntax errors.  When I attempted to Reformat it, it fails at line 67 for not having a ";" ending the previous line.  With that corrected it then fails for not finding closing ")" for the various For Each Row blocks.  That is a stopper for my debugging since without having the For blocks correctly closed, it is very tough to guess at what the program flow logic is.

Please correct these errors and then resubmit.

Also, please use the JSL icon at the top of the input window to place your JSL into the Discussion.  It allows for the reader to better evaluate your code.