GUI Combo Box based on Two Related Columns

[#1]

Hello, 

If I have two columns in a file, I can create a combo box using the two columns separately (see JSL file attached). However, what I really want is that once I select the first option (say "a"), it should restrict my second box options to "a1", "a2" and "a3" only. See the Excel file attached.

Any help would be appreciated. 

v1 = "m";
v2 = "n";

dt = Open("JMP_Question.xlsx", Worksheets("Sheet1"), invisible);

subDt1 = dt << Subset(Columns(:Column 1), invisible);
Summarize(col1_unique_vals = by(Column 1));
subDt2 = dt << Subset(Columns(:Column 2), invisible);
Summarize(col2_unique_vals = by(Column 2));


win = New Window("Set the Value",
	H List Box(Text Box("Col1"), combo_box1 = Combo Box(col1_unique_vals)),
	H List Box(Text Box("Col2"), combo_box2 = Combo Box(col2_unique_vals)), 

	H List Box(
		Button Box("OK", 

			v1 = combo_box1 << get selected;
			v2 = combo_box2 << get selected;

			win << close window;

		), 

//a cancel button that closes the window and doesn't store store the results
		Button Box("Cancel", win << close window)
	)
);

JMP Version - JMP Pro 14

[#2 · Accepted solution]

You need to add function (or script) to limit the options in second combo box. One option below (I think it should work with JMP14)

Names Default To Here(1);

v1 = "";
v2 = "";

dt = Open("$DOWNLOADS/JMP_Question.xlsx", Worksheets("Sheet1"), invisible);

Summarize(dt, uniqvals = by(:Column 1));

aa_options = Associative Array();
For(i = 1, i <= N Items(uniqvals), i++,
	col1_idx = dt << Get Rows Where(:Column 1 == uniqvals[i]);
	col2_vals = dt[col1_idx, "Column 2"];
	aa_options[uniqvals[i]] = col2_vals;
);



nw = New Window("Set the Value",
	Lineup Box(N Col(2),
		Text Box("Col1"), 
		cb1 = Combo Box(uniqvals, << set function(function({this},
			cb2_vals = aa_options[(this << get selected)];
			cb2 << Set Items(cb2_vals);
		)), << Set(1)
		),
		Text Box("Col2"), 
		cb2 = Combo Box(aa_options[uniqvals[1]])
	),
	H List Box(
		Button Box("OK", 
			v1 = cb1 << get selected;
			v2 = cb2 << get selected;
			nw << close window;
			show(v1, v2);
		),
		Button Box("Cancel", nw << close window)
	)
);

[#3]

Thanks Jarmo! You made it look easy.

It worked. 

[#4]

Hello Jarmo, 

Another trouble I have been having with the dialog box is taking a list as an input. 

If I use the following code. 

Names Default To Here(1);

v1 = "";
v2 = "";

dt = Open("JMP_Question.xlsx", Worksheets("Sheet1"), invisible);

Summarize(dt, uniqvals = by(:Column 1));

aa_options = Associative Array();
For(i = 1, i <= N Items(uniqvals), i++,
	col1_idx = dt << Get Rows Where(:Column 1 == uniqvals[i]);
	col2_vals = dt[col1_idx, "Column 2"];
	aa_options[uniqvals[i]] = col2_vals;
);



nw = New Window("Set the Value",
	Lineup Box(N Col(2),
		Text Box("Col1"),
		cb1 = Combo Box(
			uniqvals,
			<<set function(
				Function({this},
					cb2_vals = aa_options[(this << get selected)];
					cb2 << Set Items(cb2_vals);
				)
			),
			<<Set(1)
		),
		Text Box("Col2"),
		cb2 = Combo Box(aa_options[uniqvals[1]])
	), 

	Text Box("List select"),
	text_box = Text Edit Box({"list1", "list2", "list3"}), 

	box 1 = 

	H List Box(
		Button Box("OK",
			v1 = cb1 << get selected;
			v2 = cb2 << get selected;
			v3 = text_box << get text;
			nw << close window;
			Show(v1, v2, v3);

			New Window("Hello", String Col Box(v3[1]));


		),
		Button Box("Cancel", nw << close window)
	)
);

It is not taking the list as an input. Am I doing something wrong?

Thanks!

[#5]

Even if JMP lets you put the list into Text Box (not sure why, I think it should throw an error), it is still a string (JMP most likely performs conversion on it), so you cannot reference it with index directly.

You can either split it into list using Words (and maybe Substitute to remove extra characters such as brackets) or Parse() it into a list

Names Default To Here(1);

nw = new window("",
	tb = Text Edit Box({"list1", "list2", "list3"}), 
);

tb_txt = tb << get text;

show(tb_txt);
show(type(tb_txt));

show(Parse(tb << get text));
show(type(Parse(tb << get text)));

[#6]

Hello Jarmo, 

If I try to apply the solution to three columns, it works. However, I am not able to create a unique selection based on the first AND second columns. 

Names Default To Here(1);

v1 = "";
v2 = "";
v3 = "";

dt = Open("JMP_Question.xlsx", Worksheets("Sheet1"), invisible);

Summarize(dt, uniqvals_1 = by(:Column 1));
Summarize(dt, uniqvals_2 = by(:Column 2));
Summarize(dt, uniqvals_3 = by(:Column 2,:Column 3));



aa_options = Associative Array();
For(i = 1, i <= N Items(uniqvals_1), i++,
	col1_idx = dt << Get Rows Where(:Column 1 == uniqvals_1[i]);
	col2_vals = dt[col1_idx, "Column 2"];
	aa_options[uniqvals_1[i]] = col2_vals;
);

ab_options = Associative Array();
For(i = 1, i <= N Items(uniqvals_2), i++,
	col2_idx = dt << Get Rows Where(:Column 2 == uniqvals_2[i]);
	col3_vals = dt[col2_idx, "Column 3"];
	ab_options[uniqvals_2[i]] = col3_vals;
);

/*
ac_options = Associative Array();
For(i = 1, i <= N Items(uniqvals_1), i++,
	col1_idx = dt << Get Rows Where(:Column 1 == uniqvals_1[i]);
	col2_vals = dt[col1_idx, "Column 2"];
	
	For(j=1, j<=N Items(uniqvals_2),j++
	
		col2_idx = dt << Get Rows Where(:Column 2 == uniqvals_2[j]);
		col3_vals = dt[col2_idx, "Column 3"];
		ac_options[uniqvals_2[j]] = col3_vals;
		
	);
	
);
*/

print(ac_options);


nw = New Window("Set the Value",
	Lineup Box(N Col(2),
		Text Box("Col1"), 
		cb1 = Combo Box(uniqvals_1, << set function(function({this},
			
			cb2_vals = aa_options[(this << get selected)];
			cb2 << Set Items(cb2_vals);

		)), << Set(1)
		),
		
		Text Box("Col2"), 
		cb2 = Combo Box(uniqvals_2, << set function(function({this},
		
			cb3_vals = ab_options[(this << get selected)];
			cb3 << Set Items(cb3_vals);
			
		)), << Set(1)
		),
		
		Text Box("Col3"), 
		cb3 = Combo Box(ab_options[uniqvals_2[1]])
	),
	H List Box(
		Button Box("OK", 
			v1 = cb1 << get selected;
			v2 = cb2 << get selected;
			v3 = cb3 << get selected;
			nw << close window;
			show(v1, v2, v3);
		),
		Button Box("Cancel", nw << close window)
	)
);

The above code works. However, if you look at the attached images, I am not able to create unique lists for Col2 and Col3. 

There is probably a straightforward solution that I am missing.

Thanks!

[#7]

You could create new column which is combination of :Column 1 and :Column 2 and use that for the keys