Oracle-Apex sorting shuttle

Question

I've created "many to many relationship using a shuttle" When I select ROLE_NAME from LOV I want to see privileges for selected role on right side, and privilege which role doesn't have on left side.

Instead, I see privilege which role doesn't have on right side and privilege for this role on left side.

Answer 1

There is a variety of issues that are causing this behavior.

• You are seeing the the items on the wrong side because you have switched the list of values "SQL Query" and the "Source". SQL Query should return all possible values. "Source" is the selected values.
• There is a cascading LOV on the shuttle. This is not what a cascading lov does. A cascading lov controls the available values for selection (for example if lov 1 is REGIONS and lov 2 is countries then your cascading lov will restrict lov 2 to only countries in the region in lov 1 - this is not the case in your list of values. You want to change the selected values for the selected role. So you need a dynamic action on the lov to refresh the shuttle value instead of a cascading lov.
• An existing dynamic action exists that submits the page. This is not needed. Instead you can use a dynamic action with action "Set Value" to set the shuttle value to the value of the selected role.
• Your database action only does an insert. That means if you click "Save" again for a Role that already has privileges you will create duplicate values.

I have fixed all issues (apart from the last one, the page process) in an app called " shuttle-test - KOEN" on your workspace

One more thing: the naming convention in apex for page items is P#page_number#_ITEMNAME. I strongly suggest you stick to that.

Rgds Koen

Answer 2

You are selecting the Privileges which are NOT assigned to the LOV item(role). So it is showing the NOT assigned roles on the right side.

If you remove the NOT from the query then it will show the assigned Roles on the right and the to be assigned roles on the left

SELECT priv_id
FROM privileges s1
WHERE NOT EXISTS (SELECT 1
                  FROM roles s2
                  WHERE s2.fk_priv_id = s1.priv_id
                  AND s2.fk_role_id = :P_4_LOV
                  );

Should be

SELECT priv_id
    FROM privileges s1
    WHERE EXISTS (SELECT 1
                      FROM roles s2
                      WHERE s2.fk_priv_id = s1.priv_id
                      AND s2.fk_role_id = :P_4_LOV
                      );