The power of using records in APEX II

In this post I follow up on the post where I started talking about records in APEX. In this post I’ll show how to add a page in front of the form created in that post. Thus this post will show a report and let you navigate to the form where the selected record is displayed. Pretty basic stuff, but it allows better testing of the form and it sets the stage for showing how to make the form used for insert, update and delete in the third post on this subject.

An interactive report is based on a report. As using procedures to read data for the form to avoid having hardcoded SQL in the application, we’ll use a similar technique here. By having a view, we can change the table and columns with no impact on the application. So we start with creating a view specific for this report.

create view apex_vw_emp as
  select empno
        ,ename
        ,job
        ,mgr
        ,hiredate
        ,sal
        ,comm
        ,deptno
    from emp;

Now that we have the view, we just create a new page with an interactive report.

Go ahead and create a page with an interactive report that uses the SQL “select * from apex_vw_emp“. Choose to not have a link to single row view.

When the page is created, edit the report attributes and add a link to a custom target.

  • Target = Page in this application
  • Page = 4 (the page where the form created in the last post is located, happens to be 4 in my application)
  • Clear cache = 4 (same as Page)
  • Item 1 Name = p4_empno (the item on the form used to retrieve the row)
  • Item 1 Value = #empno#

It is worth noting that #empno# is a reference to the empno on the row in the report that was clicked.

After you apply those changes, the report now has a pen icon to the left of the rows. When you click it, it will take you to the form where the row you clicked is loaded into the form (showing the ename of the empno).

This makes it easier to test the form, but more importantly it will make it possible to set up and test full CRUD functionality in a coming post.

November 7th, 2012 by