Skip to Main Content

Drag & Drop tabular form rows

Here is how drag & Drop tabular form rows to order records how you like.This solution works for tabular forms where you do not use pagination on APEX 4.1. Your table need have column that store display sequence id. If your table do not have column that can be used for this, alter your table and add column.

ALTER TABLE your_table ADD (display_seq NUMBER);

Go into the report attributes of your tabular form. On the right hand side of the page you will see Tasks. In there click Add Derived Column. Edit derived column attributes and change Display As to Standard Report Column. Add to Column Formatting HTML Expression:

Add to Column Formatting HTML Expression:

<img class="sort-row" src="#IMAGE_PREFIX#ws/sort.gif" alt="" />

Next uncheck all Sort checkboxes from report attributes and add order by to your report query:

ORDER BY display_seq

Create dynamic Action. Select Advanced

  • Name: Set Form Ordering
  • Event: After Refresh
  • Selection Type: Region
  • Region: {select your report region}
  • Condition: No Condition
  • Action: Execute JavaScript code
  • Fire On Page Load: True
  • Code:
$(this.triggeringElement).find(".report-standard").sortable({
 cursor: "move",
 handle: "img.sort-row",
 items: "tr:not(:first)",
 containment: ".report-standard",
 axis: "y",
 opacity: 0.9,
 revert: true,
 helper: function(e,u){
 u.children().each(function(){
  $(this).width($(this).width());
 });
 return u;
 }
}).find("img.sort-row").css({"cursor":"move"}).disableSelection();
  • Selection Type: None

Create PL/SQL process On Submit - After Computations and Validations:

FOR i IN 1 .. APEX_APPLICATION.G_FROWID.COUNT
LOOP
  UPDATE emp SET display_seq = i WHERE rowid = APEX_APPLICATION.G_FROWID(i);
END LOOP;

Add to page HTML Header:

<script type="text/javascript" src="#IMAGE_PREFIX#libraries/jquery-ui/1.8.14/ui/minified/jquery.ui.sortable.min.js"></script>

NOTE ! If you are on APEX 5:
Instead of adding jQuery UI library to HTML header, place below to page JavaScript file URLs

#JQUERYUI_DIRECTORY#ui/#MIN_DIRECTORY#jquery.ui.sortable#MIN#.js

See working example.

Comments

  • Eric 25-JAN-23 04.26.11.926663 PM

    Hi, I was able to put it to work. Thanks.

  • Eric 25-JAN-23 03.57.33.882178 PM

    Hi Jari, I am trying to replicate what you did here on IG. I found the example, but not how to do it.
    Right now I am able to reorder the rows, but the sequences don't change.
    Can you explain how to do it?
    Thank you,
    Eric
    PS
    the only place that I change from your code, Where ORDERBY is the name of my Number Field column.
    sortableIG.options = $.extend(
    {
    sequenceField: "ORDERBY"
    ,regionSelector: "#projects"
    ,sequenceStep: 10
    },
    sortableIG.options
    );

  • Nikita 11-JAN-23 12.53.06.743525 PM

    Thanks Jari!
    This has worked really well!
    Regards,
    Nikita

  • Jari Laine 10-JAN-23 05.57.44.945533 PM

    Hi Nikita,

    If using EMP table where you have added column DISPLAY_SEQ. Then IR query is

    select 
     empno
     ,ename
     ,job
     ,mgr
     ,hiredate
     ,sal
     ,comm
     ,deptno
     ,display_seq
     ,apex_item.hidden( 1, empno ) as sort_pk
    from emp

    Make column SORT_PK as hidden and set Escape special characters off.

    Edit column DISPLAY_SEQ and add to HTML Expression

    <span class="fa fa fa-braille x-sortable" aria-hidden="true"></span>
    #SORT_PK#

    Create button that submit page and after submit process

    for i in 1 .. apex_application.g_f01.count
    loop
     update emp
       set display_seq = i 
     where empno = apex_application.g_f01(i)
     ;
    end loop;

    Dynamic Action JavaScript code

    $(this.triggeringElement).find("table.a-IRR-table").sortable({
     cursor: "move"
     ,handle: ".x-sortable"
     ,items: "tr:not(:first)"
     ,containment: $(".a-IRR-tableContainer")
     ,axis: "y"
     ,opacity: 0.9
     ,revert: true
     ,helper: function(event,ui){
       ui.children().each(function(){
         $(this).width($(this).width());
       });
       return ui;
     }
    }).disableSelection();

    Run page and sort report by column DISPLAY_SEQ. Save report layout.

    Edit IR region and disable sort from region attributes and from all columns.

    Add to page CSS inline

    .x-sortable{
     cursor: grab;
     width: 100%
    }

    See working example.

    Please remember that this example is using legacy APEX_ITEM and also jQuery UI is deprecated on latest APEX version. So it might broke on next APEX version.

    I created this instruction quite quickly so there might be also mistakes. If you encounter problem, please create example to apex.oracle.com and share developer login details to workspace so I can take a look.

    Regards,
    Jari

  • Nikita 10-JAN-23 05.06.22.675377 PM

    Hi Jari,
    I am trying to replicate this feature on an interactive report. I have a column called order_seq whose HTML formatting has '<i class="fa fa-braille"/>'. How would my javascript change in this case?
    Thanks,
    Nikita

  • Jari Laine 24-JUL-22 04.49.43.033133 AM

    Hi Jorge,

    If I recall correctly, in APEX 20.2 you don't need add reference to jquery.ui.sortable.js anymore. APEX includes automatically needed JavaScript for that jQuery UI sortable.

    Regards,
    Jari

  • Jorge 20-JUL-22 06.53.00.564664 PM

    I was testing the plugin with a Classic Report, to allow to end user can change de order of records..
    But in my app in apex 20.2 the console show me the following error ?
    Failed to load resource: the server responded with a status of 404 (Not Found)
    Uncaught ReferenceError: jQuery is not defined at jquery.ui.sortable.js:1077:4
    I was testing upload on file .js in the following place #WORKSPACE_IMAGES#jquery.ui.sortable.js so I dont know if I test with that location... thanks a lot

  • Jari Laine 15-FEB-22 08.19.28.774019 PM

    Hi Fabio,

    Please email details.

    It's bit late here already so I will check latest tomorrow.

    Regards,
    Jari

  • Fabio 15-FEB-22 08.02.18.395908 PM

    Hi Jari, thanks but I'm starting with apex.
    you would be able to connect if I give you the link

  • Jari Laine 15-FEB-22 07.03.58.261141 PM

    Hi Fabio,

    Try add debug to process

    ​​​​​apex_debug.info('Start updating display sequences %s rows', APEX_APPLICATION.G_F01.COUNT);
    FOR i IN 1 .. APEX_APPLICATION.G_F01.COUNT
    LOOP
      UPDATE emp SET sort_order = i WHERE id = APEX_APPLICATION.G_F01(i);
      apex_debug.info('Updating using pk %s row %s updated', APEX_APPLICATION.G_F01(i), SQL%ROWCOUNT);
    END LOOP;

    Run page in debug mode and do some updates to form and submit page. Then view debug log and find those debug rows and post here.

    Regards,
    Jari