Searching and selecting a value across multiple columns with a filter.

I recently was asked how you can select a value across multiple columns with a filter.

My first reaction was you can’t do that. You can use global search and find all the places where it matches like if you were searching for a state you could do a global search and see matches in Customer Address, Shipping Address, Address 2, Vendor Address…. You can even hold down the control key and select the value from multiple fields but that’s a little awkward.

I started playing around with this and came up with a clever way where you can use a filter and select values across multiple columns.

Start by looking at this table box:

As you can see this is a simple table box brining in Product ID 1-4.

We also have a small straight table chart that uses Product ID as the dimension and a simple count([Product ID]) as the expression.

This works because we are using the row or rather RecNo() as the RecordID which links the 2 tables.

Product ID in the COMBO table is a concatenation of Product ID1, Product ID2, Product ID3, Product ID4. It is linked to the individual row in the DATA table by the Record ID so when you select a Product ID of 23 it returns all of the rows in the DATA table that contain 23 in Product ID1, Product ID2, Product ID3, Product ID4.

So there you have it by creating a table that concatenates all of the Product ID’s and stores the Recno as an ID you can filter and select all rows regardless of which of the 4 columns the value appears in.

//1/12/2017 I took it one step further by highlighting Product ID's that are selected.

The expression for the background color for Product ID1, Product ID2, Product ID3, Product ID4 is