Process of enabling
users to quickly find and select from a
list of values as they type, searching and filtering without Native text
with auto-complete in APEX.
This process works fine with Apex 4 and 5 versions but not 18.
Steps in
APEX :
ü Step 1 : Create a text field item P67_ENAME.
ü Step 2 : Copy Below code in to the HTML
Header of the page.
<!doctype html>
<html
lang="en">
<head>
<meta
charset="utf-8">
<title>autocomplete
demo</title>
<link
rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/smoothness/jquery-ui.css ">
<script
src="//code.jquery.com/jquery-1.12.4.js"></script>
<script
src="//code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
</head>
ü Step 3 : a) Create an Application Process GET_NAMES.
b) Change the Process Point to On
Demand - Run this Process when requested by AJAX.
c) Copy the Below PL/SQL Code as
source.
Note : This Process returns values
matching the literals typed in the text field item in the JSON Format.
DECLARE
CURSOR c_names(pc_term IN VARCHAR2)
IS
SELECT '{"label":"'
|| ename
||
'","id":"'
|| empno
|| '"}' ename
FROM emp
WHERE UPPER (TO_CHAR (ename)) LIKE
UPPER ('%' || pc_term) || '%'
ORDER BY 1;
l_num NUMBER := 0;
l_term VARCHAR2 (4000);
BEGIN
l_term := apex_application.g_x01;
HTP.prn ('[');
FOR rec IN c_names(l_term)
LOOP
IF l_num != 0
THEN
HTP.prn (',');
END IF;
HTP.prn (rec.ename);
l_num := l_num + 1;
END LOOP;
HTP.prn (']');
END;
ü Step 4 :
a) Create a dynamic action on change of P67_ENAME (Text Field).
b) Create a True action as Execute Javascript
Code and Check Fire on Page Load. Copy the below code.
Note : Below Javascript Code calls the
AJAX Process whenever user types in the text field and passes the values to the
process and displays the values in the textfield.
$( "#P67_ENAME"
).autocomplete({
source:
function(req, add){
//call the page process get_contact_data and put its return in greturn
//this process returns markup for a JSON object so this can easily be
parsed in jquery
//x01: a temporary variable simply used for passing on a value
$.post('wwv_flow.show',
{"p_request" : "APPLICATION_PROCESS=GET_NAMES",
"p_flow_id" : $v('pFlowId'),
"p_flow_step_id" :
$v('pFlowStepId'),
"p_instance" : $v('pInstance'),
"x01" : req.term
},
function(data){
if(data){
add($.parseJSON(data));
$("#P67_ENAME").removeClass("ui-autocomplete-loading");
}
else
{
$("#P67_ENAME").removeClass("ui-autocomplete-loading");
};
}
);
},
select: function(event, ui){
$("#P67_ENAME").val(ui.item.value);
// $("#P26_EMPNO").val(ui.item.value);
event.preventDefault();
},
delay: 500,
minLength: 1,
autoFocus: true
});
