CODEWITHSUNDEEP
oracle-databaseplsqloracle-apex
Saddam Meshaal
Saddam Meshaal

Reputation: 552

Oracle APEX | How to change select list value and Submit it dynamically

I have two select lists

1. The first one P4_country_id_B contains countries names

    select country_name as d, country_id as r 
    from countries

2. The second one P4_CITY_ID_B contains cities of a country based on selected value in P4_CITY_ID_B.

    select city_name as d, city_id as r 
    from cities 
    where country_id = :P4_country_id_B

Everything goes OK without any problem.

BUT

I use Execute PL/SQL Code Dynamic Action to change selected values of those lists like this (for example):

:P4_country_id_B:=country_returned_value;
:P4_CITY_ID_B:=city_returned_value;

Where

country_returned_value : is a one value of countries list values for example (USA)
city_returned_value    : is a one value of cities list values for example (NewYourk).

The first selected list value changes but the second list never changes.

Notes:

How can I change list values in this case please?.

Thanks in advance.

Upvotes: 4

Views: 15837

Answers (2)

Tom
Tom

Reputation: 7028

Cascading select lists are refreshed through ajax.
Change select list 1, select list 2 will be refreshed.
You execute plsql, which in turn will set the value of the items involved. Both are select lists, and one is dependent on the other.
So while both will be set, the change of the first one will cause the second to be refreshed.
In other words, you're doing it too fast. And while there is a solution, the proper way is a bit complex and I wouldn't build it in DA's personally.

You haven't specified how or when you call the code which sets the values for the items. So here I'll just assume a DA with an action of type "Execute JavaScript" (for example)

// perform a call to the ajax callback process to get the country and city id
// there are several ways to provide values to the process if necessary.
// for example through x01, which can be used in the proces like
//   apex_application.g_x01
apex.server.process('GET_COUNTRY_DEFAULTS', {x01:""}).done(function(data){
  // process returns JSON which contains 2 properties: country_id and city_id
  // data.country_id 
  // data.city_id

  // bind a ONE-TIME handler to the after refresh event of the city
  // cascading LOVs fire the before and after refresh events like any other
  // refreshable element in apex
  // a one time handler since the values are different each time this code will
  // execute
  apex.jQuery("#Px_CITY_ID").one("apexafterrefresh",function(){
    // after refresh of the list, attempt to set the value of the list to the
    // value retrieved earlier
    apex.item(this).setValue(data.city_id);  
  });  

  // finally, set the value of the country. Doing this will also trigger the 
  // refresh of dependent elements
  apex.item('Px_CITY_ID').setValue(data.country_id);

  // since a handler has been bound, the refresh will occur, the after refresh
  // triggers, and the value will be set properly
});

Finally, create a new process on the page under "AJAX Callback", and name it GET_COUNTRY_DEFAULTS

DECLARE
  l_country_id NUMBER;
  l_city_id NUMBER;
BEGIN
  l_country_id := 8;
  l_city_id := 789;

  htp.p('{"country_id":'||l_country_id||',"city_id":'||l_city_id||'}');
EXCEPTION WHEN OTHERS THEN
  -- returning an error while using apex.server.process will prompt the user
  -- with the error and halt further processing
  htp.p('{"error":"'||sqlerrm||'"}');
END;

That should tie everything together.

Upvotes: 4

Hawk
Hawk

Reputation: 5170

I think there is some confusion here. My answer below, assumes, according to your question, the first list name is P4_country_id_B, and the second list name is Cities_LOV. If that is not the case, please clarify.

Your first list called P4_country_id_B, and you assign it to itself through the following statement:

  :P4_country_id_B:=country_returned_value;

So basically, nothing has changed, the value of P4_country_id_B is the returned value of your list P4_country_id_B without any need for this assignment. Note, it is not clear to me, what is country_returned_value, because P4_country_id_B holds the returned value.

Secondly, you have a list called Cities_LOV, and you assign the returned value to P4_CITY_ID_B page item, through the following statement:

:P4_CITY_ID_B:=returned_city_value;

Again, I am not sure what is returned_city_value, because Cities_LOV holds the returned value of that list.

I am not sure what you are trying to achieve here. But I assume, you want to allow the user to select the Country first, and then based on that, you want to refresh the Cities list to display the cities in that particular country. If that is the case, then use a dynamic action on P4_country_id_B value change, to refresh the value of Cities_LOV. You only need to pass P4_country_id_B to that dynamic action.

UPDATE

After you corrected the wording of the question, the answer would be like this:

In your child list P4_CITY_ID_B make sure you set the option Cascading LOV parent item(s) to the parent list P4_country_id_B. You do not need the dynamic action. The child list, should refresh upon the change of the parent list. The answer here, goes in details about how to implement cascading list

Upvotes: 1

Related Questions