Forum Stats

  • 3,826,400 Users
  • 2,260,641 Discussions
  • 7,896,931 Comments

Discussions

How to create text field with autocomplete, for each word?

DannyS-Oracle
DannyS-Oracle Member Posts: 165
edited Dec 7, 2017 2:12PM in APEX Discussions

Hi everyone,

Currently Apex' text field with autocomplete treats user's input as a whole. I want to create an autocomplete field where user can get suggestion for each word, similar to how Google search gives suggestions for each word. Is there a workaround to achieve this in Apex? Any feedback will be appreciated!

I am using Apex v5.1. Thank you.

Tagged:
Billy VerreynneDannyS-Oracle

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown
    edited Dec 6, 2017 12:45PM

    Use a Pipeline Function.

    Inside the Function

    1. split the string into words ( x := apex_util.string_to_table)
    2. get last word ( w := x( x.count ) )
    3. do your "suggested words" SQL  and loop results ( where word like w || '%' )
      1. append the new letters to the input string
      2. PIPE ROW( ... )
    4. end loop;
    5. return;

    MK

    Billy Verreynne
  • LA County APEX
    LA County APEX Member Posts: 637 Gold Badge
    edited Dec 6, 2017 2:37PM

    Go into my application page 8, I have a search box that acts like a suggest list. To test, enter some text in the search box.

    ----

    Workspace: ladevapex

    User Name: mydevfellow

    Password: Pa$$word1

  • DannyS-Oracle
    DannyS-Oracle Member Posts: 165
    edited Dec 6, 2017 4:08PM

    Hi, I have successfully logged in to the workspace, but when I tried to run Page 8 it keeps asking for username/password again. And it seems they are different from the workspace's username/password?

  • LA County APEX
    LA County APEX Member Posts: 637 Gold Badge
    edited Dec 6, 2017 4:55PM

    When you log into the workspace, you are login as a developer. On the other hand, when you run the application, you need to login as a user. In this case, you can enter the same developer credential one more time to test the application.

  • DannyS-Oracle
    DannyS-Oracle Member Posts: 165
    edited Dec 6, 2017 6:23PM

    Hi LA County, I tried your search box. It indeed gave me suggestions, but not for each separate word I am typing for (as I pointed out in my question).

    To elaborate my Google Search example, if I typed "ora", the search field might give suggestion "oracle". And then if I typed "oracle ap", it continues giving another suggestion: "oracle apex". So it should gives suggestion for each separate word.

  • LA County APEX
    LA County APEX Member Posts: 637 Gold Badge
    edited Dec 6, 2017 7:29PM

    You can modify the query to get what you need. And this is the query structure I created for this sample:

    create or replace function sellist(p_search in varchar2)

    return varchar2

    is

    l_list varchar2(32767);

    cursor l_cur is

    select *

       from employees

      where employee_id || '-' || upper(first_name || ' ' || last_name) like '%' || upper(nvl(p_search,'~~')) || '%'

        and rownum < 11

      order by first_name;

    begin

      for i in l_cur loop

          l_list := l_list || '''' || i.employee_id || ':' || i.employee_id || '-' || i.first_name || ' ' || i.last_name || ''',';

      end loop;

      return l_list;

    end sellist;

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown
    edited Dec 7, 2017 2:12PM

    I may need some help in figuring out what is on.

    The PIPELINED function works as expected:

    pastedImage_0.png

    But the "Text with Autocomplete" item does not seem to be sending the string to get the list.

    pastedImage_1.png

    vs

    pastedImage_2.png

    no, I haven't checked any logs... yet.

    I'd just thought I'd "pipe" in with an example of what I stated.

    MK

    DannyS-Oracle
This discussion has been closed.