2 Replies Latest reply: Jul 13, 2016 10:34 AM by Piet Hein van der Stigchel RSS

    Combine more than one column for list box?

    Ian McDevitt

      I basically have my data coming from a database that has 3 fields that i want to populate into a list box, but show the values only once...

       

      example data

       

      Column1

      First Choice

      Second Choice

      Third Choice

       

      Column 2

      Second Choice

       

      Column 3

      First Choice

      Third Choice

      Fifth Choice

       

      i would want the list box to show each value only once, even if it duplicated across the columns, like so:

       

      First Choice

      Second Choice

      Third Choice

      Fifth Choice

        • Re: Combine more than one column for list box?
          Andrew Walker

          Hi Ian,

                         Rather than a list box a search object is maybe what you are looking for. Create a search object and add the three fields you want to search on to the list of selected fields or if there are only three fields then just pick the Search In All Fields option.

           

          Good luck

           

          Andrew

          • Re: Combine more than one column for list box?
            Piet Hein van der Stigchel

            When there is either one value in column 1 or column2 or 3 ....You could use an if statement and load them into one field.

             

            load *, if(len(Column1)>0,Column1,if(len(Column2)>0,Column2,Column3)) as Choice;

            SQL Select * from Database;

             

            When there can be values in all three fields for the same record, you could use something like the following in your load script: (see crosstable in the help)

             

            Database:

            Load * Inline [Id,Column1,Column2,Column3

            1,First Choice,Second Choice,First Choice

            2,Second Choice,,Third Choice

            3,Third Choice,,Fifth Choice

            ];

             

            ListBox:

            CrossTable(Origin,Choice,1) load

            Id,

            Column1,

            Column2,

            Column3

            resident Database;