How to use case insensitive Filter in OpenUI5
Introduction
We have newcomers into our Techedge’s office in Lucca and we’re training them to become awesome OpenUI5 ninja dev (the path is long but we believe in them).
They’re fresh from IT University and they don’t know yet how to get their hands dirty and develop with SAPUI5. So we gave them some good material to read and watch (SAPUI5 Walkthrough tutorial and Developing Web Apps with SAPUI5).
After they’ve finished this introduction I would like to test what they’ve learned and their adaptive skills. So I’ve started to create some UI5 exercise examples that go from easy to hard.
In one of this example app, I needed to filter the Business Partner’s CompanyName attribute (I’m using Netweaver Gateway Demo ES5).
I’ve added a SearchField, I’ve attached the search event to my implementation on the Controller and tested it.
So in the GIF, I’m trying to filter them with the String “SAP” and everything works as expected but if I try with “Sap” I get no record.
For something like 10 long seconds I’ve scratched my head asking “Why?”
Then the lightbulb lightens up and I’ve remembered that I’ve already handled something like this for a Trenitalia’s project. And that I’ve also submitted a proposal on OpenUI5’s Github issue system.
The problem is all about the case-insensitive filter.
The solution
Here’s my actual solution (pretty easy uh?)
/**
* Method called by the FilterBar that will start a new search
*/
onSearch: function() {
//I'm getting search query like this because we're using a FilterBar and not a SearchField as I said in the blog post ;)
var oFilter = this.getView().getModel("filters");
var sPartnerName = oFilter.getProperty("/partnerName");
var aFilters = [];
if( sPartnerName ) {
aFilters.push( this.createFilter("CompanyName", FilterOperator.Contains, sPartnerName, true) );
}
this.getView().byId("businessPartnerTable").getBinding("items").filter(aFilters);
},
createFilter: function(key, operator, value, useToLower) {
return new Filter(useToLower ? "tolower(" + key + ")" : key, operator, useToLower ? "'" + value.toLowerCase() + "'" : value);
}
The implementation problem
So, if you’re backend system support the tolower operation (like HANA) you’re done.
Otherwise, you need to implement it on your own or you’ll get this error:
{
"error": {
"code": "/IWBEP/CM_MGW_EXPR/000",
"message": {
"lang": "en",
"value": "Function tolower is not supported."
},
"innererror": {
"application": {
"component_id": "OPU-BSE-SDE",
"service_namespace": "/IWBEP/",
"service_id": "GWSAMPLE_BASIC",
"service_version": "0001"
},
"transactionid": "B9E0A2A100BE0080E005B9656F36B72B",
"timestamp": "20180917175331.1571030",
"Error_Resolution": {
"SAP_Transaction": "Run transaction /IWFND/ERROR_LOG on SAP Gateway hub system and search for entries with the timestamp above for more details",
"SAP_Note": "See SAP Note 1797736 for error analysis (https://service.sap.com/sap/support/notes/1797736)",
"Additional_SAP_Note": "See SAP Note 1922246 (https://service.sap.com/sap/support/notes/1922246). This SAP Note contains specific error information.",
"Batch_SAP_Note": "See SAP Note 1869434 for details about working with $batch (https://service.sap.com/sap/support/notes/1869434)"
},
"errordetails": [{
"code": "/IWBEP/CX_MGW_EXPR_OSQL_EXCP",
"message": "Function tolower is not supported",
"propertyref": "",
"severity": "error",
"target": ""
}]
}
}
}
Happy programing!
Hi,
Thanks, that was really clear.
But in my case it seems my backend system doesn't support the tolower operation..
So what do you mean you have to implement it your own ?
A starting point or a hint how to do it will be really great.
Regards.
Filter now contains a "caseSensitive" paramter which you can set to false
Hi,
Can you kindly elaborate a little on this on how the solution can be achieved ?
Maybe a working sample would be really helpful for the community here.
Thank & Regards,
Samson
Hi Samson Moses
As per input from Nils Vanderheyden I just tried it out, it's working with the below line of code.
new Filter({ path: 'name',caseSensitive: false,operator: FilterOperator.Contains,value1: sQuery})
We don't need to have any configuration in the backend, we can achieve it through the UI5 filter itself.
Note:
Actually, tolower() works in OData V2 but not in oData V4 but the above approach works in V4.
Just for others reference https://ui5.sap.com/#/api/sap.ui.model.Filter/constructor
How do you do case-insensitive filters in Fiori Elements (list report)?
You can create a custom method to fetch all binding filters from list report grid table and loop through each filter and modify the sPath and value. This is not the ideal way but just a workaround in Fiori List Report applications. You can call this below method in onBeforeRebindTableExtension function passing oEvent as input parameter.