Saturday, February 7, 2009

Dependent LOV's in Query Criteria and View Accessor Workaround

Cascade LOVs is a very common request in many applications (refer to Andrejus Baranovskis's post for how to implement here )

Also the usage of Query Criteria component(af:Query) in pages is a very good practice when Complex search is needed.

There is always the ability to add more fields and set operators as desired in order to make your search fit to your needs.

There is a problem when trying to add second time the same LOV attribute that has bind variable(either in SQL or via ViewCriteria) in Query Criteria component(through Advanced feature). When trying to add the same LOV for second time the application crashes...

The Test Case:

In order to follow the test case we run this sample application

NOTE: to following test case is simple and does not reflect real conditions for real Design of cascade LOVs, it is an example.

Lets say that we want to make a search by EmployeeId =any value and have the following case about ManagerId.

ManagerId must be greater than 1 AND must NOT be equal to 2.

ManagerId produces LOV from ManagersView(a second Employees View Object) with View Criteria defined and a bind Variable.

This bind variable accepts value from EmployeeId in EmployeesView Object through View Accessor.This is a typical filtering process.

In order to follow our test case we run this sample application

  1. Press Advanced in Query Criteria,
  2. Press Add Fields,
  3. Click on Manager Id field.
  4. Press Again Add Fields,
  5. Click on Manager Id field again.

The Problem:

After pressing second time the ManagerId the following error appears:

Table is gone! You might get a 500 error page as well.

Stack Trace:

Stack Trace indicates that the second time the field is added, the Attribute EmployeeId cannot be evaluated. The problem is reproduced only for LOV fields that use bind variables. Any other fields can be added without a problem...

No matter if it is a feature or a bug, the problem persists and to bring your application back to working state a restart on the server is required!!.

The Work Around:

The solution to this problem is simple. A single line of code actually! Here is where Groovy comes to play. After reading Steve Muench's Tips and Tricks, i applied a very simple script. As you saw while running the application; the first time the LOV is added, the EmployeeId is evaluated correctly. The second time though, is not. For some reason, userData cannot find the attribute (and yet the question remains: is it a feature or a bug???).

Simply add the following Groovy script in the View Accessors of the View Object.

And Everything will work fine.

It is a simple check if the Attribute exists in UserData; if not, set value to null..

Bookmark and Share


  1. Filed bug# 8236521 using a simpler version of your reproducible testcase. The issue is not related to the userData, although as you discovered referencing userData from view criteria requires a bit of a helper class to do correctly. Removing the LOV on DepartmentId in your testcase, the issue still reproduces. We'll investigate.

  2. Yes, issue is not related to userData, userData is used in order to workaround problem. Issue is when userData is not used in View Accessor and only plain reference to Attribute exist (for example - EmployeeId).

    Correct, when LOV on DepartmentId is removed, issue is still reproduced - its because View Accessor (without userData) for removed LOV still exist in View Object.



Related Posts Plugin for WordPress, Blogger...