How to change the sort order of values in a local data filter?

[#1]

Hi All,

I'm using JMP 18 and have a JMP file that uses a custom SQL query to create a data table. There are several dashboards (composed of graphs created in Graph Builder) attached to the data table. Some of the dashboards contain a local data filter for users to control select rows should be displayed in the associated graphs.

I want to change the sort order of the values in the local data filter. Since the sorting of the underlying data table is not an issue, I tried using the first suggestion in @txnelson 's reply to this posting (edited code below). This sorts the data table as expected, but the sort order of the Batch/Reactor column does not get applied to the local data filter (it remains in the original sort order, which appears to be alphabetical).

The Batch/Reactor column is character/nominal.

Does anyone have feedback on why this code isn't working as expected, or on other approaches I could take?

Thanks,

Andy

New SQL Query(
	Version( 130 ),
	Connection(
		"ODBC:..."
	),
	QueryName( "Custom Query" ),
	CustomSQL(
		"		select ...
[... SQL omitted ...]
		group by
			up_pro_name) as dcw on
	dcw.up_pro_name = sample.dn_mat_name
"
	),
	PostQueryScript(
		"dt = Current Data Table();
dt << Sort( By( :Name(\!"Batch/Reactor\!"),:Name(\!"EFT (h)\!") ), Order( Descending, Ascending ), Replace table );
dt:Batch/Reactor << set property( \!"value order\!", {Common Order( 0 ), Row Order Levels( 1 )} );
"
	)
) << Run Foreground( UpdateTable( Current Data Table() ) );

[#2]

Try setting the Value Ordering column property for the columns being used in the data filters.

[#3]

I think Batch/Reactor isn't valid column name if you are using it like that. Try changing it to :"Batch/Reacher"n. You also might want to add wait(0) after the sort just to be sure.

Below is an example using JMP example table and On Run Complete instead of Post Query Script

Names Default To Here(1);

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

Column(dt, 1) << Set NAme("Test/ABC");

dtquery = New SQL Query(
	Version(130),
	Connection("JMP"),
	JMP Tables(
		["Big Class" => "\C:\Program Files\JMP\JMPPRO\18\Samples\Data\Big Class.jmp"
		]
	),
	QueryName("SQLQuery1"),
	Select(
		Column("Test/ABC", "t1"),
		Column(
			"age",
			"t1",
			Analysis Type("Ordinal"),
			Numeric Format("Fixed Dec", "0", "NO", "")
		),
		Column("sex", "t1"),
		Column("height", "t1", Numeric Format("Fixed Dec", "0", "NO", ""))
	),
	From(Table("Big Class", Alias("t1"))),
	
) << Run Foreground(
	On Run Complete(
		queryResult << Sort(By(:height), Replace Table, Order(Ascending));
		wait(0);
		queryResult:"Test/ABC"n << Set Property(
			"Value Order",
			{Common Order(0), Numerical Order(0), Row Order Levels(1)}
		);
	)
);