SAPUI5 filter on associated entity

[teejay]

Hi,

I have a associated entity eg: Workflow Status log and first record in the expanded entity is always the latest record. So, I want to filter Master entity with first record of associated entity with a particular status. The Odata query works perfectly in browser lwith following syntax and only returns Orders that have current status "Approved".

/catalog/Orders?$expand=*&$filter=StatusLog(0)/StatusCode%20eq%20%27Approved%27

But when I try to add this to SAPUI5 filter as following:

aFilters.push(new Filter("StatusLog(0)/StatusCode", sap.ui.model.FilterOperator.EQ, "Approved"));
oTable.getBinding("items").filter(aFilters);

It doesn't work and gives following error :

Error: Type cannot be determined, no metadata for path: /Orders/StatusLog(0)/StatusCode

Thanks,

TJ

[mathias_uhlmann · Accepted solution]

Hi Tarun,

I fear it does not work the way you try to do it. What is working is the support for any and all filters as described in https://sapui5.hana.ondemand.com/#/topic/5338bd1f9afb45fb8b2af957c3530e8f. Could you please check if filtering for items with any log entry with StatusCode "Approved" works for your use case?

Best regards
Mathias.

[teejay · Accepted solution]

Hi Mathias,

Thanks for looking into this..

That is for static filter. I want a dynamic filter that could work on all Status codes not just fixed “Approved”.

Thanks,

TJ

[mathias_uhlmann · Accepted solution]

Hi TJ,

an any-Filter could do that. Let me take the example from https://sapui5.hana.ondemand.com/#/topic/5338bd1f9afb45fb8b2af957c3530e8f:

oTeamsBinding.filter(new sap.ui.model.Filter({
        path :"TEAM_2_EMPLOYEES",operator: sap.ui.model.FilterOperator.Any,
        variable :"employee",
        condition :new sap.ui.model.Filter("employee/AGE", sap.ui.model.FilterOperator.GT,42)}););

This will fetch all teams with at least one employee older than 42. Obviously, when calling the Filter constructor also another value than 42 could be provided. The same is true for your status code use case. You can check also for different status codes or you could check for another property.

Best regards
Mathias.

[teejay · Accepted solution]

Thanks Mathias again .. I tried following format it works in browser but SAPUI5 filter fails to translate into Odata query.

( I am not sure about relevance of variable around COLON, In browser it works with anything before and after colon as long as both have same value.)

/catalog/Orders?$expand=StatusLog&$filter=StatusLog/any(ID:ID/StatusCode%20eq%20%27Approved%27)

oTable.getBinding("items").filter(new sap.ui.model.Filter({
				path: "StatusLog",
				operator: sap.ui.model.FilterOperator.Any,
				variable: "ID",
				condition: new sap.ui.model.Filter("ID/StatusCode", sap.ui.model.FilterOperator.EQ, selectedStatus)
			}));

Failed to get contexts for /catalog/Orders with start index 0 and length 100 - Error: Internal Server Error at Object.createError (https://sapui5.hana.ondemand.com/resources/sap/ui/core/library-preload.js:4433:1689) at https://sapui5.hana.ondemand.com/resources/sap/ui/core/library-preload.js:4548:519 at Array.forEach (<anonymous>) at v (https://sapui5.hana.ondemand.com/resources/sap/ui/core/library-preload.js:4548:267) at https://sapui5.hana.ondemand.com/resources/sap/ui/core/library-preload.js:4548:1210 sap.ui.model.odata.v4.ODataListBinding f @ Log-dbg.js:456

Thanks,

TJ

[mathias_uhlmann · Accepted solution]

Hi TJ,

the backend could not process the generated request. Could you please provide the $filter of the generated request?

Best regards
Mathias.

[teejay · Accepted solution]

Thanks Mathias ! It worked.

[teejay · Accepted solution]

				
oTable.getBinding("items").filter(new sap.ui.model.Filter({
                                path: "StatusLog",
				operator: sap.ui.model.FilterOperator.Any,
				variable: "ID",
				condition: new sap.ui.model.Filter("ID/StatusCode", sap.ui.model.FilterOperator.EQ, selectedStatus)
			}));