Search on multiple fields at different nodes from a single field on UI using BOPF and CDS

[Former Member]

I am involved in a project in the Banking domain at present. More specifically, loans management in banking. We are using BOPF BOs generated out of CDS views and use smart tables for the UI. Our BO structure is as follows.



From the root node to the child nodes, the association is 1:N. Being a BOPF object generated from CDS views, these 3 nodes have their own CDS views and connected by associations. We have a table on the UI based on the root node. So it displays some of the fields on the header. The requirement is as follows:

1. Introduce three search fields on the table called 'Account Number', 'BP' and 'BP Category'. When a search parameter is given in 'Account Number', then search must be made on 'Deal Number' field on the root node, 'Tranche Number', 'Drawdown number' and 'Contract number' fields at the deal item node. BP and BP category search fields should search on the respective fields on the Responsible node.


2. After searching, the result should be displayed at the header level. For example, if deal 85 has a drawdown 90 and I enter 90 in the search field, then the table should display record 85 as that is the header.


3. Search maybe done with wildcard characters, LT, GT, NE etc.


4. Search maybe done on more than one field.


5. Search should be done on active as well as draft entries.

With such a requirement, we considered the following approaches:

1. Using path expressions:From the UI, it is possible to fire a filter condition on a field in an association. But for this to work, the association should have cardinality 1 to 1. In our case that is not possible since there can be multiple items for the header and multiple sub nodes for each item (as mentioned in SAP Help document). So we cannot go ahead with this approach.


2. Use an AMDP to apply filters:Take the filter values and filter on the relevant node CDS views inside an AMDP. The result of this AMDP is a list of header keys. This can then be passed to the SADL GET_ENTITYSET as a filter condition and the records be fetched just for those keys.Advantages:
   
   
   
   • There is less data transfer to the application layer since all filter application happens in the AMDP.
   
   
   • Filter application is faster and scalable because its inside the AMDP.
   
   
   
              Disadvantage:
   
   
   
   • Reading the CDS view via an AMDP will not give the draft values. So the search will omit the data in the draft tables.Since we require results from the draft also, we cannot going ahead with this approach.
   
   
   
   


3. Use QUERY on BO nodes
   
   Model QUERY_BY_ELEMENTS on each BO node. Then based on the filter value, trigger the query on the relevant node and fetch the key of the header which satisfies the provided filter condition. THis can then be passed to the SADL GET_ENTITYSET as a filter condition and the records be fetched just for those keys. But BOPF does not let us model queries on the nodes for BOs created out of a CDS view. So we cannot go ahead with this approach.


4. Modify the CDS view to support filtering:The deal header CDS view can be modified to provide the fields on which filter option is provided. This can be done by introducing parameters in the CDS view for each filter value. If the filter value is provided, then the parameter is passed as 1. Otherwise, 0. If the value is passed as 1, then the CDS exposes the field value. Otherwise it returns NULL for that field. On this result, the filter option will be applied and just the header rows which satisfy the conditions will be returned.           Advantages:
   
   
   
   • Fastest in terms of execution since all processing happens within the CDS.
   
   
   • Paging and other things are handled by SADL like how it happens now.
   
   
   • We don’t have to modify the standard execution process.Disadvantage:
   
   
   • 1 search field needs to be provided for each field that needs to be searched.
   
   
   
   Since we require just 1 search field, we are not going ahead with this approach.


5. Fetch the record and search in it ourselves
   This is the brute force method. We fetch all the deal header nodes ignoring paging (but applying the search condition on header fields). Then based on the filter condition, we fetch the corresponding node by association; search in them; filter the relevant header node; apply paging and return that result to the UI.           Advantage:
   
   
   
   • Matches all our business requirements.Disadvantage:
   
   
   • We fetch all the headers ignoring the paging and fetch the corresponding node data for all of the header records and filter for the records we need (There is no option to apply filter on association). Because so much data is required to be fetched and sifted through, the search on these fields does not yield result in sub-seconds. Also, with increase in data volume, the speed might go down further and this might become a potential bottleneck.
   
   
   
   Because this is the only approach that solves our business case, we are going ahead with this.

Here is how to implement it

1. In the header CDS, introduce 3 new dummy fields so that they can be exposed as search fields on the UI. Also, $filter on these will be possible.


2. Attach required value help CDS to these fields.


3. In the GET_ENTITYSET method of the header node, go through IS_REQUEST_DETAILS-TECHNICAL_DETAILS-FILTER_EXPRESSIONS[]. In this, if search is requested on these fields, save the search condition separately in a range table. This table will contain the filter condition for EQ, NE, LT, GT, LE and GE. Each filter expression has an EXPRESSION_ID. Filter on different columns are linked by AND or OR operators using these EXPRESSION_ID fields. So when you remove a row of the custom field, remove the row of the corresponding logical operator also or adjust it accordingly.


4. For the fields where wild card is applied, they come in IS_REQUEST_DETAILS-TECHNICAL_DETAILS-FILTER_FUNCTIONS[]. Do the same as step above.


5. Save the paging request from IS_REQUEST_DETAILS-PAGING separately and clear it.


6. In case $count is requested, check that in IS_REQUEST_DETAILS-PARAMETERS. In this case, fill IS_REQUEST_DETAILS-TECHNICAL_FUNCTIONS-SELECT_STRINGS with all the columns that are required usually. This is because, when $count is requested, the GET_ENTITYSET returns the count directly and no data. So we cannot get the correct count after the filters.


7. Call GET_ENTITYSET.


8. Now if the filter is provided for Account Number, form the dynamic WHERE clause as follows:
   

   filter_condition_hdr = cl_shdb_seltab=>combine_seltabs(
   
             it_named_seltabs  = VALUE #( ( name = 'DEALNUMBER' dref = REF #( it_acc_num[] ) ) ) ).
   
   
   
   filter_condition_item = cl_shdb_seltab=>combine_seltabs(
   
             it_named_seltabs  = VALUE #( ( name = 'TRANCHENUM' dref = REF #( it_acc_num[] ) ) )
   
             ) && | OR | &&
   
             cl_shdb_seltab=>combine_seltabs(
   
             it_named_seltabs  = VALUE #( ( name = 'DRAWDOWNNUM' dref = REF #( it_acc_num[] ) ) )
   
             ) && | OR | &&
   
             cl_shdb_seltab=>combine_seltabs(
   
             it_named_seltabs  = VALUE #( ( name = 'CONTRACTNUM' dref = REF #( it_acc_num[] ) ) )
   
             ).​

   
   


9. The result of GET_ENTITYSET is a set of header row items. For this, fetch the corresponding item rows using RETRIEVE_BY_ASSOCIATION.


10. Pass the header rows, item rows and both filter options to an AMDP, apply the filter and get back the header rows for which the account number is present either in DEALNUMBER at header level or TRANCHENUM or DRAWDOWNNUM or CONTRACTNUM at the item levels. The AMDP code looks as follows:
    

    -- Apply filter for header
    
        t_header_filtered = APPLY_FILTER ( :it_entityset, :iv_filter_header );
    
    
    
        -- Apply filter for item
    
        t_item_filtered = APPLY_FILTER ( :it_item, :iv_filter_item );
    
    
    
        -- Group items to remove duplicates. We need only root_key
    
        t_item_filtered_grouped =
    
            SELECT
    
                root_key
    
            FROM
    
                :t_item_filtered
    
            GROUP BY
    
                root_key;
    
    
    
        -- Export the result
    
        ET_entityset =
    
            SELECT
    
                header.*
    
            FROM
    
                :t_header_filtered AS header
    
            UNION
    
            SELECT
    
                header.*
    
            FROM
    
                :it_entityset as header
    
            INNER JOIN
    
                :t_item_filtered_grouped as item
    
            ON
    
                header.dealkey = item.root_key;​

    
    We use the AMDP because
    
    
    
    1. Its easier to apply filters.
    
    
    2. If filters are to be applied on different columns, then managing dependencies and using multiple loops can be avoided.
    
    
    
    


11. Take these headers and pass them to the next set of condition applying methods for the other nodes.


12. I buffered the results at the end in a class attribute. This is because the data fetching and $count are fired for the table together. So I can perform the search once. If $count is requested, then I send the number of lines in ES_RESPONSE_CONTEXT-COUNT. Otherwise, I apply paging by fetching only the required rows and send those rows as output in ET_ENTITYSET.

There could be better ways to do this. But this is how we have done it. Thoughts and comments are welcome!