Unique Key Validation for LOV Based on Reference Attribute

While ago I was blogging how to Define LOV on a Reference Attribute in Oracle ADF 11g. Such type of LOV's are defined on description text, not on key. This approach works well, however there are some limitations when applying Unique Key business rule for such type of LOV's. Because LOV is based on read-only reference attribute, and Unique Key business rule is applied for LOV key, updated in the background - Unique Key business rule is not fired correctly for LOV based on reference attribute. I have tested this with JDev PS3, PS4 and R2 releases - same behavior is reproduced - Unique Key business rule is not working for such type of LOV's. User requirement is a must, and I will describe today how to implement Unique Key validation for LOV based on reference attribute, may be not exactly following ADF best practices.

Download sample application - UniqueKeyADFSample.zip. This sample is not using LOV based on a reference attribute approach described from the blog post mentioned above. Instead, it is using calculated attribute - DepartmentName, by default this attribute is populated through SQL expression and is updateable (which is very important):

This attribute must be set as updateable, otherwise in case of validation error - user still will see previous value. Control Hints for DepartmentName define Auto Submit = true property:

Persistent LOV key attribute - DepartmentId, is defined to be dependent on DepartmentName:

This means, whenever DepartmentName will be changing, DepartmentId value will be updated as well.

Let's return now to Unique Key business rule. This rule defines pair of attributes - DepartmentId and PhoneNumber to be unique for every row:

Business rule - Unique Key Validator, is created based on EO alternate key:

LOV is defined on calculated DepartmentName attribute and additionally returns value for DepartmentId persistent attribute:

In this scenarion, because Unique Key business rule is defined on EO level, and we are changing DepartmentId by selecting another value for DepartmentName - Unique Key business rule is not fired on UI side (it works in ADF BC tester utility).

One of the ways to force business rule execution, is to call validate method for current transaction - method is implemented inside AM implementation class:

We would need to invoke this method from UI, this means declare it inside Page Definition and call after LOV value will change:

When you will look through sample application, you will wonder why there is additional hidden button available - Helper:

This is not accidental. If we would come back to the beginning of this story, we would remember that Unique Key business rule is defined on EO level. This means business rule is fired only on submit, but not on auto submit for attribute (at least this is true for LOV attribute). It would be not convenient for user to press button, just to perform validation. So, we create hidden button and invoke it programmatically. Hidden button properties:

Hidden button calls AM method to validate current transaction.

LOV is defined with Value Change Listener and invokes hidden button action - once LOV value is changed, hidden button will be invoked and validate current transaction:

This trick allows to invoke EO level validation rule, when changing only attribute value. You may think, why not to invoke AM validate method from Value Change Listener itself - its simply still not working.

Its not the end of the story yet. When transaction is validated and validation error is reported, this error is attached only with attribute from Unique Key business rule. So, even this business rule is invoked on EO level, it reports validation errors for individual attributes - tricky. So, we need to highlight failed attribute by ourselves. We can do this by referencing attribute from Unique Key business rule, just point to errors property - this would tell us about validation status and we could copy it and display for LOV:

For row.bindings.DepartmentId.errors to work, make sure DepartmentId is declared inside tree bindings:

We can simulate Unique Key validation error:

Selected duplicate value is highlighted, validation error message is shown as well: