Creating 2nd list box based on 1st list box selections

[VivenReddy]

I have 2 list boxes. I need the 2nd list box to only list values that have been selected in the 1st list box. When no values are selected in 1st list box then both list boxes should display all values. In the example below, since Honda, Nissan and Toyota have been selected in 1st list box, they should be the only values available in 2nd list box - all 3 values should be white (unselected). The user should be able to make a selection in the 2nd list box which would not affect the values in 1st list box.

[edwin]

it sounds like you want the user to select a set and subset of the selection.  You will need to have 3 sets of the same data, the first is for users to select, then you need the other two sets to be independent of the first set so that when the second selection is made it does not affect the first.  the other two sets need to be associated.  you can use alternate states as well but this is simpler

i added a seq so that you want the second selection to show at top

[edwin]

i have a trigger that selects the 2nd field and since the 3rd field is associated it will be selectable (white) then in the list box i hid excluded entries

[VivenReddy]

Thanks for your response Edwin.

Since my use case will have hundreds of thousands to many millions of values in this field, I would be hesitant to duplicate this data set so many times.

Can we explore a solution using triggers?

[edwin]

i think you will definitely use triggers as you cant use association.  also are you saying you will have a list that is a million long?  or are you saying your fact table runs up to millions?  i wasnt suggesting to replicate your whole table - just the fields you want the user to select.  so to build the 2nd table:

noconcatenate
load distinct CARS as CARS2, CARS as CARS3 resident ALLCARS;

[VivenReddy]

Yes the fact table will have millions of rows but the relevant field could have up to 500,000 distinct values.

Previously I mistakenly mentioned that I wanted a solution using triggers. What I meant to say was that I preferred a solution using alternate states.

[yassinemhadhbi]

Good morning,

Have you tried using the hide excluded functionality of ListBox Object ? 

[VivenReddy]

Sorry @yassinemhadhbi that does not meet my requirement. Please read my initial post again.

[edwin]

here is a solution that uses alternate states.  unfortunately you need to at least create a second copy.  to address the requirement that the second list shows white instead of green (bec if the values are selected it will be green not white).  the second copy allows it to show white

you need to define an alt state then set the 2nd list as the alt state.  add trigger that sets the selection in the alt state.

[edwin · Accepted solution]