CODEWITHSUNDEEP
odatasapui5abap
ChristianR
ChristianR

Reputation: 77

How to exclude multiple values in OData call?

I am creating a SAPUI5 application. This application is connected to a backend SAP system via OData. In the SAPUI5 application I use a smart chart control. Out of the box the smart chart lets the user create filters for the underlying data. This works fine - except if you try to use multiple 'not equals' for one property. Is there a way to accomplish this?

I found out that all properties within an 'and_expression' (including nested or_expressions) must have unique name.

The reason why two parameters with the same property don't get parsed into the select options:

/IWCOR/CL_ODATA_EXPR_UTILS=>GET_FILTER_SELECT_OPTIONS takes the expression you pass and parses it into a table of select options.

The select option table returned is of type /IWCOR/IF_ODATA_TYPES=>EDM_SELECT_OPTION_T which is a HASHED TABLE .. WITH UNIQUE KEY property.

From: https://archive.sap.com/discussions/thread/3170195

The problem is that you cannot combine NE terms with OR. Because both parameters after the NE should not be shown in the result set.

So at the end the it_filter_select_options is empty and only the iv_filter_string is filled.

Is there a manual way of facing this problem (evaluation of the iv_filter_string) to handle multiple NE terms?

This would be an example request:

XYZ/SmartChartSet?$filter=(Category%20ne%20%27Smartphone%27%20and%20Category%20ne%20%27Notebook%27)%20and%20Purchaser%20eq%20%27CompanyABC%27%20and%20BuyDate%20eq%20datetime%272018-10-12T02%3a00%3a00%27&$inlinecount=allpages

Normally I want this to exclude items with the category 'Notebook' and 'Smartphone' from my result set that I retrieve from the backend.

Upvotes: 4

Views: 5635

Answers (2)

Mikael G
Mikael G

Reputation: 742

I can't find the source but I recall that multiple "ne" isn't supported. Isn't that the same thing that happens when you do multiple negatives in SE16, some warning is displayed?

I found this extract for Business ByDesign:

Excluding two values using the OR operator (for example: $filter=CACCDOCTYPE ne ‘1000’ or CACCDOCTYPE ne ‘4000’) is not possible.

The workaround I see is to select the Categories you actively want, not the ones you don't in the UI5 app.

I can also confirm that my code snippet I've used a long time for filtering also has the same problem...

* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_MGW_ABS_DATA->FILTERING
* +-------------------------------------------------------------------------------------------------+
* | [--->] IO_TECH_REQUEST_CONTEXT        TYPE REF TO /IWBEP/IF_MGW_REQ_ENTITYSET
* | [<-->] CR_ENTITYSET                   TYPE REF TO DATA
* | [!CX!] /IWBEP/CX_MGW_BUSI_EXCEPTION
* | [!CX!] /IWBEP/CX_MGW_TECH_EXCEPTION
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD FILTERING.

  FIELD-SYMBOLS <lt_entityset> TYPE STANDARD TABLE.
  ASSIGN cr_entityset->* TO <lt_entityset>.

  CHECK: cr_entityset   IS BOUND,
         <lt_entityset> IS ASSIGNED.

  DATA(lo_filter) = io_tech_request_context->get_filter( ).

  /iwbep/cl_mgw_data_util=>filtering(
    exporting it_select_options = lo_filter->get_filter_select_options( )
    changing  ct_data           = <lt_entityset> ).

ENDMETHOD.

Upvotes: 1

Dorad
Dorad

Reputation: 3713

If there is a bug inside /iwcor/cl_odata_expr_utils=>get_filter_select_options which makes it unable to treat multiple NE filters of the same component, and you cannot wait for an OSS. I would suggest to wrap it inside a new static method that will make the following logic (if you will be stuck with the ABAP implementation i would try to at least partially implement it when i get time):

  1. Get all instances of <COMPONENT> ne '<VALUE>' inside a () (using REGEX).
  2. Replace each <COMPONENT> with <COMPONENT>_<i> so there will be ( <COMPONENT>_1 ne '<VALUE_1>' and <COMPONENT>_2 ne '<VALUE_2>' and... <COMPONENT>_<n> ne '<VALUE_n>' ).
  3. Call /iwcor/cl_odata_expr_utils=>get_filter_select_options with the modified query.
  4. Modify the rt_select_options result by changing COMPONENT_<i> to <COMPONENT> again.

Upvotes: 1

Related Questions