CODEWITHSUNDEEP
sqloracle-databaseoracle-apex-5.1
philscic
philscic

Reputation: 27

Oracle APEX 5.1 - How to sort selected values in a Shuttle control

I have a shuttle control with a list of countries. So the user can select multiple countries, example: UK, France, Portugal. The order depends on how the user selects or moves the choices (see pic)

enter image description here.

These are stored as UK:France:Portugal in the underlying table.

The problem is that I need these countries to be stored alphabetically because UK:France:Portugal is not the same as France:Portugal:UK. I know that in an ideal world these are saved in separate rows, but unfortunately this is not an option for me.

Is there a way to sort the selected values within the shuttle (on the right) alphabetically, maybe through a dynamic action when selecting countries?

If not, as an alternative, can we have a Post Calculation Computation to sort and store these values?

Thanks!

Upvotes: 0

Views: 733

Answers (1)

Littlefoot
Littlefoot

Reputation: 143063

I don't know Apex-solution, but I can suggest Oracle-solution.

Here's an example: table test stores information about countries:

SQL> create table test (id number, countries varchar2(30));

Table created.

A database trigger sorts values in the countries column. How? It splits colon-separated values string into rows (that's what the regexp and connect by do), and then merges them back into another colon-separated values string (using listagg), but this time sorted (order by 1):

SQL> create or replace trigger trg_biu_cou
  2    before insert or update on test
  3    for each row
  4  begin
  5    select listagg(regexp_substr(:new.countries, '[^:]+', 1, level), ':') within group (order by 1)
  6    into :new.countries
  7    from dual
  8    connect by level <= regexp_count(:new.countries, ':') + 1;
  9  end;
 10  /

Trigger created.

OK, let's see it work:

SQL> insert into test (id, countries) values (1, 'UK:France:Portugal');

1 row created.

SQL> select * from test;

        ID COUNTRIES
---------- ------------------------------
         1 France:Portugal:UK

SQL> update test set countries = 'New Zealand:Croatia:Hungary' where id = 1;

1 row updated.

SQL> select * from test;

        ID COUNTRIES
---------- ------------------------------
         1 Croatia:Hungary:New Zealand

SQL>

Might be OK; give it a try.

Upvotes: 1

Related Questions