List available selection options?

[#1]

I have a sheet with several list boxes.  Visually I can see when I select a value in one box, how it impacts another box (unavailable options are greyed out).

Is there a way to determine the available options for a given field based on the selections placed on other fields?

I've used triggers to assign filters based on identical but differently named fields (using GetFieldSelections) but now would like to determine what options are available for a given field based on other filters being set.

Is there a way to do this?

Thank you.

[MK_QSL]

You can create all list box by using below type Expressions instead of directly selecting fieldname...

For Year List Box

Aggr(Year,Year)

For Month List Box

Aggr(Month, Month)

Now when you select any Year from Year List Box, it will only show you the associated Month and all other non-associated months would be hidden...

[JonnyPoole]

there is something called 'associative search' that is worth taking a look at.

in the caption of a list box is an icon, and if you press it , you get a search field for the list box.

if you hit the >> within the seach text you get an expanded search box which means 'associative search'. You can then search fields outside the list box and see how that will filter the values in the current list box.  kind of like an indirect search.

its worth taking a look just to know its there.

[#4]

Let me rephrase this.

Is there a way, programmatically, to determine available options?  Let me explain.

I have two data sets, Summary and Detail.  They are related but not so far as QV is concerned as I have deliberately named the fields differently.  I need to be able to treat each independently.

Thus in the Summary Set I have ReportYear and in the Detail Set I have Report_Year.  As you might imagine, the detail data set has more dimensional fields available to it - one of those is Product_Family. 

The visible filters are based on the Detail data set.  When they select a value in Product_Family, it automatically restricts the values in the Report_Year field in the same dataset.  However, would I would like to do is determine what fields are available in Report_Year so I can filter my summary data set the same way.

I know if I do an actual selection on Report_Year, I can grab the selected values and set the same filters in ReportYear - that is in place.  What I now need to do is if in the Detail table, they set an option that is NOT in the summary set but impacts the overall available value set in the summary data set, how do I grab the "available" values from the detail set so that I can set the appropriate filters in the summary set?

Perhaps an example:

Summary Set

ReportYear     Unit_Sales

2011               5000

2012               5500

2013               7000

2014               5800

Detail Set

Report_Year     Product_Family     Unit_Sales

2011               Type A                    3000

2011               Type B                    2000

2012               Type A                    3200

2012               Type B                    2300

2013               Type A                    2800

2013               Type B                    2400

2013               Type C                    1800

2014               Type A                    2100

2014               Type B                    1900

2014               Type C                    1800

When the user selects Type C from Product Family listbox, I need to be able to programmatically see that only 2013 and 2014 are valid years and apply that as a filter in the Summary data set.  Be able to see the available values for Report_Year so I can set those as filters for ReportYear (although they are not set as explicit filters for Report_Year but rather are implied filters based on other filters within the data set).

Make any more sense?

Thanks.

[#5]

This does not address the specific need I have currently but I just played with that and like it a lot.  Will very likely use in the future.

[MK_QSL]

As, I already replied in my first answer,,,,create a Report_Year list box with below expression

Aggr(Report_Year,Report_Year)

[#7]

Thank you Jonathan.  That is something of which I was not aware and know I'll be able to use.  Doesn't quite address my immediate need but is good to know and will get used.

[#8]

Hi Manish,

I can see that by creating the listbox as Aggr(Report_Year, Report_Year) it will show me only those values which are valid.

What I need to do is be able to grab those two values and set them as filters on my ReportYear field in the other dataset.  How do I programmatically grab those two values as they are not selected values and I cannot use the GetFieldSelections() function?

For curiosity sake, I tried mixing the two fields and setting up a listbox as Aggr(ReportYear, Report_Year) to see if they would intermix.  I results in a blank listbox.

Thanks!

[MK_QSL · Accepted solution]

=Concat(Aggr(Report_Year,Report_Year),',')

[#10]

Perfect!  Thank you!