Single Article

Tabular Form Validation & Autocomplete with jQuery

Category APEX and jQuery

Here is how to set autocomplete for tabular form field  and store key value with validation to database.
I did get lot of help to this when reading Patrick Wolf blog APEX Tips & Tricks.

If you are on APEX 3 load jQuery and jQuery UI library and css in page template header or page HTML header.

On APEX 4 add to page HTML header:

<link type="text/css" href="#IMAGE_PREFIX#libraries/jquery-ui/1.8/themes/base/jquery.ui.autocomplete.css" rel="stylesheet" />
<script type="text/javascript" src="#IMAGE_PREFIX#libraries/jquery-ui/1.8/ui/minified/jquery.ui.autocomplete.min.js"></script>


First, I did create tabular form on EMP table using wizard.
In the sample I did set Allowed Operations to "Update Only" and only MGR column can be updated.
You can have "Update, Insert and Delete" and select more columns to be updated.

Then create page process before regions:

IF APEX_COLLECTION.COLLECTION_EXISTS(p_collection_name => 'MGR_NAME_COL') THEN
  APEX_COLLECTION.DELETE_COLLECTION(p_collection_name => 'MGR_NAME_COL');
END IF;
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B(
  p_collection_name => 'MGR_NAME_COL',
  p_query => '
    SELECT e.empno,
      m.ename AS mgrname
    FROM emp e,
      emp m
    WHERE e.mgr = m.empno(+)
  '
);

 

This is needed to create collection to store user entered values when validation error occurs.
Set process condition "No InLine Validation Errors Displayed".

Then modify your form Region Source:

SELECT e.empno,
  e.empno AS empno_display,
  e.ename,
  e.job,
  e.mgr,
  e.hiredate,
  e.sal,
  e.comm,
  e.deptno,
  c002 AS mgr_name,
  seq_id
FROM emp e,
  apex_collections
WHERE collection_name = 'MGR_NAME_COL'
  AND e.empno = c001

 

Go to Report Attributes.
Edit MGR column attributes and set Display As to "Hidden".

Edit SEQ_ID column attributes and set Show Column to "No".

Edit MGR_NAME column attributes and set Display As to "Standard Report Column".
Enter to HTML Expression:

<input type="text" value="#MGR_NAME#" name="f40" id="f40_#ROWNUM#" />
<input type="hidden" value="#SEQ_ID#" name="f41" id="f41_#ROWNUM#" />

 

If you are on APEX 3.2 create application On Demand process.
On APEX 4 you can create page process and set process point to "On Demand".
Process name GET_MGR. Source:

DECLARE
  l_sql VARCHAR2(32700);
BEGIN

  /* Query column aliases are important. Used in JavaScript */
  l_sql := '
    SELECT ename AS DIS,
      ename AS RET
    FROM emp
    WHERE ename LIKE ''' || UPPER(APEX_APPLICATION.G_X01) || '%''
    ORDER BY 1
  ';

  APEX_UTIL.JSON_FROM_SQL(l_sql);

EXCEPTION
  /* 
   Exception handler needed on APEX 3.2
   See Martin Giffy D'Souza blog post:
   http://www.talkapex.com/2009/05/apexutiljsonfromsql-no-rows-bug-fix.html 
  */
  WHEN OTHERS THEN
    HTP.prn('{"row":[]}');
END;

 

Create page level validation type "Function Returning Error Text":

DECLARE
  l_mgrno     NUMBER;
  l_err_mesg  VARCHAR2(32700);
BEGIN

  /* Save all values to collection and validate. Note: 
      g_f40 is the text field column MGR_NAME
      g_f41 is the hidden column SEQ_ID
  */

  FOR i IN 1 .. APEX_APPLICATION.G_F40.COUNT
  LOOP

    l_mgrno := NULL;  

    /* Save value to collection */
    APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE(
      p_collection_name => 'PAGE202COL',
      p_seq => APEX_APPLICATION.G_F41(i),
      p_attr_number => 2,
      p_attr_value  => APEX_APPLICATION.G_F40(i)
    );
    
    IF TRIM(APEX_APPLICATION.G_F40(i)) IS NOT NULL THEN

      /* Check that value exists and fetch return value */
      BEGIN
      
        SELECT empno
        INTO l_mgrno
        FROM wwv_demo_emp
        WHERE UPPER(ename) = TRIM(UPPER(APEX_APPLICATION.G_F40(i)))
        ;

      /* Set error message and collect rows in error */      
      EXCEPTION WHEN NO_DATA_FOUND THEN
      
        IF l_err_mesg IS NULL THEN
          l_err_mesg := 'Employee(s) not exists.<script type="text/javascript">$(function(){$("#f40_' || i;
        ELSE
          l_err_mesg := l_err_mesg || ',#f40_' || i;
        END IF;

      END;
  
    END IF;

    /* Set manager empno */
    APEX_APPLICATION.G_F02(i) := l_mgrno;

  END LOOP;
  
  /* If errors complete error message JavaScript and return error message */
  IF l_err_mesg IS NOT NULL THEN
    l_err_mesg := l_err_mesg || '").addClass("ui-state-error");});</script>';
    RETURN l_err_mesg;
  END IF;
  
  RETURN NULL;

END;

 

Set Error message display location to "Inline in Notification".

Place to page HTML header:

<script type="text/javascript">
$(function(){
 /* Set autocomplete */
 $('input[name="f40"]').autocomplete({
  source:function(req,res){

   /* Parameters for jQuery ajax */ 
   var opt={
    url:"wwv_flow.show",
    dataType:"json",
    traditional:true,
    cache:false,
    type:"POST",
    data:{
     p_flow_id:$v("pFlowId"),
     p_flow_step_id:$v("pFlowStepId"),
     p_instance:$v("pInstance"),
     p_request:"APPLICATION_PROCESS=GET_MGR",
     x01:req.term
    },
    success:function(jsonData){
     res(
      $.map(jsonData.row,function(jsonRow){
       return{
        label:jsonRow.RET,
        value:jsonRow.DIS
       };
      })
     );
    }
   };
   /* Call Ajax to get list */
   $.ajax(opt);
  },
  focus:function(){
   /* Prevent value change when list get focus */
   return false;
  }
 });
});
</script>

See working example.

Comments

  • Jari Laine · 17 Jul 2015  

    Hi Siva Ram,

    Please create example about problem to apex.oracle.com and share developer login details to workspace.

    Regards,
    Jari

        
  • Siva Ram · 16 Jul 2015  

    Hi,

    I tried that one. but it was not working in my application. i follow the steps correctly but it wasn't working. every time it was showing no data found.I bring the query from region source and run in Benthic there it was working and showing some records when we run in apex page it was showing no data found, failed to parse query error. can you please give the sample application download link with that example.Thanks in advance.Please suggest me something.

    Thanks & Regards,

    Siva Ram N.

        
  • Jari Laine · 4 Mar 2014  

    Hi John T,

    I have try check this, but not yet get solution where I'm happy.

    What you can do is, change Add Row button to submit page and use process to add new row. Just like in old days before APEX 4.x

    Regards,
    Jari

        
  • John T · 4 Mar 2014  

    Anyone else have a solution?

    Jari seems to be reasonably busy. I really really need a solution to this in the very near future. I have a project that is on a deadline and I really need this feature.

        
  • John T · 1 Mar 2014  

    Jari, I am on apex 4.2

        
  • Jari Laine · 1 Mar 2014  

    Hi John T,

    What is your APEX version?

    Regards,
    Jari

        
  • John T · 25 Feb 2014  

    Jari,

    Having the below code in HTML header works, only when records exist. How can i make auto complete work on new records when adding new row to tabular form?

    Please help.

    Thanks,

    <link type="text/css" href="#IMAGE_PREFIX#libraries/jquery-ui/1.8/themes/base/jquery.ui.autocomplete.css" rel="stylesheet" />
    <script type="text/javascript" src="#IMAGE_PREFIX#libraries/jquery-ui/1.8/ui/minified/jquery.ui.autocomplete.min.js"></script>
    
    <script type="text/javascript">
        $(function () {
    
            $('input[name="f05"]').autocomplete({
                source: function (req, res) {
    
                    var opt = {
                        url: "wwv_flow.show",
                        dataType: "json",
                        traditional: true,
                        cache: false,
                        type: "POST",
                        data: {
                            p_flow_id: $v("pFlowId"),
                            p_flow_step_id: $v("pFlowStepId"),
                            p_instance: $v("pInstance"),
                            p_request: "APPLICATION_PROCESS=GET_GENERATOR",
                            x01: req.term
                        },
                        success: function (jsonData) {
                            event.preventDefault();
                            res(
                                $.map(jsonData.row, function (jsonRow) {
                                    return {
                                        label: jsonRow.DIS,
                                        value: jsonRow.RET
                                    };
    
                                })
                            );
    
                            return false;
                        }
                    };
                    /* Call Ajax to get list */
                    $.ajax(opt);
    
                },
                focus: function () {
                    /* Prevent value change when list get focus */
                    return false;
                }
            });
        });
    </script>
    
    
        
  • John T · 25 Feb 2014  

    Jari,

    Like the previous 2 comments.

    Is there anyway to get this to work with new rows?

    Thanks..

        
  • Maximus · 16 Mar 2012  
    Hi, This is only working for existing data. Is there a way to get it work for a new record as well? Regards, Max     
  • RobV · 5 Feb 2012  
    Hello, I have managed to get it working on existing data. However it does not work for new records. Is there anything to set to have the autocomplete functionality available for new records? Thanks, Rob     

Global Right Column

Search

Archives

Subscribe in a Reader