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!