How to attatch Dynamic Action or Process to a Menu Popout list item - APEX 21.1

Question

I'm trying to add a dropdown menu button to a classic report row and assign the menu items either a process or DA. I've used the Universal Theme docs to help me figure out how to get the menu button added to a classic report, but now I'm stuck and do not understand how to assign/attach a Dynamic Action or Process to each item in my menu list:

• APPROVE WORKLOAD
• ASSIGN WORKLOAD

For this instance, both menu items will just open a dialog page passing in the primary key of the row (MASTER_ID), but some other actions elsewhere will need to handle more complex logic where APEX Processing or DA would be best.

I would be surprised APEX does not have something like this to be implemented natively since there is something similar with Interactive Grids and is a common web pattern.

UPDATE: I went ahead and added this as an APEX Idea (FR-2133) here. If you think this is something worth adding to APEX, please give it an upvote.

Answer 1

There isn't a built-in solution for this unfortunately. There is a good blog post by John Snyders that describes a way of building this yourself.

I have also built some similar functionality, which I have a demo of here. This uses a bespoke table to store the menu options, and makes an AJAX call when the menu is opened to get the appropriate menu entries to display. These can be conditional e.g. the "Calculate commission" menu entry is only applicable to employees with job='SALESMAN'.

Tables

create table  report_menus 
   ( menu_name varchar2(30), 
     constraint report_menus_pk primary key (menu_name)
   );

create table  report_menu_entries 
   ( menu_name varchar2(30) not null enable, 
     entry_title varchar2(100) not null enable, 
     entry_target varchar2(1000) not null enable, 
     display_sequence number, 
     condition_type varchar2(30), 
     condition_expression varchar2(4000), 
     constraint report_menu_entries_pk 
        primary key (menu_name, display_sequence),   
     constraint report_menu_entries_fk1 
        foreign key (menu_name)
           references  report_menus (menu_name) 
    );

(Table REPORT_MENUS is there for completeness but doesn't do much in this demo).

Sample data

For my example I created a single menu called 'EMPLOYEE_MENU' with 2 options:

insert into report_menu_entries values 
   ( 'EMPLOYEE_MENU',
     'Update',
     'f?p=&APP_ALIAS.:3:&SESSION.::&DEBUG.:3:P3_EMPNO:<pk>',
     1,
     'NOT_EXISTS',
     'select * from emp where empno=<pk> and job = 'PRESIDENT''');

insert into report_menu_entries values 
   ( 'EMPLOYEE_MENU',
     'Calculate commission',
     'javascript:alert('Not yet implemented');',
     2,
     'EXISTS',
     'select * from emp where empno=<pk> and job='SALESMAN''');

I have invented a special placeholder <pk> which I use wherever I want to plug in the primary key of the record we are on (i.e. the EMPNO in this demo). This is required in the target URL and in conditions that depend on the data.

Report region

In the report, I create a dummy column for the menu by selecting null as menu. I make this into a Link with a target URL of # (we don't need the target really) and these properties:

Property	Value
Link text	<span class="fa fa-navicon" aria-hidden="true" title="report menu"></span>
Link attributes	class="report-menu" data-key="#EMPNO#" data-menu="EMPLOYEE_MENU"

The link text displays a suitable icon, and the properties are what make it start to work. The data-key attribute defines the primary key value for this row, and the data-menu attribute specifies which of our REPORT_MENUS to use.

Dynamic Action

We now need a dynamic action to handle clicking on the menu. This uses a jQuery selector .report-menu to identify the link being clicked, and it executes this Javascript code:

showReportMenu (this.triggeringElement);

Javascript code

showReportMenu is a Javascript function defined as follows:

function showReportMenu (pLink) {
    var theLink = $(pLink);
    var dataMenu = theLink.attr("data-menu");
    var dataKey = theLink.attr("data-key");
    apex.server.process ( "Show_Report_Menu", {
        x01: dataMenu,
        x02: dataKey
        }, {
       success: function( pData ) {
           $('div#reportMenu').remove();
            var html = '<div id="reportMenu" ><ul>';
            for (i=0; i<pData.menu.length; i++) {
                if (pData.menu[i].url == '') {
                    html = html + '<li><span>' + pData.menu[i].title + '</span></li>';
                } else {
                    html = html + '<li><a href="' + pData.menu[i].url + '">' + pData.menu[i].title + '</a></li>';
                }
            }
            html = html + '</ul></div>';
            $(theLink).after(html);
            $('#reportMenu').menu({}).menu("toggle",theLink);
       }
    });
}

That invokes an AJAX callback application process 'Show_Report_Menu' which returns JSON containing the menu options, which are then displayed.

Application process

The 'Show_Report_Menu' process just calls a procedure:

report_menu_pkg.render_menu
  ( p_menu_name => apex_application.g_x01
  , p_keyvals => apex_application.g_x02
  );

Package procedure code

The procedure does the work of looking up the menu entries for the menu, deciding whether they should be shown for the key value, and returning the JSON for the ones to show. It looks like this:

procedure render_menu
    ( p_menu_name varchar2
    , p_keyvals   varchar2
    )
is
    l_first boolean := true;
    k_template constant long := '{"title": "%0", "url": "%1"}';
    l_buffer long;
    l_test_sql long;
    l_count integer;
begin
    -- Construct a JSON object to return the menu entries e.g. {"menu": [{"title": "Update", "url": ".."}, {"title": "Delete", "url": "..."}]}
    sys.htp.p('{"menu": [');

    -- Process the menu entries in display sequence order and check any condition before adding to the JSON
    <<entries>>
    for r in (
        select *
        from report_menu_entries
        where menu_name = p_menu_name
        order by display_sequence
    ) loop
        -- Check any condition, substituting placeholder <pk> with the actual key value
        case r.condition_type
            when 'EXISTS' then
                l_test_sql := 'select count(*) from dual where exists (' || replace (r.condition_expression, '<pk>', p_keyvals) || ')';
                execute immediate l_test_sql  into l_count;
                continue entries when l_count = 0;
            when 'NOT_EXISTS' then
                l_test_sql := 'select count(*) from dual where exists (' || replace (r.condition_expression, '<pk>', p_keyvals) || ')';
                execute immediate l_test_sql  into l_count;
                continue entries when l_count = 1;
            else
                null;
        end case;

        -- Separate entries by commas (no comma befor first)
        if l_first then
            l_first := false;
        else
            sys.htp.p (',');
        end if;

        -- Replace placeholders in target URL including the key value
        r.entry_target := replace (r.entry_target, '<pk>', p_keyvals);
        r.entry_target := replace (r.entry_target, '&APP_ALIAS.', v('APP_ALIAS'));
        r.entry_target := replace (r.entry_target, '&SESSION.', v('SESSION'));
        r.entry_target := replace (r.entry_target, '&DEBUG.', v('DEBUG'));
        r.entry_target := apex_util.prepare_url (r.entry_target);
        
        -- Construct JSON entry
        l_buffer := apex_string.format (k_template, r.entry_title, r.entry_target);
        sys.htp.p(l_buffer);
    end loop entries;

    if l_first then
        l_buffer := apex_string.format (k_template, '(No actions available)', null);
        sys.htp.p(l_buffer);
    end if;
    sys.htp.p(']}');
end;

I think that's everything. There is room for improvement, as this is only demo code. Ideally the JSON would be constructed more robustly using APEX_JSON or whatever, and it should be able to handle composite keys e.g. by passing in a comma-separated list of values and specifying e.g. <pk1>,<pk2> as placeholders.