Generating Matrix-Forms using ADF and JHeadstart – (Excel style)

One of the long-standing challenges of application development – in any technology – is the Matrix Form: an updateable grid-like structure of rows, columns and cells. In this post I will show a way to build and even generate such a matrix form using ADF and JHeadstart (10.1.2). In this example I create a Matrix Form for a Timesheet in a Time Registration Application. Of course there are many more examples of matrix forms: basically anywhere you want to represent a master-detail-detail structure as a master-detail-with-many-columns. Examples:

• timesheet
• questionnaire with multiple predefined answers per question where more than one can be selected (so not a radiogroup but probably using checkboxes)
• Expense Reports with the expenses per day and per category
• Any time the paper representation of the form would contain a grid

.

Usually in my articles on the weblog, I have the feeling that I have a complete and solid story to tell. However, this time I am less sure. I propose a working solution – with some small obvious defects -, yet I am not convinced it is the absolute best way of doing this. So I would welcome any comments from readers with suggestions to improve on this approach.

The case at hand: we need to generate an application for Time Registration. The core screen in this application is a grid-like structure where projects are lined up on the rows and days are organized in columns and our employees enter the hours spent on each of the projects for any given day. The underlying database design has tables for timesheets, timesheet records (intersection for timesheet and project) and hour registration (intersection between timesheet record and day). The challenge is generating the matrix form based on this table design.

Steps to implement the Timesheet Application’s Model:

• Create a default Business Components from Tables in Database Model in a new Web Application
• Add new (transient) attributes to the TimesheetRecordsView for every column (day)
• Override the getter-methods for the transient attribute: have them use the details (HourRegistrations) to provide the values for these transient attributes
• Override the setter-methods for the transient attributes: have them manipulate details (HourRegistrations) to store the values

The main trick is to have attributes in the master (TimesheetRecord) represent detail records (HourRegistration). Since the detail-records consist of only one real value (hours) and two intersection-foreign keys (to timesheet record and to day), we can represent the details with attributes in one master (timesheet record, row) in fixed locations representing the other master (day, column).

Preparing the TimesheetRecordView

First of all, I have added 31 transient attributes to the TimesheetRecordsView, since our timesheet is used on a monthly basis. These attributes are of type Number, corresponding with the datatype of the Hours attribute in the HourRegistrationsView.

I have implemented a generic Number getDayId(int dayno) method in the TimesheetRecordsViewRowImpl class. This method finds the primary key (Id attribute) for the Day corresponding with the dayno parameter. Dayno can be seen as the column sequence number in the matrix. Note: there is a much more efficient way to implement this getDayId() method that I have to implement. Building a map of dayno,dayId value-pairs once (at Timesheet level) and using that to retrieve these values from is a much better performing approach than this one.

  /**
   * This method will find the ID from the DAYS table for the DAYNO-th day
   * in the month for the timesheet that this timesheetrecord is part of.
   * @return The ID value corresponding with the DAYS.id column, to be used for setting the DayId attribute on the HourRegistration
   * @param dayno the #th day of the month (1-31)
   *
   * @todo add a method getDayId to timesheetsViewRowImpl that uses a Map with dayNo, dayId that
   *       is initialized only once per timesheet
   */
private Number getDayId(int dayno) {
   ViewObject findDayVO = getApplicationModule().findViewObject("FindDayView1");
   findDayVO.setWhereClauseParam(0, new Integer(dayno));
   findDayVO.setWhereClauseParam(1, getTimesheet().getAttribute("StartDayId"));
   findDayVO.executeQuery();
   Number dayId= (Number)findDayVO.next().getAttribute("Id");
   return dayId;
}

Note that this method uses the FindDayView(1) ViewObject(Usage). This ViewObject contains the following query:

SELECT Days.ID
FROM DAYS Days
WHERE days.day_date - :1+1 = (select day_date from days where id = :2)

and returns the id of the day whose date minus the day-number is equal to the start date of the timesheet.

Next I have implemented a generic Number getDay(int dayno) method in the TimesheetRecordsViewRowImpl class, that returns the hours recorded on the given day. The method also translates the dayno (the sequence-number of the day in the month of the master-timesheet) to a dayId, the primary key of the Day object (from the DaysView ViewObject). This method searches through the HourRegistration details of the TimesheetRecord, getting hold of these using the Accessor method corresponding with the ViewLink between TimesheetRecordsView and HourRegistrationView:

  /**
   * Find the value of the HourRegistration.Hours attribute (corresponding with the
   * HOUR_REGISTRATION.HOURS column) for the given dayNo, the #th day of the month
   * for which this timesheet is defined.
   * @return the number of hours written on the given day
   * @param dayNo the sequence number of the day of the month for which we need the number of hours registered
   */
 private Number getDay( int dayNo) {
    RowIterator rows = getHourRegistrations(); // call to accessor method corresponding to the ViewLink between TimesheetRecord and HourRegistration
    Number dayId = getDayId(dayNo); //convert the dayno (or column number in the matrix) to the primary of the alternate master (Day)
    while (rows.hasNext()) {
      HourRegistrationViewRowImpl hourReg = (HourRegistrationViewRowImpl)rows.next();
      if (hourReg.getDayId().longValue()==dayId.longValue()) { // locate the HourRegistration that corresponds to the day we are interested in
        return hourReg.getHours(); // return the number of hours recorded on this HourRegistration
      }
    }
    return null;
  }

All getDay1()..getDay31() methods for the transient attributes are now implemented with calls to the generic getDay(dayno) method:

  public Number getDay1() {
  return getDay(1);
  }

With this in place, we can retrieve all timesheet-records in pivot-ed format with the hours recorded in the database for all days in the month. Next we want to implement the capability to edit hour-values and have them recorded in the database. We now have to bear in mind that (re)setting a value for a certain day corresponds with an operation on a detail record:

• changing an existing value means updating an existing HourRegistration detail-record
• setting a value in previously empty cell means creating an HourRegistration detail-record
• resetting an existing value means deleting an existing HourRegistration detail-record

We have to implement the setDay1()..setDay31() methods in the TimesheetRecordsView:

  public void setDay1(Number value) {
    setDay( value, 1);
  }

Again we can make use of a generic setDay() method:

  public void setDay(Number value, int dayno) {
    Number dayId= getDayId(dayno);
    if (value== null) {
      // delete hourReg element
      removeHourRegistration(dayId);
    }
    else {
      HourRegistrationViewRowImpl hourReg = getHourRegistration(dayId);
      if (hourReg != null) {
         hourReg.setHours(value);
      }
      else {
        RowIterator rowSet = getHourRegistrations();
        HourRegistrationViewRowImpl row = (HourRegistrationViewRowImpl)rowSet.createRow();
        row.setHours(value);
        row.setDayId(dayId);
      }
    }
  }

Additional preparations

In order to have the Id attribute (primary key) in both HourRegistration and TimesheetRecord entities derived from the hrn_seq and trd_seq database sequences, I have implemented the create() method on the EntityImpl classes.:

 public class TimesheetRecordsImpl extends EntityImpl  {
....
 protected void create(AttributeList attributeList) {
    super.create(attributeList);
    SequenceImpl s = new SequenceImpl("trd_seq", getDBTransaction());
    Long next = (Long)s.getData();
    setId(new Number(next.intValue()));
  }

Testing our model

Using the ADF BC Application Module Tester, we can now test whether our model does what we want: present timesheetrecords in pivoted format and allow us to edit the hours in the timesheetrecord while having the database accordingly updated.

Generating the Web Application using JHeadstart

Now we are ready to generate a simple web application with our Matrix Form. Using JHeadstart, this is a piece of cake, especially now all matrix complexity and pivot-details are hidden in the Business Service layer, notably the ViewObjects. JHeadstart and the WebApplication are completely unaware of the fact that the transient attributes in TimesheetRecordsView do not correspond one-to-one with columns but instead with individual detail-records. The steps for creating a JHeadstart application are pretty simple:

• preparation: download and install JHeadstart for your JDeveloper instance
• enable JHeadstart on the ViewController project
• create a new JHeadstart Application Structure File
• (optional) remove all GROUP elements from the JHeadstart Application Structure File
• edit the JHeadstart Application Structure File: create a base group for Timesheets, a detail group for TimesheetRecords with a Lookup (type Choice) for Projects
• edit the Timesheet Records ViewObject: set the display width for all day1..day31 attributes to 3; set display to false for Id and TstId;set the prompt for pjtId to project and set the prompts for day1..day31 to 1..31
• Generate and run the application

Here we see the Application Structure file for our little application. In a real application we would have groups for Maintaining Employee, Project and Day data.

This image demonstrates the ViewObject Editor where we set the JHeadstart custom properties that will guide the Application Generator:

Finally the generated web application (UIX user interface style) looks as follows:

Here you can dowload the entire JDeveloper 10.1.2 workspace with the above application. Use the file timesheets.txt for creation of the underlying database and some sample data.TimeRegistration.zip

What’s Next

Remaining Deficiencies

The solution as it stands has a few sore spots:

• Currently you cannot add a new line in the matrix (add a project) and set the hours in this line in the same transaction; you need to first create the new line, then Save, and only then can you start entering the time. This is the result of the fact that the setDay#() methods immediately create an HourRegistration, in this particular case even before the TimesheetRecord has been created and assigned a primary key value.
• For each getDay#() that is invoked, the getDayId() method is used to find the Id of the Day that corresponds with the #th column in the grid. With that DayId in hand, we sift through the HourRegistration details of the TimesheetRecord. That is a very inefficient way of getting hold of the desired hours. First of all, I think we should build a Map of day# and dayId when initializing a Timesheet. Any time we need the DayId for a given day of the month, we can simply get it from the Map instead of from the database. Then I think we should use a key on HourRegistration (timesheetRecord, dayId) to find the desired HourRegistration.
• It seems that we can restrict the accessor usage to two moments: when the TimesheetRecord is initialized and when a set attribute method is invoked. In between, the getter methods should be able to work against an intermal map (internal attribute) that is also maintained by the setter-methods. Note: this relies on the fact that the timesheet entries are very unlikely to be changed in parallel sessions.

Further enhancements

Some common features for such matrix forms are

• Summations for rows and columns, as well as a grand total, maintained Client Side while editing
• Column headings derived from secondary master (day-labels for example)
• Dynamic Restrictions or Color markings on the cells – for example hiding or disabling certain cells to prevent entry (no holiday hours on weekend days)

I hope to extend this example to include such advanced features.

Alternative solutions

In this case I have chosen to perform the data-pivot (turning rows into columns) in the ViewObject. I could have done the same thing in the database, for example in a view with possibly a table function, using an Instead Of Trigger to re-route DML operations. The resulting code would have been quite similar to what we have achieved now in the ViewObject. The database solution is one to explore. Especially when we have a 8i EE or 9i RDBMS at our disposal, we can make use of Analytical Functions or Table Functions for the pivot operation – or even a Multiset and Cast as Collection or Cursor Expression. I would have to see whether a ViewObject attribute can be based on a NestedTable or RefCursor and to what extent JHeadstart will be able to generate against such attribute types.