10 Replies Latest reply on Oct 16, 2017 12:58 PM by J.col

    Lov with several display value

    J.col

      Hello everyone,

       

      I'm working on apex 5.1.1

       

      I know this is not possible to display several value in a List of Value without plug-in.

       

      So I tried to concatenate several value with whitespaces between the datas.

      select rpad(empno, 10, ' ') || ' ' || rpad(ename, 20, ' ') || ' ' || rpad(sal, 10, ' ')

      from emp

       

      It works fine on SQLDeveloper, but not on Apex LoV.

      I guess this is because the page is rendered as HTML page, and in HTML several whitespaces are replaced by only one.

       

      I tried with rpad(empno, 10, ' ') but it doesn't work, the ' ' is displayed as text in my LoV.

      I also wanted to try with CSS using white-space:pre but I don't know where and how to implemante this.

       

      Do you know if there is any way to do what I want please ?

      If no, do you know any good plug-in ? I would prefer to not use plug-in, but if I don't have any choice...

       

      Thanks in advance.

        • 1. Re: Lov with several display value
          David Hernán

          Hi

           

          Did you try remove RPAD and use ALIAS ??

           

          Like this..

           

          SELECT  (FIELD1 || '  ' || FIELD2 || ' - ' ||  FIELD3) NICK

               FROM TABLE

          • 2. Re: Lov with several display value
            Aneesh Vamsi

            I am not sure if this is all you are looking for :

             

            For displaying several values using LOVs ( Pop up LOV or Query based Select LOV ) the syntax would be typically like this:

            SELECT COLUMN_NAME D, COLUMN_NAME R FROM TABLE_NAME 
            

             

            Where D stands for Display Value and R is for Return Value

             

            You could concatenate several column values into one field and have them displayed as one value. However the value could only be stored into one column into a table.

             

            SELECT EMPNO || ' - ' || ENAME || ' - '|| SAL as D, ENAME as R FROM EMP
            

             

             

            Hope this helps.

            • 3. Re: Lov with several display value
              fac586

              J.col wrote:

               

              I'm working on apex 5.1.1

               

              I know this is not possible to display several value in a List of Value without plug-in.

               

              So I tried to concatenate several value with whitespaces between the datas.

              select rpad(empno, 10, ' ') || ' ' || rpad(ename, 20, ' ') || ' ' || rpad(sal, 10, ' ')

              from emp

               

              It works fine on SQLDeveloper, but not on Apex LoV.

              I guess this is because the page is rendered as HTML page, and in HTML several whitespaces are replaced by only one.

               

              I tried with rpad(empno, 10, '') but it doesn't work, the '' is displayed as text in my LoV.

              I also wanted to try with CSS using white-space:pre but I don't know where and how to implemante this.

               

              Do you know if there is any way to do what I want please ?

              If no, do you know any good plug-in ? I would prefer to not use plug-in, but if I don't have any choice...

              Long before plug-ins existed we used pop-up report pages for this.

              • 4. Re: Lov with several display value
                J.col

                I already tried with Alias and with the concatenation separated by a '-'

                 

                But in case of columns without the same lenght, the rendered is awfull, not readable.

                 

                So I wanted to separate the columns, that's why I wanted to use rpad with whitespaces.

                 

                This is not clean (specially for KING line) :

                 

                select empno ||' - '||ename || ' - ' || nvl(mgr, 0) || ' - ' || sal
                from emp;
                
                EMPNO||'-'||ENAME||'-'||NVL(MGR,0)||'-'||SAL                                                                                             
                -------------------------------------------------------------------------------------------------------------------------------------------
                7369 - SMITH - 7902 - 800                                                                                                                 
                7499 - ALLEN - 7698 - 1600                                                                                                                 
                7521 - WARD - 7698 - 1250                                                                                                                 
                7566 - JONES - 7839 - 2975                                                                                                                 
                7654 - MARTIN - 7698 - 1250                                                                                                               
                7698 - BLAKE - 7839 - 2850                                                                                                                 
                7782 - CLARK - 7839 - 2450                                                                                                                 
                7788 - SCOTT - 7566 - 3000                                                                                                                 
                7839 - KING - 0 - 5000                                                                                                                     
                7844 - TURNER - 7698 - 1500                                                                                                               
                7876 - ADAMS - 7788 - 1100                                                                                                                 
                7900 - JAMES - 7698 - 950                                                                                                                 
                7902 - FORD - 7566 - 3000                                                                                                                 
                7934 - MILLER - 7782 - 1300
                

                 

                But this one is clean :

                 

                RPAD(EMPNO,10,'')||''||RPAD(ENAME,20,'')||''||RPAD(NVL(MGR,0),10,'')||''||RPAD(SAL,10,'')
                -----------------------------------------------------------------------------------------
                7369       SMITH                7902       800                                            
                7499       ALLEN                7698       1600                                           
                7521       WARD                 7698       1250                                           
                7566       JONES                7839       2975                                           
                7654       MARTIN               7698       1250                                           
                7698       BLAKE                7839       2850                                           
                7782       CLARK                7839       2450                                           
                7788       SCOTT                7566       3000                                           
                7839       KING                 0          5000                                           
                7844       TURNER               7698       1500                                           
                7876       ADAMS                7788       1100                                           
                7900       JAMES                7698       950                                            
                7902       FORD                 7566       3000                                           
                7934       MILLER               7782       1300
                

                 

                So my question is : how to force Apex to not remove several whitespaces ?

                 

                fac586 do you have an example please ?

                • 5. Re: Lov with several display value
                  fac586

                  J.col wrote:

                   

                  I already tried with Alias and with the concatenation separated by a '-'

                   

                  But in case of columns without the same lenght, the rendered is awfull, not readable.

                   

                  So I wanted to separate the columns, that's why I wanted to use rpad with whitespaces.

                   

                  This is not clean (specially for KING line) :

                  1. selectempno||'-'||ename||'-'||nvl(mgr,0)||'-'||sal
                  2. fromemp;
                  3. EMPNO||'-'||ENAME||'-'||NVL(MGR,0)||'-'||SAL
                  4. -------------------------------------------------------------------------------------------------------------------------------------------
                  5. 7369-SMITH-7902-800
                  6. 7499-ALLEN-7698-1600
                  7. 7521-WARD-7698-1250
                  8. 7566-JONES-7839-2975
                  9. 7654-MARTIN-7698-1250
                  10. 7698-BLAKE-7839-2850
                  11. 7782-CLARK-7839-2450
                  12. 7788-SCOTT-7566-3000
                  13. 7839-KING-0-5000
                  14. 7844-TURNER-7698-1500
                  15. 7876-ADAMS-7788-1100
                  16. 7900-JAMES-7698-950
                  17. 7902-FORD-7566-3000
                  18. 7934-MILLER-7782-1300

                  But this one is clean :

                  And unsemantic.

                   

                  1. RPAD(EMPNO,10,'')||''||RPAD(ENAME,20,'')||''||RPAD(NVL(MGR,0),10,'')||''||RPAD(SAL,10,'')
                  2. -----------------------------------------------------------------------------------------
                  3. 7369SMITH7902800
                  4. 7499ALLEN76981600
                  5. 7521WARD76981250
                  6. 7566JONES78392975
                  7. 7654MARTIN76981250
                  8. 7698BLAKE78392850
                  9. 7782CLARK78392450
                  10. 7788SCOTT75663000
                  11. 7839KING05000
                  12. 7844TURNER76981500
                  13. 7876ADAMS77881100
                  14. 7900JAMES7698950
                  15. 7902FORD75663000
                  16. 7934MILLER77821300

                  So my question is : how to force Apex to not remove several whitespaces ?

                   

                  fac586 do you have an example please ?

                   

                  No, but if you create one I'll look at it.

                   

                  It's not difficult. Create a pop-up modal dialog page with a report region based on the "LOV" query. Choose the report type based on the number and complexity of the options that will be presented. Add a selection/close dialog mechanism using dynamic actions. If you break down the requirements into individual steps you should be able to find the implementation details by searching the forum.

                  • 6. Re: Lov with several display value
                    Q_STEPHENSON

                    Hi J.col

                     

                    Try the following.  It works for me in a popup LOV.  In my example I also gave each section of the record a different colour. Note the spacing between the different parts of the record.

                     

                    I didn't know if you want a return value so I just returned the empno as part of the example. 

                     

                    Write your query so is looks like the following:

                     

                    select -- this just sets the mode.

                        APEX_ESCAPE.HTML (

                        null)

                        d, null r

                    from dual

                    where 1=2

                    union all

                    select '<div style="color:blue;display:inline-block;padding-right:5em">'||empno||'</div>'

                         ||'<div style="color:red;display:inline-block;min-width:200px">'||ename||'</div>'

                         ||'<div style="color:green;display:inline-block;padding-right:5em">'||sal||'</div>' d,

                         empno r

                    --rpad(empno, 10, ' ') || ' ' || rpad(ename, 20, ' ') || ' ' || rpad(sal, 10, ' ')

                    from emp

                     

                    The result is the folloing screen shot:

                    Hope that helps.

                    Cheers

                    Q

                    • 7. Re: Lov with several display value
                      sect55

                      Dan McGhan wrote a blog on who to do a similar LOV. Click on the link below to view the blog.

                       

                      Dan McGhan's Oracle APEX Blog: Tutorial: Creating a Component Similar to Super LOV Using Native Components

                       

                      Robert

                      • 8. Re: Lov with several display value
                        -Max-

                        Hello

                         

                        As you noticed APEX is replacing/removing all extra spaces.

                         

                        If you really need to do it using a select list item here's what you can do.

                        Instead of padding with a space is to pad with a non breakable space.

                        The easiest way to do that is to reference it using it's Unicode code using unistr('\00A0')

                         

                        The query would look like this:

                        select val display, val return 
                          from (select rpad(empno, 10, unistr('\00A0')) || ' ' || rpad(ename, 20, unistr('\00A0')) || ' ' || rpad(sal, 10, unistr('\00A0')) val
                                  from emp)
                        

                         

                        With that, you will have all the "spaces" displayed.

                        But what you'll soon notice is that the LOVs are not using a monospaced font (where all characters have the same width);

                         

                        Now what you need to do is override the CSS font-family property for that LOV (I suggest you do it using a class so that you could do the same with other LOVs).

                        .monospacedFontList option {
                          font-family: "Courier New", Courier, monospace;
                        }
                        

                         

                        The only thing left for you to do is add the "monospacedFontList" class to your select list item.

                         

                        Regards

                        Max

                        • 9. Re: Lov with several display value
                          fac586

                          As stated above the problem with all of the seductively simple proposals involving concatenation and non-breaking padding is that of semantics: making the actual meaning of the data involved clearly accessible to all users.

                           

                          Attempts to create visible spatial relationships between data through the alignment of pseudo-columns convey nothing to users of assistive technologies like screen readers and refreshable Braille displays for whom all white space is equivalent. People with learning disabilities and dyslexia may also struggle to comprehend what the combination of values and white space is intended to mean.

                           

                          In contrast, the table mark-up used in the report-based approach I suggested and described in Dan's article is accessible to technologies used by the visually impaired, the ability to apply proper visual formatting is beneficial to those with learning and cognitive difficulties, and is overall a much more professional-looking and usable solution.

                          • 10. Re: Lov with several display value
                            J.col

                            Thanks for all your answers, you gave me a lot of options.

                            I tested them and it's perfect.