Extract Expression Possible Values

[#1]

I have a scenario where I have a table of values and a separate field in a different table with a comma-separated field with any number of those values.

Example:

Fruit:

Apple

Orange

Peach

Pear

Supplier Table:

Supplier     Fruits

John          Apple,Orange

Fred          Peach

Joe           Pear,Apple

I need to have a listbox on Fruit so the users can select one or more fruits, and then apply that list of selected fruits to the "Fruits" field in the Supplier table to determine which suppliers are valid.

I'm most of the way there but can't figure out how to pull the values from a listbox driven by an expression and apply that to a filter on the Fruits field.

I've attached a simple qvw file that shows what I've done to get a dynamic list of value Fruits options, but need that help to get the last bit.

Any help or thoughts are much appreciated.

Mark.

[sunny_talwar]

Not sure if this is what you are looking for? But have a look (PFA)

Best,

Sunny

[sunny_talwar]

Updated file (PFA)

[maxgro · Accepted solution]

add a link table

Fruit:

load * inline [

Fruit

Apple

Orange

Peach

Pear

];

SupplierTable:

load * inline [

Supplier ;    Fruits

John     ;     Apple,Orange

Fred     ;     Peach

Joe      ;     Pear,Apple

] (delimiter is ';');

Link:

load Supplier, subfield(Fruits, ',') as Fruit Resident SupplierTable;

[#5]

I like this and it shows me the suppliers, but I still don't see how to pull the valid fruits list (those with 'Grape' in them), and apply that as a filter to the Suppliers table.

This is a simplified example as the real example has about 15 additional fields which are used for various analytics and so I need to be able to pick from the fruit listbox and apply the filter to the supplier table.  Thus, the "Valid Fruits" listbox which is expression driven.  What I need to do it extract the values from that and apply those as a filter to the Suppliers table so it gets filtered as well (just as the Fruit table does with the Fruit listbox).

Seeing the information is great, but extracting it and applying it as a filter is where I am stuck.

Thanks.

[#6]

Works like a charm.  I think I went through a lot of work I didn't need to in calculating which fields met the criteria.  I hadn't thought about creating a link table.

Much thanks!