Autocomplete box with OData service

[Former Member]

Hello,

I want to include an autocomplete box in my SAPUI5 Application that uses an OData Service for retrieving data. The OData Service is linked to a calculation view on the HANA System.

The problem is that the autocomplete box does show entries but not all of them. It seems that only -for example- the top 100 entries are being read by the OData service.

These are my lines:

// Define Data Model (ODATA service)




var oModelData = new sap.ui.model.odata.ODataModel("/services/odata/data.xsodata/", true);






//Create a AutoComplete control and bind the items aggregation
var oAutoComplete = new sap.ui.commons.AutoComplete({
     tooltip: "Enter a name",
     displaySecondaryValues: true,
     items: {
          path: "/DATA",
          template: new sap.ui.core.ListItem({text: "{DESCRIPTION}", additionalText: "{ID}"})
     }
});



// bind model to OData Service
oAutoComplete.setModel(oModelData);



//Define a custom filter
oAutoComplete.setFilterFunction(function(sValue, oItem){
     return jQuery.sap.startsWithIgnoreCase(oItem.getText(), sValue) || jQuery.sap.startsWithIgnoreCase(oItem.getAdditionalText(), sValue);
});

Is it possible to retrieve all data stored in the table? Since the available data is very big (approximately 4000 entries) I wanted to use OData service instead of getting all data and store it in an array.

Thanks in advance for your help.

Kind regards,

Stefan

[former_member182372 · Accepted solution]

oModelData.setSizeLimit(300);

[Former Member]

Did you figure out how to accomplish the autocomplete filtering by both text and additionalText?  We have virtually the same problem as in the OP.  This line by Maksim fixed the issue with the autocomplete only showing the top ~100 records (although there is a small performance hit):

oModelData.setSizeLimit(300);

But we are not able to have the autocomplete work based on comparing what is entered to the field represented by additionalText. i.e. If I enter a material code starting with 006, the autocomplete works as expected, showing both the material code and the additional text; if I enter a Material Name into the autocomplete, nothing comes up. Here is my code:

 var oItemTemplate = new sap.ui.core.ListItem({text: "{Material}", additionalText: "{MaterialName}"});
 var oItemFilters = [ new sap.ui.model.Filter("MaterialType", sap.ui.model.FilterOperator.EQ, "ZFG"),
              new sap.ui.model.Filter("Material", sap.ui.model.FilterOperator.StartsWith, "006")];
 
 var oAuto1 = new sap.ui.commons.AutoComplete({
  tooltip: "Enter a material",
  maxPopupItems: 8,
  displaySecondaryValues: true,
  items: {
   path: "/MATERIAL_LOOKUP",
   template: oItemTemplate,
   filters: oItemFilters
  },
  change: oController.MaterialDescLookupEvent
 });
 
 oModel.setSizeLimit(1500);
 oAuto1.setModel(oModel);

Thanks for your help,

Andy

[Former Member]

Looking further, I see that the setFilterFunction works for this, which I think was posted above.  I was thinking that would mess with my filtering at the data level, but it appears it is only filtering the text on the autocomplete, which is good, just confusing to me.

oAuto1.setFilterFunction(function(sValue, oItem){
  return sValue.length > 2 &&
         (jQuery.sap.startsWithIgnoreCase(oItem.getText(), sValue) ||
          jQuery.sap.startsWithIgnoreCase(oItem.getAdditionalText(), sValue));
});

[Former Member]

Andy using setFilterFunction method allows to pick the key on mouse click only not on keyboard enter can you tell why its behaving like this??

[Former Member]

Well, the filter works if I include it like this into my autocomplete box

var oAutoComplete = new sap.ui.commons.AutoComplete({

     tooltip: "Enter Name or Description",

     maxPopupItems: 30,

     displaySecondaryValues: true,

     items: {

          path: "/DATA",

         filters: filters,

          template: new sap.ui.core.ListItem({text: "{DESCRIPTION}", additionalText: "{NAME}"})

               }   

});

The problem is that this overrides the custom filter I used prior to provide a 'fuzzy' search which works for "DESCRIPTION" and "NAME":

// Define a custom filter

oAutoComplete.setFilterFunction(function(sValue, oItem){

     return jQuery.sap.startsWithIgnoreCase(oItem.getText(), sValue) || jQuery.sap.startsWithIgnoreCase(oItem.getAdditionalText(), sValue);

});

Is it possible to include the filter option <<show only values which are not NULL in column "DESCRIPTION">> into the custom filter which provides the fuzzy search?

[Former Member]

Thanks Jason and Maksim. When I change the SizeLimit the odata service exposes all data.

Furthermore I would like to 'pre' filter the data the odata service exposes because some of the data have no entry in column "DESCRIPTION".

Is it possible to only show the entries were "DESCRIPTION" is not null? Something like adding a filter to the odata service like this:

var filters = new Array();  
var filterDescription = new sap.ui.model.Filter("DESCRIPTION", sap.ui.model.FilterOperator.NE, null);  
filters.push(filterDescription);  

and adding this filter to the autocomplete box?

Thanks again for your help!

[former_member182372]

did you try what Jason suggested?

var filters = new Array();

var filterDescription = new sap.ui.model.Filter("DESCRIPTION", sap.ui.model.FilterOperator.NE, null);

filters.push(filterDescription);

1.      oAutoComplete.getBinding("items").filter(filters); 

[Former Member]

I tried your lines but it throws an error:

' <what i typed in the box> has no method getParameter'

could you explain where and how I have to insert your statement?

[jmoors]

Hi Stefan,

That wasn't actual code, just for example, I'll see if I can put together later, however if you look at the examples in the documentation, it's possible to build dynamic items in the suggest event.

https://sapui5.hana.ondemand.com/sdk/#test-resources/sap/ui/commons/demokit/AutoComplete.html#__2

I'm not sure how well this would perform if you are making a oData on input of every key press, so you might need to throttle the number of calls.

Regards,

Jason

[jmoors]

Have you tried applying a filter to the oData model, you might need manually set the filter in the suggest event??

https://sapui5.hana.ondemand.com/sdk/#docs/guide/BindingAggregations.html

Not sure how well it will perform if you have to make a new request on live change, but this it's your only option if you're not returning all the results to the client.

Regards,

Jason

[Former Member]

I could use a filter but then it would be a predefined filter, isn't it? Instead the user shall be able to start writing and the autocomplete box suggests entries from the table.

[jmoors]

I think you should be able to update the filter based on what the user has entered, either on LiveChange or suggest event.

Something like:

suggest: function(oEvent){
     var sValue = oEvent.getParameter("suggestValue");

     var oFilter1 = new sap.ui.model.Filter("name", sap.ui.model.FilterOperator.StartsWith, sValue);

     oAutoComplete.getBinding("items").filter([oFilter1], sap.ui.model.FilterType.Application);
}