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

DannyS-Oracle

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.

Mike Kutz

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

LA County APEX

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

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

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

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

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

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

The PIPELINED function works as expected:

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

vs

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

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

MK