1

I am using Oracle Application Express 4.2 and I have an LOV (List of Values) with a list of flight numbers in a select list. This is a dynamic list of values. I want to add a static value at the top of the select list that says "Upcoming". I also want to add a static value before a specific flight in the select list that says "Past Flights". This way I can distinguish inside the select list which flights are upcoming and which are past flights. My Dynamic LOV is currently called P_100_FLIGHT_LOV2.

I have some JavaScript code below that is executing when the page loads and it is adding "Upcoming" static value to my select list but it adds as the last option. I want this at the top and one in the middle before a specific flight. How can I achieve this using JavaScript?

Here is my JavaScript code below:

var x = document.getElementById("P100_FLIGHT_LOV2");
 var option = document.createElement("option");
 option.text = "Upcoming";
 x.add(option);

Here is the results of this JS code currently: 

SPX-14
67P
OA-9
55S
DRAGONX
34R
UPCOMING ----------CURRENTLY AT BOTTOM OF SELECT LIST

The desired result set I want to achieve:

UPCOMING -----------STATIC VALUE
SPX-14
67P
OA-9
PAST FLIGHTS ---------STATIC VALUE
55S
DRAGONX
34R
Chris M
  • 143
  • 2
  • 14

4 Answers4

2

I don't know how to do it using JavaScript, but I do know how to do it using Oracle. So, here you go.

SQL> with flight_schedule (flight, sched) as
  2  (select 'SPX-14' , to_date('12.01.2018 22:00', 'dd.mm.yyyy hh24:mi') from dual union
  3   select '67P'    , to_date('12.01.2018 22:15', 'dd.mm.yyyy hh24:mi') from dual union
  4   select 'OA-9'   , to_date('12.01.2018 22:40', 'dd.mm.yyyy hh24:mi') from dual union
  5   select '555'    , to_date('12.01.2018 18:30', 'dd.mm.yyyy hh24:mi') from dual union
  6   select 'DRAGONX', to_date('12.01.2018 19:00', 'dd.mm.yyyy hh24:mi') from dual union
  7   select '34R'    , to_date('12.01.2018 19:28', 'dd.mm.yyyy hh24:mi') from dual),
  8  static_values as
  9  (select 1 what, 'UPCOMING' statval from dual union
 10   select 3     , 'PAST FLIGHTS'     from dual
 11  ),
 12  --
 13  prep as
 14  (-- Static values
 15   select what, statval flight, null sched
 16     from static_values x
 17   union
 18   -- Upcoming flights
 19   select 2 what, flight, sched
 20     from flight_schedule
 21     where sched > sysdate
 22   union
 23   -- Past flights
 24   select 4 what, flight, sched
 25     from flight_schedule
 26     where sched <= sysdate
 27  )
 28  select flight, sched scheduled_time
 29  from prep
 30  order by what, sched;

FLIGHT       SCHEDULED_TIME
------------ ----------------
UPCOMING
SPX-14       12.01.2018 22:00
67P          12.01.2018 22:15
OA-9         12.01.2018 22:40
PAST FLIGHTS
555          12.01.2018 18:30
DRAGONX      12.01.2018 19:00
34R          12.01.2018 19:28

8 rows selected.

SQL>

A few notes:

  • current "sysdate" (regarding this example) is 12.01.2018 21:00
  • flight_schedule is actual "flight schedule table", having flights and schedule times
  • they are "unioned" with static values. WHAT column shows where to place these values (i.e. points to their sort position)
  • PREP table prepares these values; static ones are trivial, while upcoming and past flights depend on "sysdate"

Oh, yes - one more thing: as this is an Apex LoV, you'd have to select two values exactly: a display and a return value; what would these be, you'll know better (probably FLIGHT and its ID, or something like that).

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
2

This is the JavaScript code that I needed to be able to add a static value to my existing LOV. This JavaScript code executes when the page is loaded and added the static value "Past Flights" to the specified option. Thank for the help everyone. 

$('#P100_FLIGHT_LOV2 option[value="55S"]').before('<option value="Past">--- Past Flights ---</option>');
Chris M
  • 143
  • 2
  • 14
1

The Javascript solution to this is not APEX-specific. Since APEX uses the jQuery library this SO answer should do it.

(NB I have not proposed closing this question as a duplicate, because @Littlefoot has also shown that in APEX specifically there are other ways to do this without using Javascript.)

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
1

The most convenient way to do this is to use Select2 plug-in. As written on the plug-in's page, it is compatible with version 4.2.

In properties of the plug-in you need to write a query, which returns 3 columns. The third is a column for the group names (UPCOMING and PAST FLIGHTS):

select 'SPX-14' display_value, 1 return_value, 'UPCOMING' group_name from dual union all
select '67P',     2, 'UPCOMING' from dual union all
select 'OA-9',    3, 'UPCOMING' from dual union all
select '55S',     4, 'PAST FLIGHTS' group_name from dual union all
select 'DRAGONX', 5, 'PAST FLIGHTS' from dual union all
select '34R',     6, 'PAST FLIGHTS' from dual

See example "Option Grouping" on the Select2 plug-in's page.

Dmitriy
  • 5,525
  • 12
  • 25
  • 38