Filtering on Association property in Fiori Element app via ABAP CDS Virtual Elements

Some time back there was a auestion about filtering the Fiori Element application by association property of the CDS view.

https://answers.sap.com/questions/758309/fiori-elements-filter-based-on-association-propert.html

At that time, I debugged both the UI5 Fiori Elements APIs and backend odata and found that Fiori Elements & SAP Gateway both doesn’t support it.

Actually It supports the filter on association property but only with the associations having cardinality of 1..1 and it doesn’t support 1..n .  Not sure if something has changed in between as I debugged it last year Feb 2019.

In fact, I was planning to blog about it 😀 funny, I didn’t have a solution at that time for that but still created a blog post and hoped in that process I will figure out the solution 🙂 🙂 believe me it works 🙂 😀 and it is one of the best way to learn & dig deep into the subject 🙂  But I was lazy and left that blog post in the draft state 😀

Now recently there was some activity in that question asking for a proper solution and I replied with a workaround of using the table function but now again I found a different way, which uses the Virtual Elements in ABAP CDS 🙂

So in this blog post, I will show the way to filter the data by association property with 1..n cardinality using the Virtual Elements in CDS view. I believe this will be applicable for ABAP Restful programming model as well as the SADL layer(Data fetching logic of CDS view) is common for both ABAP programming model for Fiori and RAP.

For filtering via AMDP/Table Function in CDS, check the below blog.

https://blogs.sap.com/2020/01/27/filtering-on-association-property-using-table-function-amdp-vs-abap-virtual-elements/

Solution

I will use an example of Carrier CDS view, which has an association(1..n cardinality) with Flights CDS view.

OData service is generated based on the annotations and consumed in the Fiori Elements List Report application.

Now the functionality to fetch all the carries is based on the “Country From”(COUNTRYFR) should be provided. But this field is not part of SCARR table, it is part of the association table SPFLI(1..n).

For this we will use the virtual element annotations and create a class as shown in the below steps.

Step:1

Creating both the root(parent) and child cds views.

Root CDS view – ZC_CARRIER

@AbapCatalog.sqlViewName: 'ZVCCARRIER'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Car1 Test'
@OData.publish: true
define view ZC_CARRIER
  as select from scarr
  association [0..*] to ZC_SPFLI as _spfli on $projection.carrid = _spfli.carrid
  association [0..*] to sgeocity as _geoCity on $projection.country_from = _geoCity.country
{
      @UI.lineItem: [
      { position: 10 }]
  key scarr.carrid,
      @UI.selectionField: [{ position: 20 }]
      @UI.lineItem: [{ position: 20 }]
      scarr.carrname,
      @UI.selectionField: [{ position: 30 }]
      @UI.lineItem: [{ position: 30 }]
      scarr.currcode,
      @UI.lineItem: [{ position: 50 }]
      scarr.url,
      @ObjectModel.virtualElement
//      @ObjectModel.virtualElementCalculatedBy: 'ABAP:ZCL_VE_CAR1'
      @ObjectModel.filter.transformedBy: 'ABAP:ZCL_VE_CAR1'
      @EndUserText.label: 'Country From'
      @UI.selectionField: [{ position: 50 }]
      @Consumption.valueHelp: '_geoCity'
      cast( '   ' as land1 preserving type ) as country_from,

        /* Expose the associations*/
      _spfli,
      _geoCity
}

Child CDS view

@AbapCatalog.sqlViewName: 'ZVCSPFLI'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Spfli1'
define view ZC_SPFLI
  as select from spfli
  association [0..1] to ZC_CARRIER as _carrier on spfli.carrid = _carrier.carrid
{
  key spfli.carrid,
  key spfli.connid,
      spfli.countryfr,
      spfli.cityfrom,
      spfli.airpfrom,
      spfli.countryto,
      spfli.cityto,
      spfli.airpto,
      _carrier
}

They both are pretty simple CDS views, but the Root CDS view has the new Virtual Element “country_from” with annotations related to Virtual Elements.

More about it in SAP Help link:

https://help.sap.com/viewer/cc0c305d2fab47bd808adcad3ca7ee9d/7.51.7/en-US/a7fc007921d44263b09ccc092392b05f.html

@ObjectModel.virtualElement

Above annotation tells that the property is a Virtual Element.

@ObjectModel.filter.transformedBy: 'ABAP:ZCL_VE_CARRIER'

above one will tell the framework that if  the filter is applied on the Virtual Element then call the ABAP class “ZCL_VE_CARRIER” to get the filter condition.

Step:2

Let’s create the class and implement the interface as shown below:

CLASS ZCL_VE_CARRIER DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .
  PUBLIC SECTION.
    INTERFACES if_sadl_exit .
    INTERFACES if_sadl_exit_filter_transform .
  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.

CLASS ZCL_VE_CARRIER IMPLEMENTATION.
  METHOD if_sadl_exit_filter_transform~map_atom.
    DATA(country_from) = cl_sadl_cond_prov_factory_pub=>create_simple_cond_factory( )->element( '_SPFLI.COUNTRYFR' ).
    ro_condition = country_from->equals( iv_value ).
  ENDMETHOD.
ENDCLASS.

In the above code,  I created a condition on the Association Property “_SPFLI.COUNTRYFR” and sent is back to the framework.

For more information, check the SAP help from the step1, It’s just a basic code and in production scenarios, it should be able to handle the different type of of filter conditions(EQ, Contains..,).

That’s it, just run the application to see the new Country From Filter, select  a value and apply the filter to get all the carries based on the Country From:

Some more Technical Details for the Geeks

When the search is executed in the Fiori app, SADL layer will request the Virtual Element filters before performing the actual CDS view Select execution below:

These conditions taken from that exit will be converted to a where condition by SADL layer in the below format:

EXISTS ( SELECT ZC_CAR1~CARRID 
           FROM ZC_CAR1 AS ZC_CAR1 
LEFT OUTER JOIN ZC_SPFLI1 AS nav_TO_SPFLIZC_SPFLI1_0 
             ON ZC_CAR1~CARRID = nav_TO_SPFLIZC_SPFLI1_0~CARRID 
          WHERE nav_TO_SPFLIZC_SPFLI1_0~COUNTRYFR = 'DE' AND ( ZC_CAR1~CARRID = ZC_CAR1__BSAX~CARRID OR ZC_CAR1~CARRID IS NULL AND ZC_CAR1__BSAX~CARRID IS NULL ) )                                                                                                                                                                                                        

It’s a general subquery, but I never used it and never knew about it (What a shame 😀 😀 ) , thought it is a new syntax for a second there 😛 😛 😀 😀

BTW I found this virtual elements association filtering concept when I was analysing a standard application to understand the behavior of the Virtual Elements 🙂 🙂

Also, this might be heavy on the performance due to the Sub Query, not sure though. If yes, this can be handled using the Table Function, which I mentioned in the Original Question’s answer. Maybe in an another blog post 🙂

<Update>: Performance is better with Virtual Elements via subquery, check the below blog post.

https://blogs.sap.com/2020/01/27/filtering-on-association-property-using-table-function-amdp-vs-abap-virtual-elements/

So that’s it folks 🙂 and please share your thoughts and suggestions about this.

And Don’t forget to check the blogging initiative by Phil Cooley  This year is the year of SAP blogs 2020YearofSAPBlogs  😀 🙂  I will be creating atleast one blog post per month 🙂 So don’t wait to share your SAP findings and knowledge. Learn, Share & Repeat 🙂

Thanks,

Mahesh