Apex faceted search to automatically generate row nuber

user_14235961 0
Viewed 76

i am trying to add a column 'Number' in my report, so far all i can do is use row_number()

select row_number over (order by date_a) as N, id, date_a
from my_table
order by date_a

the problem is when i start using the faceted search (which is essential), the numbers of course are not generated again, so i get results like these

n date_a
33 1/1/2020
150 3/2/2020
491 2/4/2020
647 8/6/2020

so is there a way to generate row numbers automatically after each filter or a java-script to generate numbers from client-side

oracle-apex
1 Answers

You cannot do this out of the box. The faceted search functionality happens on the client so you'll have to generate the row numbers with javascript. Also, when users perform any action that causes the faceted search results to refresh, the row numbers have to be recalculated.

I got this to work doing the following. For my example I used the sample emp table, with the query SELECT * FROM emp as source.

  • Static id for the "Search Results" region: fs-report
  • Add a derived column to the report to display the generated rownumber. In my case, this is the first column.
  • In the page attributes, under "Function and Global Variable Declaration", create the following function:
function resequenceEmpTab(){
    let table = document.getElementById('report_table_fs-report');
    console.log('x');
    for(var i = 1; i < table.rows.length; i++)
    {
        {
            //set the value of the first column to i
            table.rows[i].cells[0].innerHTML = i;
        }
    }         
}
  • In the page attributes, under "Execute when Page Loads", add the following code:
resequenceEmpTab();
  • create a dynamic action, Event: "After Refresh", Selection type: "Region", Region: "Search". Set Event Scope to "Dynamic".
  • add a true action of type "Execute Javascript Code" and source
resequenceEmpTab();

Verified this on an apex 20.2 instance.

user_41898140
Related