13 Replies Latest reply: Mar 18, 2014 12:19 PM by Mike Kutz RSS

    Paste from spreadsheet searchbox

    Doolius


      Problem: Users need to be able to copy values from one column in a spreadsheet, paste them into a textbox, and search for those values without having to format the data.

       

      For my tabular form in my applicaion I currently have a search box (List Manager Type) that users can input multiple values for one column and search for them. However, they either have to be added to the search box 1 by 1, clicking "add" each time. OR they can paste multiple values in at one time, but have to go through and insert a comma in between each value. 

      The users want to be able to just copy the values from our spreadsheet and paste them in instead of insterting one by one.

       

      Is there a way to allow the users to paste information into a text box and when "search" is clicked it searches line by line? (like shown below)

      1

      2

      3

      4

      5

      I'm thinking something along the lines of a textarea to paste into, but I'm not quite sure of the code to make the search line by line without having the user insert commas after each one, or the code to link it to the search button/Tabular Report.

       

      Steven

        • 1. Re: Paste from spreadsheet searchbox
          Mike Kutz

          You have a "varying IN list" problem.

          See Tom's blog for "9iR2 and above" on how to write your SQL statement

          The Tom Kyte Blog: Varying in lists...

           

          The "text area" is nothing more then a "return line separated value".

          It is identical to a "comma separated value" (CSV), but, instead of using a comma, you are using a return line character ... chr(10)

          Basically, wherever you see ',' in Tom's code, you replace it with chr(10)

           

          MK

          • 2. Re: Paste from spreadsheet searchbox
            Doolius

            Would that be like me wanting to:

            select * from pet where type in ('dog','cat')

            select * from pet where color in('brown','black')

             

            If so, that's not what I am wanting. I am wanting only:

            select * from pet where type in ('dog','cat','pig', etc)

             

            But the way it is set up now, when my user is trying to search for multiple values in the same column they can either (from a drop down) choose 'type' and a select list will appear with all of the current values, or they can choose 'multiple type search' and a textbox will appear and they can type in 'dog' (click add) 'cat' (click add) 'pig' (click add) or they can type in 'dog, cat, pig' (click add).

             

            I want the user to be able to copy and paste from an excel sheet:

            dog

            cat

            pig

             

            and be able to click search without having to do anything else.

             

            But if that's not the case, am I on the right track with this sql?


            I am unsure what the below 3 lines are doing:

             

            select level l
              from dual
              connect by level <= 5;

             

            with data
              as
              (
              select    trim( substr (txt,
              instr (txt, chr(10), 1, level  ) + 1,
              instr (txt, chr(10), 1, level+1)
              - instr (txt, chr(10), 1, level) -1 ) )
              as token
              from (select chr(10)||:txt||chr(10) txt
              from dual)
              connect by level <=
              length(:txt)-length(replace(:txt,chr(10),''))+1
              )
              select *
              from pet
              where type in (select * from data);

             

            Do I need to change the above 2 lines from all_users and username to pet and type? or leave them as all_users and username.

             

            Would this code be associated with the search button? textarea?

            • 3. Re: Paste from spreadsheet searchbox
              Doolius

              Is there a way to basically let the user input their values like above, and instead of searching from there, the user can click a button to parse the values and the commas would be automatically added where needed, so they can paste it into the list manager form to then search from?

               

              ex.

              INPUT

              123

              456

              789

               

              they then click "parse"

               

              OUTPUT

              123,456,789

               

              and they can copy that and paste into list manager to 'add'?

               

              This would be an extra step for the user, but oh well.

              • 4. Re: Paste from spreadsheet searchbox
                Mike Kutz

                Demo App

                Take a look at this sample page:

                https://apex.oracle.com/pls/apex/f?p=70399:14

                 

                name/password:  demo/demo

                 

                The Text Area limits the search for ENAME in the EMP table

                 

                So, if you copy paste:

                SCOTT

                TURNER

                 

                Into the Text Area and click 'REFRESH', you'll get just the rows for SCOTT and TURNER.

                If you clear it out and click 'REFRESH', you'll get back all the data.

                 

                If this is what you want, details are below.

                If not, create your own account on http://apex.oracle.com/ with an example layout and then describe in details.

                (please create a 'developer' account so you don't have to give your email address to all the spam-bots that read this forum)

                 

                Details of Implementation

                The SQL Statement that you posted is the one that you use for the Report.

                However, you'll need to do some minor variations to it.

                • change the bind variables to be the name of your Text Area Item  (eg  :txt  --> :P14_SEARCH_INPUT)
                • slight modification in the "select * from data" to remove "carriage return" character from the input.  (ie    replace( token, chr(13), '' )
                • added an "OR :P14_SEARCH_INPUT IS NULL" to the WHERE clause -- this causes the WHERE clause to be the equivalent of "WHERE 1=1" when the Text Area Item is empty
                • (oh... almost forgot) I added an upper() function to make the search 'case insensitive'.  If you're dealing with numbers, you the to_number() function.

                 

                The actual SQL statement I used for the report in the demo app is:

                with data
                  as
                  (
                  select    trim( substr (txt,
                  instr (txt, chr(10), 1, level  ) + 1,
                  instr (txt, chr(10), 1, level+1)
                  - instr (txt, chr(10), 1, level) -1 ) )
                  as token
                  from (select chr(10)||:P14_SEARCH_INPUT||chr(10) txt
                  from dual)
                  connect by level <=
                  length(:P14_SEARCH_INPUT)-length(replace(:P14_SEARCH_INPUT,chr(10),''))+1
                  )
                  select *
                  from emp
                  where ename in (select upper( replace( token, chr(13), '') ) from data)
                     or :P14_SEARCH_INPUT is null
                

                 

                MK

                • 5. Re: Paste from spreadsheet searchbox
                  Doolius

                  Yes that works how I need it to! Thank you very much.

                   

                  If I made it search for columns that the values might have spaces in them:

                  Ex.

                  Group one

                  Group two

                   

                  would it work the same way? or would I need to change something?

                   

                  Steven

                  • 6. Re: Paste from spreadsheet searchbox
                    Mike Kutz

                    It should work as-is.

                    The 'split' is done at the "end-of-line" character and, therefore, won't affect anything else.

                     

                    Well... except.... The trim() function is in use.

                    The trim() function will remove any preceding/trailing spaces for each entry.

                    so ' bob ' becomes 'bob' (no spaces at beginning or end)

                    but 'bobby sue' remain 'bobby sue'.

                     

                    don't forget:  I did add the UPPER() function, which will actually make it 'BOBBY SUE'

                    (just remove it if you need case sensitive searches.)

                    • 7. Re: Paste from spreadsheet searchbox
                      Doolius

                      ok thanks!

                       

                      I probably wouldn't have a job for very long if it wasn't for you and the rest of the helpful guys on here!

                       

                      Steven

                      • 8. Re: Paste from spreadsheet searchbox
                        Doolius

                        I'm having a little trouble connecting the button to the tabular report.


                        I made the button and pasted the edited code into the "Button Request" and changed the "Button Request Source Type" to "SQL Query (return single value)"

                        but it isn't refreshing the form when I click it.


                        I can create a new "region button" (with the other 'search' and 'help' buttons) and it will submit the page correctly(with a normal search) but I do not know where to add the edited code to to make it search on the specific text area.

                         

                        If it would be easier than explaining (and possible), you could attach the application export file and I can look at how you did it through that.

                         

                        Steven

                        • 9. Re: Paste from spreadsheet searchbox
                          Mike Kutz

                          The "magic" is in the SQL for the Report... not the button.

                           

                          The button is a simple "submit" button with all default values.  nothing special.

                          The bottom region is an Interactive Report region.... whose "Source" is the SQL statement that I posted.

                           

                          The best way I can describe it:

                          The "Source" for the report contains a "pre-filter" that causes the results to be filtered prior to APEX getting the data(*).

                          (ie the WHERE clause)

                           

                          http://apex.oracle.com/

                          workspace:  mikekutz_test

                          name/passwd:  developer/trymeout

                          application:  Basic Stuff

                          page: 14

                           

                          I have many other things in that app that might confuse you.

                          Just concentrate/export Page 14.

                           

                          MK

                           

                          (*)technical information

                          In reality:  APEX takes the 'filters' that an end-user desires and formats it into something that the database understands.

                          APEX doesn't do the filtering.  The database does.

                          • 10. Re: Paste from spreadsheet searchbox
                            Doolius

                            Okay. I'm going to have to make another report. (or figure out how to edit the existing one)

                             

                            Below is the where clause of the existing report query: (hard for me to understand it so I don't expect you to be able to understand it. So it might be better for me to copy the page and then when the user clicks the 'refresh' button, redirect them to the new page with the new report) There's 4 columns they want to have this type of search feature on and it seems like it would be a waste to have to make a new page just for each separate report.

                             

                               WHERE pod in (:P1_PORT, :P2_PORT) and

                            (((:SEARCH_TYPE = 'PCFN') AND (pcfn in (select * from

                            table(get_filter_vals(:SEARCH_VALUES))))) OR

                               ((:SEARCH_TYPE = 'VOYDOC') AND (dod_voydoc in (select * from

                            table(get_filter_vals(:SEARCH_VALUES))))) OR

                               ((:SEARCH_TYPE = 'TRAIN NUMBER') AND (train_number in (select * from

                            table(get_filter_vals(:SEARCH_VALUES))))) OR

                               ((:SEARCH_TYPE = 'CONTAINER NUMBER') AND (container_number in (select * from table(get_filter_vals(:SEARCH_VALUES))))) OR

                            -------- When the above 4 are selected from the search type list, a select list box pops up and they can select individual values to filter from

                               ((:SEARCH_TYPE = 'CONTAINER SEARCH BOX') AND (container_number in (select * from table(get_filter_vals(:SEARCH_CONTAINER_NUMBERS, :SEARCH_TYPE))))) OR

                               ((:SEARCH_TYPE = 'TCN SEARCH BOX') AND (tcn in (select * from table(get_filter_vals(:SEARCH_TCNS, :SEARCH_TYPE))))) OR

                            -------- When the above are selected from the search type list, a list manager box pops up where the user an 'add' multiple values and then search/filter the tabular form

                               ((:SEARCH_TYPE = 'BOOKING NUMBER') AND (booking_number in (select * from table(get_filter_vals(:SEARCH_VALUES))))) OR

                               ((:SEARCH_TYPE = 'ROUTE INDICATOR') AND (route_ind in (select * from table(get_filter_vals(:SEARCH_VALUES))))) OR

                               ((:SEARCH_TYPE = 'CHY NAME') AND (chy_name in (select * from table(get_filter_vals(:SEARCH_VALUES))))) OR

                               ((:SEARCH_TYPE = 'GROUP IDENTIFIER 1') AND (group_identifier_1 in (select * from table(get_filter_vals(:SEARCH_VALUES))))) )

                            -------- When the above 4 are selected from the search type list, a select list box pops up and they can select individual values to filter from

                             

                            The bold part of the WHERE part of the query is what I think I'll need to change. So I changed it to:

                             

                            ((:SEARCH_TYPE = 'TCN SEARCH BOX') AND (  ((:SEARCH_TYPE = 'TCN SEARCH BOX') AND

                                 (tcn in( with data as(

                                   select    trim( substr (txt,

                                   instr (txt, chr(10), 1, level  ) + 1,

                                   instr (txt, chr(10), 1, level+1)

                                   - instr (txt, chr(10), 1, level) -1 ) )

                                   as token

                                   from (select chr(10)||:SEARCH_TCNS||chr(10) txt

                                   from dual)

                                   connect by level <=

                                   length(:SEARCH_TCNS)-length(replace(:SEARCH_TCNS,chr(10),''))+1

                                   )

                                   select *

                                   from oef_itv_ndn

                                   where TCN in (select upper( replace( token, chr(13), '') ) from data)

                                      or :SEARCH_TCNS is null)))))

                             

                            Which didn't work.

                             

                            Thoughts?

                             

                            Steven

                            • 11. Re: Paste from spreadsheet searchbox
                              Mike Kutz

                              STOP

                              The additional information you gave us totally changes your answer.

                              I really wish you had given that SQL in the original post.

                               

                              REREAD THE BLOG

                              Please, re-read Tom's entire blog.

                              The Tom Kyte Blog: Varying in lists...

                              This time, please note Tom's 8i solution and notice that it matches the following, heavily repeated, SQL in your current statement

                              <some column name> in (select * from table(get_filter_vals(:SEARCH_CONTAINER_NUMBERS)) )

                              ie someone solved the 'varying IN list' problem using the 8i solution.

                              If you want the function get_filter_vals() to accept "new line separated values", then you should have the person responsible for modifying that code (eg the DBA) modify the function to accept "new line separated values" also.

                               

                              INTERIM SOLUTION

                              In the mean time, you can adjust the input to the function by doing in-line string replacement

                              from:  get_filter_vals(:SEARCH_CONTAINER_NUMBERS))

                              to:  get_filter_vals( replace( replace(:SEARCH_CONTAINER_NUMBERS, chr(13),''), chr(10),','))

                              The inner most REPLACE() will replace CR with NULL

                              The outer most REPLACE() will replace LF with ','

                               

                              -- AND MAKE SURE YOU HAVE A BACKUP COPY OF THE ORIGINAL SQL STATEMENT!!!

                               

                              MK

                               

                              PS - Please pickup a book on SQL and read it.

                              APEX is just a way to create a web based interface to the database.

                              You really need to know SQL when developing APEX apps.

                              • 12. Re: Paste from spreadsheet searchbox
                                Doolius

                                After much thought I believe I've figured a way around it.

                                 

                                I created a new tabular form for each column I needed to have the new search for and made it conditional to only display when the 'search_type' equals that specific search type.

                                 

                                More work on my end but I think it'll be easier in the long run because of what I know now.

                                 

                                Steven

                                • 13. Re: Paste from spreadsheet searchbox
                                  Mike Kutz

                                  The best place to start learning SQL (for the Oracle database) is here:  Contents