For a client I needed to develop an Apex page were some items were set when the user changed the value of a select list. Because of user-experience I did not want to do a post to the server. Instead I wanted to use Javascript and Ajax. This blog tells how I implemented this.

Thanx to Roel’s comment, there is a more easy way; just make a (advanced) dynamic action on the list-item which sets the items based on a select statement. So, this blog is just to get some more depth in javascript-code & JSON.

Basically the page consists three items:

  1. A select list were the user can pick a product.
  2. A display item were the price of the product is shown.
  3. A display item were the supplier of the product is shown.

Task to accomplish
When the user selects a product, the values of price and customer had to change to the corresponding values in the database.

Steps to accomplish this task:

  1. Create a procedure which sends the price and supplier in Json-format depending on the product-id
    create or replace procedure get_product_info (p_product_id in number)
    is
      t_sql varchar2(256);
    
    begin
      t_sql := 'SELECT price, supplier FROM products WHERE id = '||p_poduct_id;
    
      /*Print JSON result set*/
      apex_util.json_from_sql (t_sql);
    end;
    
  2. Create a pl/sql page-process productInfo (point: on-demand)
    Begin
    get_product_info(p_product_id => :P3000_PRODUCT_ID);
    End;
    
  3. Add javascript to your page
    function ChooseProduct()
    {
    //the application proces to be executes (prepare ajax-call)
    var get = new htmldb_Get(null,&APP_ID., 'APPLICATION_PROCESS= productInfo',&APP_PAGE_ID.);
    
    //set the value of product-id to the value of the item 
    get.add('P3000_PRODUCT_ID',$('#P3000_PRODUCT_ID').val());
    
    //exceute the ajax-call and get the reponse in JSON format
    var gReturn = get.get();
    
    // make from the response string a JSON object
    var obj = eval ("(" + gReturn + ")");
    
    //set the value of the items, based on the response
    $s('P3000_PRICE',obj.row[0].PRICE);
    $s('P3000_SUPPLIER',obj.row[0].SUPPLIER);
    }
    
  4. Add a dynamic action to the select list which executes the javascript function “ChooseProduct();”, when the value is changed.

That’s it!
Sergei Martens
www.smart4apex.nl

2 thoughts on “Set value of items by changing select list using Ajax and JSON

  1. What version of APEX are you using?
    Because I think you can accomplish the same result by creating a Dynamic Action on that select list that does either a “Set Value” or a PL./SQL process. Just use those two fields as the return fields.
    So no need for a single line of JavaScript…just one select statement would be sufficient…

    Reply

Leave a Reply