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:

    1.png 2.png

     

    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.

     

    3.png

    Now we have the filter which is just Product ID.

    4.png 5.png

    If you filter on a Product ID this is your result:

    6.png

    Notice any row that has 23 in any month is displayed.

     

    Take a look at the data model:

    10.png

     

    There are 2 tables the DATA table and then a COMBO table.

     

    DATA:
    Load * inline [Product ID1, Product ID2, Product ID3, Product ID4,RecordID
    3,1,,,1
    28,,,,2
    30,23,29,26,3
    32,,,,4
    22,30,,,5
    25,29,,,6
    ,,,,7
    29,,,,8
    26,,,,9
    23,,,,10
    39,29,23,,11
    30,29,23,26,12
    23,22,,,13
    23,,,,14
    26,,,,15
    23,,,,16
    23,,,,17
    ,,,,18]
    ;


    COMBO:
    Load [Product ID1] as [Product ID],
    1
    as ordinal,
    RecNo() as RecordID
    resident DATA;

    Load [Product ID2] as [Product ID],
    2
    as ordinal,
    RecNo() as RecordID
    resident DATA;

    Load [Product ID3] as [Product ID],
    3
    as ordinal,
    RecNo() as RecordID
    resident DATA;

    Load [Product ID4] as [Product ID],
    4
    as ordinal,
    RecNo() as RecordID
    resident DATA;


    How does this work?

     

    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.


    1a.png

    2a.png

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

    =if(index(GetFieldSelections([Product ID]),[Product ID1])>0,RGB(227,239,251))

    changing [Product ID1] to the right product ID.