Fiori Elements: Filter based on association property

[sascha_weidlich2]

Hi folks,

im facing a strange Problem in the "ListReport" of the Fiori Elements and hope you can help me to fix my issue. 🙂

My requirement is to provide some filter-options for the starting Table based on an association. (for example the List shows some SalesOrder Headers and on the Object Page also the SalesOrder Items are provided. And now the User wants just to see the SalesOrder Headers where an Item is included with the Product "XY")

After looking into the CDS-Help i thought that the following Code would work:

define view ZCDS_C_SalesOrder_00 
  as select from ZCDS_I_SalesOrder_00 as SalesOrder 
  association [0..*] to ZCDS_C_SalesOrderItems_00 as _Items on _Items.SalesOrderUUID = SalesOrder.SalesOrderUUID

{
    ....

    @UI.selectionField: [{ element: '_Items.Product', position : 2 }]
    _Items,
}

But unfortunately the Filter for the Product is still not visible in the Filterbar, even under "more Filters".

The CDS-Help (Link to Help) says for UI.selectionField.element the following:

Must be used when an association is annotated, the value is a path to an element of the associated view. You use this option if you want to filter a table for a column that is not defined in your CDS view but in another CDS view.

Example:You define a sales order view in which you want to filter business partners for their country of origin. However, these country information are not maintained in the sales order view but in the business partner view.

So i thought this is exactly what im looking for, but it seems not. 😄

Has anyone an idea how to achieve the requirements with CDS Annotations?

As always any kind of informations is appreciated.

Thanks for your help!

Sascha

[sascha_weidlich2]

After some further investigations i figured out that even the generated annotations in the service are correct. So i think this is something UI5-related, but im clueless how to fix this?

<Annotation Term="UI.SelectionFields">   
  <Collection>
    <PropertyPath>to_Items/Product</PropertyPath>
  </Collection>
</Annotation>

[susmitha_pritam]

Hi Sascha,

We have a similar requirement in the application we are developing.

The Associated view has a 0:* cardinality with the main View.

I created Views similar to the one you had mentioned and tested the same.

But, I found Duplicate entries for the Sales Orders when there are multiple items with same product.

Please find below the code and screen shot.

Could you please let us know if you were able to achieve the same?

If so, kindly let me know the steps.

define view ZC_SO_HEAD
  as select from I_SalesDocument as head
  association [0..*] to ZC_SO_ITEM as _Items   on _Items.SalesDocument = $projection.SalesDocument
  association [0..1] to ZC_SO_ITEM as _VHItems on _VHItems.SalesDocument = $projection.SalesDocument
{
....

      @ObjectModel.association.type:  [ #TO_COMPOSITION_CHILD ]
      @UI.selectionField: [{ element: '_Items.Material', position : 4 }]
      _Items,
      _VHItems
}

[sunil1101]

@susmitha_pritam, First of all this approach is not good as it changes the Root node means Header CDS is already changed same primary key will be repeated.
As a workaround duplicate entry by implementing get_entitySet method of DPC_EXT, or if you are using RAP you can use Custom Query implementation.

[maheshpalavalli · Accepted solution]

Hi Sascha Weidlich,

When I was checking your scenario, I found the below two things:

1. SAP UI5 doesn't support searching the associations of cardinality 1..* it only supports 1..1. So you cannot check the items but we can check the sales orders based on the business partner country (1..1) like the example given in the help.

https://help.sap.com/doc/saphelp_nw75/7.5.5/en-US/17/d5ee74dd4e4e8f802aaf4fdd51fbc6/frameset.htm

2. So I went a head and tested the odata service directly by sending the filter for the items. But that returned an error.

Left hand expression of memberaccess operator has wrong cardinality (to many not allowed)

So i guess only 1..1 cardinality search is supported.

BR,

Mahesh

[sascha_weidlich2 · Accepted solution]

Hi Mahesh Kumar Palavalli,

thanks for your Input, this lead me to the right direction. 🙂

You're right, at the moment the "Fiori Element"-Concept doesn't support the "to many"-cardinality.

However, i found a "sneaky" workaround to achieve my requirement. See the following code:

define view ZCDS_C_SalesOrder_00 
  as select from ZCDS_I_SalesOrder_00 as SalesOrder 
  association [0..*] to ZCDS_C_SalesOrderItems_00 as _Items on _Items.SalesOrderUUID = SalesOrder.SalesOrderUUID
  association [0..1] to ZCDS_C_SalesOrderItems_00 as _VHItems on _Items.SalesOrderUUID = SalesOrder.SalesOrderUUID
{
    ....

    _Items,
    _VHItems
}<br>

With a second association (and wrong cardinality, which leads to an warning) the filter will appear in the SmartFilter-Bar.

Thanks for your Help!

Regards,

Sascha

[maheshpalavalli · Accepted solution]

Hi Sascha Weidlich

Awesome, didn't think in this direction. Super!!

One Question, I hope you are not getting any duplicate data?

This looks so intresting, I have to debug and see what exactly is happeing in the system 😛

BR,

Mahesh

[susmitha_pritam · Accepted solution]

Hi Mahesh,

Did you get a chance to look into this solution?

As you can see from my above post, I did try to implement the same however, I am facing issues with duplicate data.

Any help will be really appreciated.

Thanks in advance.

Susmitha Pritam Durai

[maheshpalavalli · Accepted solution]

Hi Susmitha Pritam Durai,

I came to conclusion that if we do it with 1..1 cardinality data, it works fine else if it is by default 1..n and if we override it with 1..1, we will end up with duplicate data.. Not sure if any other approach is there though.

Thanks,

Mahesh

[susmitha_pritam · Accepted solution]

Hi maheshkumar.palavalli ,

Is there any other approach you have tried to achieve this 1:N cardinality on filter search?

Kindly let me know the same.

Thanks & Regards,

Susmitha Pritam

[maheshpalavalli · Accepted solution]

Not sure Susmitha Pritam Durai if it is possible,

But check if this approach is possible or not, use amdp and table function and concatenate multiple item rows column into single column using string aggregate function and maybe whatever the user selects from the UI, you could use contains and filter that out? Not sure if it will work though.

[philipdavy · Accepted solution]

Hello maheshkumar.palavalli

Can you please help me in finding the MetadataAnalyser-dbg.js file? I have developed a custom Fiori Elements app and i would like to navigate to the exact file you mentioned in the screenshot.

My source structure looks somehow like below.

[maheshpalavalli]

martin.s5 susmitha_pritam Sascha Weidlich sunil1101

You can use the below blog as reference to do this using virtual elements

https://blogs.sap.com/2020/01/16/filtering-on-association-property-in-fiori-element-app-via-abap-cds...

Or you could use table function to concatenate all the searchable fields into one line using string_aggr function and use "Contains" filter to search from the UI.

Thanks,
Mahesh

[MartinSommer]

Hi Sascha, I was wondering about the results of your "sneaky" workaround 😉 are you using CDS based oData? Does the filtering work?

Best regards

[anoukvandenengel]

Hi everyone,

I stumbled upon this question because I had a very similar requirement: adding a filter on the list report page for a property of a child entity (i.e. filtering on an associated entity with a one-to-many cardinality). In my RAP BO and Fiori Elements app, I eventually managed to solve this using a virtual element with a custom filter.

In the implementation of the filter transformation method (if_sadl_exit_filter_transform~map_atom), I selected all entries from the child entity where the relevant field contains the filter value (iv_value). Next, I looped over the retrieved entries and, for each one, created a condition on the field used to match the association between the parent and the child. (In my scenario this is also the unique key of the parent entity, so I could directly filter on this field, but perhaps there are some use cases where it is necessary to first obtain the corresponding entries of the parent entity). I then chained these individual conditions together using the OR operator, as explained on this SAP Help page. Finally, I added a fallback condition to handle the case when no matching entries are found, ensuring that the filter is never empty.

Below is a generic code sample where you can replace the placeholders (e.g. <your_child_entity>, <matching_field>, <parent_key>, and <filter_field>) with your actual names.

I hope this solution will help anyone else with the same requirement!

METHOD if_sadl_exit_filter_transform~map_atom.

  DATA lt_child_entries TYPE TABLE OF <your_child_entity>.
  DATA ls_child_entry   TYPE <your_child_entity>.
  DATA lo_field_filter  TYPE REF TO if_sadl_simple_cond_element.
  DATA lo_condition     TYPE REF TO if_sadl_cond_provider_generic.

  " Get all entries from the child entity where <filter_field> equals iv_value
  SELECT <matching_field> FROM <your_child_entity>
    WHERE <filter_field> = @iv_value
    INTO TABLE @LT_child_entries.

  LOOP AT lt_child_entries INTO ls_child_entry.
    " Create a condition for the parent entity key field
    lo_field_filter = cl_sadl_cond_prov_factory_pub=>create_simple_cond_factory( )->element( '<parent_key>' ).
    lo_condition = lo_field_filter->equals( ls_child_entry-<matching_field> ).

    " Chain conditions with OR
    IF ro_condition IS INITIAL.
      ro_condition = lo_condition.
    ELSE.
      ro_condition = ro_condition->or( lo_condition ).
    ENDIF.

    CLEAR lo_field_filter.
  ENDLOOP.

  " Fallback: If no conditions were created, apply a default condition
  IF ro_condition IS INITIAL.
    lo_field_filter = cl_sadl_cond_prov_factory_pub=>create_simple_cond_factory( )->element( '<parent_key>' ).
    ro_condition = lo_field_filter->equals( '' ).
  ENDIF.

ENDMETHOD.

[ddebiase]

In my case, having this in my cds interface view:

I just added this in my metadata extension of the consumption view:

and everything worked as expected!