Set value of items by changing select list using Ajax and JSON

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

Execute Page process with Ajax callback

Sometimes you want to call a page-proces, but do need to make a compleet refresh of the page by making a request.

In my case I made a simple master-detail screen with Tabs (Thank you Richard). In the tabs there are reports with a delete button for each row. For the delete operation I did not want refresh the complete page, but only wanted to call the page-proces I had written for the delete-operation and only wanted to refresh the report-region affected.

This is wat i did:

  • I made a normal report and added an icon for the delete operation. The icon for the delete operation is the id-column in the report (PRODUCT_VERWIJZING_ID) and has the follwoing column link properties:
    • link text: <img src=”#IMAGE_PREFIX#del.gif” alt=””>
    • target: URL
    • URL: javascript:del_verwijzing(#PRODUCT_VERWIJZING_ID#)

Continue reading