adfemg
  1. adfemg
  2. ADFEMG-14

Input List Of Values, autosuggest behavior

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Incomplete
    • Labels:
      None
    • Environment:

      jdeveloper 11.1.1.2 - 11.1.1.6

      Description

      JDeveloper version : ALL
      WebLogic Server version : n.a.
      impact level : high
      why : annoying to users and performance implications
      summary :
      I would like to post about an issue i am straggling with oracle support since jdeveloper 11.1.1.2.
      It is SR 3-5035735611 and referenced Bug 9316480: LOV AUTOCOMPLETE USES LIKE OPER FOR NUMBER, CAUSING FULL TABLE SCAN

      Description:
      By default when you type a value in an input list of values the query is excecuted always with the LIKE operator.

      SELECT Departments.DEPARTMENT_ID,
      Departments.DEPARTMENT_NAME,
      Departments.MANAGER_ID,
      Departments.LOCATION_ID
      FROM DEPARTMENTS Departments WHERE
      (((Departments.DEPARTMENT_ID LIKE ( :vc_temp_1 || '%'))))

      And even if the value you entered is valid the LOV popup will open showing all values starting with the value you entered.
      so in the attached test cases if you type '10' in the departmentId the LOV will open showing 10, 100, 101 etc.

      Impact:
      This is very annoying for users that do heads-down data entry. They make change requests for us to create custom made popups for LOVs.
      Also if the field is a number (like the departmentId) the executed query force full table scan even though it is the primary key.
      In Large List of values this causes performance issues.
      I understand this functionality is designed for the auto-suggest behavior, yet there is no way to override it.

      Proposal:
      We just need the option to disable auto-suggest behavior (on design time) so that the query is executed with '=' operator and the LOV does not open when the value entered is valid.

        Activity

        Hide
        mkoniotakis added a comment -

        Thank you Jan very much for the suggestion.
        It is an interesting approach.

        A workaround that we found lately that might be more future proof and does not require change in each view object is the following:

        In LaunchPopupListener of LOV we check if the entered value exist in LOV and we don't lauch the popup
        It also need to reset the LOV criteria so that next time it opens it does not keep the previous value.
        A sample code is:

        public void reqProviderLaunchPopupListener(LaunchPopupEvent launchPopupEvent) {
        Object submittedValue = launchPopupEvent.getSubmittedValue();

        Row byKeyString = valueExistsInReqProviderLov(submittedValue);

        if(byKeyString!=null)

        { launchPopupEvent.setLaunchPopup(false); ADFUtils.setBoundAttributeValue("RequesterProviderId", submittedValue.toString().toUpperCase()); this.getReqProvider().setSubmittedValue(submittedValue.toString().toUpperCase()); AdfFacesContext.getCurrentInstance().addPartialTarget(this.getReqProvider()); FacesCtrlLOVBinding.ListOfValuesModelImpl listOfValuesModel = null; listOfValuesModel = (FacesCtrlLOVBinding.ListOfValuesModelImpl)this.getReqProvider().getModel(); listOfValuesModel.resetCriteria(); }

        }

        /**

        • Helper method that checks if key exists in list iterator.
        • @param value
        • @return
          */
          private Row valueExistsInReqProviderLov(Object value){
          Key key = new Key(new Object[] { value.toString().toUpperCase() }

          );

        FacesCtrlLOVBinding.ListOfValuesModelImpl listOfValuesModel = null;
        listOfValuesModel = (FacesCtrlLOVBinding.ListOfValuesModelImpl)this.getReqProvider().getModel();
        JUCtrlListBinding ctrlListBinding = null;
        ctrlListBinding = (JUCtrlListBinding)ADFUtils.findCtrlBinding("RequesterProviderId");
        DCIteratorBinding iter = ctrlListBinding.getListIterBinding();
        iter.executeQuery();

        Row byKeyString = iter.findRowByKeyString(key.toStringFormat(true));

        return byKeyString;
        }

        Show
        mkoniotakis added a comment - Thank you Jan very much for the suggestion. It is an interesting approach. A workaround that we found lately that might be more future proof and does not require change in each view object is the following: In LaunchPopupListener of LOV we check if the entered value exist in LOV and we don't lauch the popup It also need to reset the LOV criteria so that next time it opens it does not keep the previous value. A sample code is: public void reqProviderLaunchPopupListener(LaunchPopupEvent launchPopupEvent) { Object submittedValue = launchPopupEvent.getSubmittedValue(); Row byKeyString = valueExistsInReqProviderLov(submittedValue); if(byKeyString!=null) { launchPopupEvent.setLaunchPopup(false); ADFUtils.setBoundAttributeValue("RequesterProviderId", submittedValue.toString().toUpperCase()); this.getReqProvider().setSubmittedValue(submittedValue.toString().toUpperCase()); AdfFacesContext.getCurrentInstance().addPartialTarget(this.getReqProvider()); FacesCtrlLOVBinding.ListOfValuesModelImpl listOfValuesModel = null; listOfValuesModel = (FacesCtrlLOVBinding.ListOfValuesModelImpl)this.getReqProvider().getModel(); listOfValuesModel.resetCriteria(); } } /** Helper method that checks if key exists in list iterator. @param value @return */ private Row valueExistsInReqProviderLov(Object value){ Key key = new Key(new Object[] { value.toString().toUpperCase() } ); FacesCtrlLOVBinding.ListOfValuesModelImpl listOfValuesModel = null; listOfValuesModel = (FacesCtrlLOVBinding.ListOfValuesModelImpl)this.getReqProvider().getModel(); JUCtrlListBinding ctrlListBinding = null; ctrlListBinding = (JUCtrlListBinding)ADFUtils.findCtrlBinding("RequesterProviderId"); DCIteratorBinding iter = ctrlListBinding.getListIterBinding(); iter.executeQuery(); Row byKeyString = iter.findRowByKeyString(key.toStringFormat(true)); return byKeyString; }
        Hide
        leond added a comment -

        Hi

        We are busy testing the code supplied above, but have encountered some inconsistencies. Will give feedback next week.

        I am not pro having workarounds, especially when fixes are imminent and you have to backtrack and remove redundant code!

        We have also hit this scenario and it really does not go down well with our users. Considering that our product table contains close on 500 000 records and when they manually capture product id 20, it displays all product id like '20%'.

        I have read Bug 8983396: EXTRA QUERIES EXECUTED FOR LOV AND PERFORMANCE ISSUES and it has a Fixed in Version entry, but the bug still persists.

        We will log another SR and reference the above mentioned SR and hope this add impetus to a future fix. Until then I will keep the users at bay!

        Leon.

        Show
        leond added a comment - Hi We are busy testing the code supplied above, but have encountered some inconsistencies. Will give feedback next week. I am not pro having workarounds, especially when fixes are imminent and you have to backtrack and remove redundant code! We have also hit this scenario and it really does not go down well with our users. Considering that our product table contains close on 500 000 records and when they manually capture product id 20, it displays all product id like '20%'. I have read Bug 8983396: EXTRA QUERIES EXECUTED FOR LOV AND PERFORMANCE ISSUES and it has a Fixed in Version entry, but the bug still persists. We will log another SR and reference the above mentioned SR and hope this add impetus to a future fix. Until then I will keep the users at bay! Leon.
        Hide
        chriscmuir added a comment -

        Leon, can you confirm the new SR number please?

        CM.

        Show
        chriscmuir added a comment - Leon, can you confirm the new SR number please? CM.
        Hide
        chriscmuir added a comment -

        Without a new SR/bug to reference I'll close this issue in 1 week.

        Show
        chriscmuir added a comment - Without a new SR/bug to reference I'll close this issue in 1 week.
        Hide
        chriscmuir added a comment -

        Issue closed, no further information supplied.

        Show
        chriscmuir added a comment - Issue closed, no further information supplied.

          People

          • Assignee:
            chriscmuir
            Reporter:
            mkoniotakis
          • Votes:
            1 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: