Update data after Filter

[mantrishekar]

Hello All,

I have a sap.m. table.From that table am selecting few records based on some filter conditions and applying filters.

After filter condition am able to see the record count using iLength Property.

But my requirement is to get the filtered records and i need to download these records to excel.

Could any body please help me how to get the filtered records.

[ugurkaya · Accepted solution]

Hey Mantri,

Did you check this example?

SAPUI5 Explored

You can change following lines of onDataExport event of the controller to filter which rows to export.

1. // binding information for the rows aggregation
2. rows : {
3. path : "/ProductCollection?$filter=XXXXX"
4. },

[mantrishekar · Accepted solution]

Hi Ugur,

Could you Please explain briefly. I am unable to follow that code.

[ugurkaya · Accepted solution]

Mantri,

This is an example of downloading table contents as csv format, basicaly if you follow the code in the controller which downloads the data this is how its done.

SAPUI5 Explored

When you click the upper right button "download" on the table view, this event is triggered on related controller.

1. onDataExport : sap.m.Table.prototype.exportData || function(oEvent) {
3. var oExport = new Export({
5. // Type that will be used to generate the content. Own ExportType's can be created to support other formats
6. exportType : new ExportTypeCSV({
7. separatorChar : ";"
8. }),
10. // Pass in the model created above
11. models : this.getView().getModel(),
13. // binding information for the rows aggregation
14. rows : {
15. path : "/ProductCollection"
16. },
18. // column definitions with column name and binding info for the content
20. columns : [{
21. name : "Product",
22. template : {
23. content : "{Name}"
24. }
25. }, {
26. name : "Product ID",
27. template : {
28. content : "{ProductId}"
29. }
30. }, {
31. name : "Supplier",
32. template : {
33. content : "{SupplierName}"
34. }
35. }, {
36. name : "Dimensions",
37. template : {
38. content : {
39. parts : ["Width", "Depth", "Height", "DimUnit"],
40. formatter : function(width, depth, height, dimUnit) {
41. return width + " x " + depth + " x " + height + " " + dimUnit;
42. },
43. state : "Warning"
44. }
45. // "{Width} x {Depth} x {Height} {DimUnit}"
46. }
47. }, {
48. name : "Weight",
49. template : {
50. content : "{WeightMeasure} {WeightUnit}"
51. }
52. }, {
53. name : "Price",
54. template : {
55. content : "{Price} {CurrencyCode}"
56. }
57. }]
58. });
60. // download exported file
61. oExport.saveFile().catch(function(oError) {
62. MessageBox.error("Error when downloading data. Browser might not be supported!\n\n" + oError);
63. }).then(function() {
64. oExport.destroy();
65. });
66. }
68. });

You can adapt his easily to your scenario & table structure.

This piece of code indicates which rows of the table will be downloaded to your file.

1. // binding information for the rows aggregation
2. rows : {
3. path : "/ProductCollection"
4. },

If you want to download all the data you see without filtering, this is fine.

In your case, you want to download a filtered data so you can change the path above with your filtered item binding. This is just an example

1. // binding information for the rows aggregation
2. rows : {
3. path : "/ProductCollection?$filter=Name eq 'Apple'"
4. },

So this should download the product(s) with the name 'Apple' into file, not the entire data on your table.

I hope this gives you an idea.

Regards

Ugur

[mantrishekar · Accepted solution]

But for my scenario i have multiple filters like

1)Line(Here i need to get from T1-T4)

2)Machines(I need to get different machined )

Like this i have filters .how can i pass this logic in that path

[ugurkaya · Accepted solution]

Well, if this path should be same as your table's item binding path at the time when download is requested. You can set it dynamically by:

1. // binding information for the rows aggregation
2. rows : {
3. path : this.getView().byId("table_id").getBinding("items");
4. },

This should give you the filtered binding of your table.

[mantrishekar · Accepted solution]

Hi Ugur,

in Debugging mode after filtration i saw the value using

this.getView().byId("table_id").getBinding("items");

It is showing undefined.

[ugurkaya · Accepted solution]

Hey Mantri,

"table_id" should be the id of your table and the item binding should be set already when you want to do this. I would suggest you to download and modify that example I have sent you to have a better understanding. Furthermore, if you could post your codes, would be easier to help you out.

Regards

Ugur

[mantrishekar · Accepted solution]

HI,

in place of table_id i placed my table id.

I am sharing the code.

Please do suggest some changes to get only the filtered records.

var getlineFromValue		= sap.ui.getCore().byId("lineFromFilterId").mProperties.value;
	var getlineToValue		= sap.ui.getCore().byId("lineToFilterId").mProperties.value;
	var getMachineFromValue	= sap.ui.getCore().byId("machineFromFilterId").mProperties.value;
	var getMachineToValue	= sap.ui.getCore().byId("machineToFilterId").mProperties.value;
	var getSKUFromValue		= sap.ui.getCore().byId("skuFromFilterId").mProperties.value;
	var getSKUToValue		= sap.ui.getCore().byId("skuToFilterId").mProperties.value;
	var getDateFromValue		= sap.ui.getCore().byId("dateFrom").mProperties.value;
	var getDateToValue		= sap.ui.getCore().byId("dateTo").mProperties.value;
	debugger;
	var lineFilter	= new sap.ui.model.Filter({
		path	: "line",
		operator : sap.ui.model.FilterOperator.BT,
		value1   : getlineFromValue,
		value2   : getlineToValue});
	var machineFilter	= new sap.ui.model.Filter({
		path	: "machine",
		operator : sap.ui.model.FilterOperator.BT,
		value1   : getMachineFromValue,
		value2   : getMachineToValue});
	var skuFilter	= new sap.ui.model.Filter({
		path	: "line",
		operator : sap.ui.model.FilterOperator.BT,
		value1   : getSKUFromValue,
		value2   : getSKUToValue});
	var dateFilter	= new sap.ui.model.Filter({
		path	: "line",
		operator : sap.ui.model.FilterOperator.BT,
		value1   : getDateFromValue,
		value2   : getDateToValue});
	var finalFilter = new sap.ui.model.Filter({
			filters :[
			lineFilter,
			machineFilter,
			skuFilter,
			dateFilter,
			],
			or : true});
	var selRecords   = sap.ui.getCore().byId("RuleTableItems");
	selRecords.getBinding("items").filter(finalFilter,sap.ui.model.FilterType.Application);
	this.getView().byId("("RuleTableItems");").getBinding("items");

}

[ugurkaya · Accepted solution]

Hey Mantri,

This synax is wrong, -> this.getView().byId("("RuleTableItems");").getBinding("items");

Should be var oBinding = this.getView().byId("RuleTableItems").getBinding("items");

console.log("item binding",oBinding); should let you see the binding on your console, but this is not really necessary, it will just give you an idea of what you have on your table.

Anyway, the code piece you have given is where you are filtering your table binding, i see. This should be working as you intended.

Lets say that you put a button on your screen to download those filtered entries from table according to the filtered binding of the table as you did. This button should trigger onDataExport event in your controller ( you should define this ) as given in the example, with minor differences. You should change the column names and binding according to your scenario.

1. // binding information for the rows aggregation
2. rows : {
3. path :this.getView().byId("RuleTableItems").getBinding("items");
4. },

You can give the path like this if you want to download the filtered data when the button is pressed.

1. columns : [{
2. name : "Your Table Column Name 1", -> change this
3. template : {
4. content : "{XXXX}" -> change this
5. }
6. }, {
7. name : "Your Table Column Name 2", -> change this
8. template : {
9. content : "{YYYY}" -> change this
10. }
11. }, {

....

....

...

...

You should prepare data format to be transferred to the file by giving the columns.

I hope that will give you an idea.

Regards

Ugur.

[mantrishekar · Accepted solution]

Still am getting the same error after changing the code

Uncaught TypeError: Cannot read property 'getBinding' of undefined

[mantrishekar · Accepted solution]

Hi Ugur,

This is my code to dowload data to excel.Please let me know if any changes need to be done.

I am able to download whole data.But i want to download only filtered data.Please let me know if any changes need to be done

var oExport = new sap.ui.core.util.Export({

           exportType: new sap.ui.core.util.ExportTypeCSV({

            separatorChar: " "

            }),

            models: oModel,

            rows: {

            //path: this.getView().byId("RuleTableItems").getBinding("items")

            path : selRecords.getBinding("items").getPath()

            },

            columns: [

                     {

                     name: "Line",

                     template: {

                     content: {

                     path: "line"

                     }

                     }

                     },

                     {

                     name: "Machine",

                     template: {

                     content: {

                     path: "machine"

                     }

                     }

                     }

                     ]

            });

     oExport.saveFile().always(function() { 

       this.destroy(); 

    });

[ugurkaya · Accepted solution]

Hey Mantri,

I assume that you can reach the raw path from:

path : selRecords.getBinding("items").getPath()

This brings the EntitySet only, not the filters.

Could you try this:

path : selRecords.getBinding("items").sPath + selRecords.getBinding("items").sFilterParams;

[mantrishekar · Accepted solution]

Hey Ugur,

selRecords.getBinding("items").sFilterParams

For this it is showing undefined value

[ugurkaya · Accepted solution]

Hey Mantri,

Could you please console.log your item binding when download button is clicked and put a screenshot like that please.

console.log(this.getView().byId("RuleTableItems").getBinding("items"));

This is my item binding for example, you can see the filter params.

[mantrishekar · Accepted solution]

Hey Ugur,

console.log(this.getView().byId("RuleTableItems").getBinding("items"));

This statements shoing undefined.

So i used

sap.ui.getCore().byId("RuleTableItems").getBinding("items")

I attached the screen shot.Please go through it and let me know where am doing mistake.

[ugurkaya · Accepted solution]

Hey Mantri,

Could you check aFilters array, you should be able to read the filters from that. So you can concatenate your path+filters to form your filtered binding path.

[mantrishekar · Accepted solution]

Hey Ugur,

You are right.Exactly i have done same as a last try.It Worked fine.Now am able to download filetered records to excel.