Search tables with multiple values

[lazzarjovvch74]

Hi,

I have two tables where some of the columns contain multiple values. This is the example of 2 tables:

Table 1 (Contacts)

ID	Name	Territory Interest	Product Type	Invest
1	Diana	Location 1, Location 2, Location 3	Type1, Type2	10000
2	Brad	Location 1	Type 1	2000
3	Nagel	All	Type 2	20000
4	Harold	All	Type1, Type2	4000
6	Brad	Location 2	Type1, Type2	10000
7	Dror	All	Type 3	8000
8	Harold	Location 3, Location 4	Type1, Type2, Type3	15000
9	Steven	Location 3	Type 2	10000
10	Brad	Location 4	Type3	2500

Table 2 (Products)

Product ID	Product Line	Product Type	Locations	Budget
1	Product Line 1	Type1	Location 2	$10,000
2	Product Line 2	Type2	Location 4	$15,000
3	Product Line 3	Type2	Location 3	$12,500
4	Product Line 4	Type1, Type2	Location 3	$8,000
5	Product Line 5	Type3	Location 1	$4,000

I want to create drop-down filters "Product Type" and "Locations" with unique values from these columns and when a user selects "Type2" (for an example), both tables should list records that contain "Type2" in the column "Product Type". I want to do the same with a selection based on the column "Location".

Thank you in advance!

[ibarrau · Accepted solution]

Hi. In ordert o get this you should duplicate some rows to expand those values. Go to edit queries. Right click on the column with commas, then split column -> by delimiter. Then open the advanced options and check for "Rows", remove the quotes and click accept. That will create a unique value for the column type for example.

The problem with this is that you will have problem with relationships if you do that for product because you are going to have duplicated ids. Then you may want to create a new combined column to have as the key unique column of the table; like product_id + type.

Hope this helps,

Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.