Saturday, September 27, 2014

Suppressing ADF LOV Like Operator Filtering

ADF LOV component provides filtering option with STARTSWITH operation. This is used to check if similar value exists in the LOV, if such value exists - LOV popup is opened with all the suggested values. If user types 10 and there are 100, 1000 in the LOV, instead of accepting value 10 - LOV popup will be opened and all three values 10, 100, 1000 will be displayed. While this is useful, there is no option to turn off such functionality. My post describes a solution, that can be used to disable suggested LOV filtering.

Here you can see, how it works by default. User types 10, there is 100 in the LOV. LOV popup is opened with both suggested values. This can be annoying for advanced user, who already know the code and they don't need to use LOV popup to select it:


We can check generated SQL statement. Original LOV SQL was updated with LIKE and this was applied for bind variable value. This is how suggested LOV values are retrieved:


LIKE is applied for DepartmentID (LOV key), even we have set it explicitly in the LOV View Criteria to use EQUALS operator:


The solution is to override buildViewCriteriaClauses method in VO Impl class. ADF executes additional View Criteria to retrieve suggested LOV values, this criteria name is "__lov__filterlist_vcr___". We can intercept this View Criteria and replace all STARTSWITH operators with EQUALS:


With this fix applied, user could type 10 and there is no LOV popup opened anymore with suggested values:


Same SQL as before is generated, but LIKE is changed to =, this is the result of our fix:


New LOV value can be successfully saved to the DB:


Download  - ADFTableApp.zip.

7 comments:

Anonymous said...

Andrejus, First time it works as expected, when you open for second time it again applies the like condition and you can see from the Advances search option inside popup the operator are like null. This happens to me at least in 11.1.1.7.
Do you know about this? or any further setup is missing in the blog!! Thanks, Sara.

Andrej Baranovskij said...

I was testing this with 11.1.2.4, there it worked well. I will need to test on 11.1.1.7

Andrejus

Andrej Baranovskij said...

I have tested my sample with 12c today, it works. This was crated with 12c, so it can't be easily migrated to 11g.

Andrejus

Anonymous said...

Andrejus, can you check the following sequence?
1. Start the application
2. Enter 10, works as expected popup not triggered.
3. Enter 100, works as expected popup not triggered.
4. Enter 10 again, we can see the popup is fired with like operator.(i can see this happens in 12c too)

Hope, here with default vc check, we have to check for the custom view criteria too and looks like we have to override buildViewCriteriaClauses and applyViewCriteria(vc, boolean). Thanks Sara

Andrej Baranovskij said...

Hi Sara,

Yes, now I can reproduce your scenario. I will need to check, how this can be fixed and why it prevents from working as expected.

Regards,
Andrejus

Andrej Baranovskij said...

Hi Sara,

I have updated my sample app to check for LIKE operator in applyViewCriteria(vc, boolean) method as well. Your use case works now as expected. I will post updated sample app.

Regards,
Andrejus

Andrej Baranovskij said...

Hi,

I have posted updated sample app for this use case here: http://andrejusb.blogspot.com/2014/11/suppressing-adf-lov-like-operator.html

Regards,
Andrejus