7 Replies Latest reply: Oct 15, 2013 5:07 PM by ThereseS RSS

    Multiple column in LOV

    926190
      Dear community,

      I have been struggling with the issue that Apex wont allow me to display more than 1 value/column in a LOV/select-list.

      Is there a fix or workaround for that? I have been searching all over the internet, landed on a webpage with jQuery but I have no idea how to execute since I am a beginner at apex.

      Situation:

      I have a table with students containing columns: studentnumber, name, lastname.
      I want to create a LOV displaying all the 3 columns and return with studentnumber.

      what I tried in sql:

      select studentnumber, name, lastname as display_value, studentnumber as return_value
      from students
      order by 1

      But failed since you can give only 1 value @ display_value ( I gave 3 ).


      Thanks!

      Edited by: 923187 on 25-mrt-2012 7:53
        • 1. Re: Multiple column in LOV
          Mindmap
          Hi user,

          Pls, check this...

          http://apex-plugin.com/oracle-apex-plugins/dynamic-action-plugin/skillbuilders-modal-page_138.html
          • 2. Re: Multiple column in LOV
            926190
            Thanks so much!

            Actually your link did NOT work but it navigated me to a webpage called http://skillbuilders.com/oracle-apex/Application-Express-APEX-Consulting-Training.cfm?tab=plug-ins&click=super_lov.zip

            this tool fixed my problem and it works.


            IF anyone needs support on this, I will help!

            Edited by: 923187 on 25-mrt-2012 8:33
            • 3. Re: Multiple column in LOV
              166279
              Hi,

              I'm struggling with another LOV issue wanting to display multiple columns and passing those to fields in de detailed part of a master detail form.

              Any ideas
              • 4. Re: Multiple column in LOV
                unficyp
                This plugin is very nice indeed, but its defeats the purpose of "dont reinvent the wheel".
                Simply because you cannot use the "shared component" - "list of values" option.

                You have to enter your (multicolumn) query everytime you use more than 2 columns.
                A lot of work if you have, lets say, 20 lovs based on the same query...this is NOT an option...
                • 5. Re: Multiple column in LOV
                  eapro
                  You could return your query using a PL/SQL function:

                  List of values definition:
                  return call_to_function_returning_select;

                  That way you code it once and reuse it by calling the function.
                  • 6. Re: Multiple column in LOV
                    895126
                    i just concatenated the fields i needed together, in the display string....

                    SELECT DISTINCT TABLE_CODE||' '||CODE_DESCRIPTION D, TABLE_CODE R
                    FROM USR_TABLE_CODES
                    WHERE ACTUARIAL_TABLE_CODE_FLAG = 'Y'
                    ORDER BY TABLE_CODE
                    • 7. Re: Multiple column in LOV
                      ThereseS

                      Does anyone know how to get a set number of blanks to display in between the columns using the concatenated sql above.

                      The Code value and Description values don't line up from row to row.

                      I am  using similar  sql as above doing a simple Department/Department Description selection and it looks bad when the department code and department description have only one or no spaces between them.  It appears that extra spaces are removed and not shown. For example the Dept Code is defined as length VARCHAR2(7) however some of the data values are only 4 and no matter how I try to add extra spaces the description is not moved out and therefore not lined up from row to row.

                      I've tried adding extra spaces in the || '    ' || they seem to be removed. I've also tried using  RPAD to give it extra spaces but does not work:

                      select rpad(dept, 12, ' ') || dept_desc as display_value,

                       

                      3000 ACCRUED REVENUE

                      3000100 ACCRUED REVENUE

                      3000200 ACCRUED REVENUE

                      3010 ACCRUED REVENUE

                      3010100 ACCRUED REVENUE

                      301200 ACCRUED REVENUE

                       

                      This is how I want it to look;

                      3000          ACCRUED REVENUE

                      3000100     ACCRUED REVENUE

                      3000200     ACCRUED REVENUE

                      3010          ACCRUED REVENUE

                      3010100    ACCRUED REVENUE

                      301200      ACCRUED REVENUE

                       

                      I tried adding a character value in the RPAD to see if the RPAD works and it did work to fill in the spaces but I obviously don't want to display it that way

                      select rpad(dept, 12, 'X') || dept_desc as display_value,

                      dept as return_value

                      from department order by 1

                       

                      3000XXXXXXXXACCRUED REVENUE

                      3000100XXXXXACCRUED REVENUE

                      3000200XXXXXACCRUED REVENUE

                      3010XXXXXXXXACCRUED REVENUE

                      3010100XXXXXACCRUED REVENUE

                      301200XXXXXXACCRUED REVENUE

                       

                      I am using APEX 4.2 theme 24 this is in a DML form same issue with displayed as "Select List" and "Popup LOV".

                      Thanks for any suggestions, I checked out the Super LOV plugins from Skillbuilder , they look really cool but the appearance and style doesn't fit in with this app.