topic Re: How do I search for unique values in column and use in input window? in Discussions

How do I search for unique values in column and use in input window?

BeefSupreme — Wed, 07 Mar 2018 22:32:10 GMT

I have a table that looks something like this, what I would like to do is search the "Cup Color" column for unique values, then have a pop-up window for the user to input a corresponding flavor for each unique cup color...

Cup Color	Calories	Taste	Ranking	Tester
blue	140	4	2	1
green	80	7	1	4
yellow	200	2	3	6
purple	130	3	6	4
blue	140	3	5	2
blue	140	4	4	3
yellow	200	5	7	1

Once we have a flavor for each cup color, I would like to add a new column which is populated based on the the input that we got from the user.

Cup Color	Calories	Taste	Ranking	Tester	Flavor
blue	140	4	2	1	cherry
green	80	7	1	4	raspberry
yellow	200	2	3	6	chocolate
purple	130	3	6	4	really minty
blue	140	3	5	2	cherry
blue	140	4	4	3	cherry
yellow	200	5	7	1	chocolate

I'm still pretty new to JSL and have spent the last several hours going through forums looking for some guidance but can't seem to piece this together, a little help would be much appreciated.

Re: How do I search for unique values in column and use in input window?

txnelson — Wed, 07 Mar 2018 23:16:22 GMT

Welcome to the community.

First, I have a suggestion on how to speed up your learning of JSL.

Read the Scripting Guide!!!!

It is available at

Help==>Books==>Scripting Guide

Below is a barebones script to accomplish what you requested.

Names Default To Here( 1 );
dt = New Table( "Untitled 2",
	Add Rows( 7 ),
	New Column( "Cup Color",
		Character,
		"Nominal",
		Set Selected,
		Set Values( {"blue", "green", "yellow", "purple", "blue", "blue", "yellow"} )
	),
	New Column( "Calories",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [140, 80, 200, 130, 140, 140, 200] )
	),
	New Column( "Taste",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [4, 7, 2, 3, 3, 4, 5] )
	),
	New Column( "Ranking",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [2, 1, 3, 6, 5, 4, 7] )
	),
	New Column( "Tester",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 4, 6, 4, 2, 3, 1] )
	)
);

// Get the list of unique colors
Summarize( dt, colorList = by( :Cup Color ) );

// Initialize the flavor list and make it the same
// length as the colorList
flavorList = {};
For( i = 1, i <= N Items( colorList ), i++,
	Insert Into( flavorList, "" )
);

// Create the display window
nw = New Window( "Select Flavors",
	Text Box( "Enter in Flavors for each Color" ),
	tb = Table Box(
		String Col Box( "Colors", colorList ),
		scb = String Col Edit Box( "Flavors", flavorList )
	),
	Button Box( "OK",
		nw << close window;
		// Create the flavor table and update it to
		// the original table
		dt2 = New Table( "combined",
			New Column( "Cup Color", character, set values( colorList ) ),
			New Column( "Flavors", character, set values( flavorList ) )
		);
		dt << Update( With( dt2 ), Match Columns( :Cup Color = :Cup Color ) );
		close( dt2, nosave );
	)
);
// Set the String Col Edit Box action
scb << Set Function( Function( {this, which}, flavorList[which] = scb << get( which ) ) );

Re: How do I search for unique values in column and use in input window?

BeefSupreme — Wed, 07 Mar 2018 23:44:08 GMT

Perfect, thanks!

Re: How do I search for unique values in column and use in input window?

BeefSupreme — Thu, 08 Mar 2018 17:30:59 GMT

I actually need for my script to wait for the user input before progressing.

I found in the scripting guide that I can do this by using the modal message and thought that I got it added in correctly, but when I run the script I now the table doesn't update - log says "Name Unresolved: Button in access or evaluation of 'Button' , Button( 1 ) /*###*/"

// Get the list of unique colors
Summarize( dt, colorList = by( :Cup Color ) );

// Initialize the flavor list and make it the same
// length as the colorList
flavorList = {};
For( i = 1, i <= N Items( colorList ), i++,
	Insert Into( flavorList, "" )
);

// Create the display window
nw = New Window( "Select Flavors",
	<< Modal,
	Text Box( "Enter in Flavors for each Color" ),
	tb = Table Box(
		String Col Box( "Colors", colorList ),
		scb = String Col Edit Box( "Flavors", flavorList )
	),
	Button Box( "OK",
		nw << close window;
		// Create the flavor table and update it to
		// the original table
		dt2 = New Table( "combined",
			New Column( "Cup Color", character, set values( colorList ) ),
			New Column( "Flavors", character, set values( flavorList ) )
		);
		dt << Update( With( dt2 ), Match Columns( :Cup Color = :Cup Color ) );
		close( dt2, nosave );
	)
);
// Set the String Col Edit Box action
scb << Set Function( Function( {this, which}, flavorList[which] = scb << get( which ) ) );

Re: How do I search for unique values in column and use in input window?

txnelson — Thu, 08 Mar 2018 19:34:18 GMT

Using a Modal window is a very unique type of window. There are several rules that you must follow. One of which involves a Button Box with the text of "OK". It has a special function in a modal window. So you can use them, but you will need to study up on them, and do some practicing with them.

My preference is not to use Modal windows, but rather use Functions() or Expr() elements to encapsulate the code, keeping it from being executed until the script indicates it should be run. Below is a simple example of how to do that.

Names Default To Here( 1 );

// Get the list of unique colors
Summarize( dt, colorList = by( :Cup Color ) );

// Initialize the flavor list and make it the same
// length as the colorList
flavorList = {};
For( i = 1, i <= N Items( colorList ), i++,
	Insert Into( flavorList, "" )
);

// Create the display window
nw = New Window( "Select Flavors",
	//<< Modal,
	Text Box( "Enter in Flavors for each Color" ),
	tb = Table Box(
		String Col Box( "Colors", colorList ),
		scb = String Col Edit Box( "Flavors", flavorList )
	),
	Button Box( "OK",
		nw << close window;
		// Create the flavor table and update it to
		// the original table
		dt2 = New Table( "combined",
			New Column( "Cup Color", character, set values( colorList ) ),
			New Column( "Flavors", character, set values( flavorList ) )
		);
		dt << Update( With( dt2 ), Match Columns( :Cup Color = :Cup Color ) );
		close( dt2, nosave );
		runAdditionalCodePlease;
	)
);
// Set the String Col Edit Box action
scb << Set Function( Function( {this, which}, flavorList[which] = scb << get( which ) ) );

runAdditionalCodePlease = Expr(
	Dialog("This represents the kind of things that",
		"you may want to run after the data entry.",
		"By placing the code inside an Expr() or in",
		"a Function().  The code will be run only",
		"when executed within the code."
	)
);