Forum Discussion
Search tables with multiple values
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!
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.
1 Reply
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.