SAP Builders Blog Posts
Learn from peers about their low-code journey and write your own blog posts to share your thoughts and experiences as you become an SAP Builder.
cancel
Showing results for 
Search instead for 
Did you mean: 
Kanishka
Advisor
Advisor
744

In SAP Build Apps, to fetch data from any data source, we generally use a data variable to connect with the data entity and search for a keyword stored in a page variable. It is pretty simple. Here is a blog post from @Dan_Wroblewski "Your Guide to Calling S/4HANA Cloud APIs from SAP Build" as reference for fetching data from an ERP system.

However, in a regular business case, it becomes more complicated, especially with numerous data fields in an entity, such as those in S/4HANA Cloud, ECC, and other business systems.
Presentation1.png

Exploring Data Entities

When working with large data entities, writing an effective query is crucial. If you already know the key for the record you want, you can load it directly. However, if you aim to offer valuable help in the form of a suggestion list through a multi-field search, it becomes a bit more complicated, requiring a query. In this blog, we're focusing on using OData to refine search processes in SAP Build Apps. Before we dive into the specifics of SAP Build Apps, we will get familiar with how queries work which will set the stage for optimizing your search methods. Once you've got a handle on that, we'll smoothly transition to applying these techniques in SAP Build Apps. So, let's begin by examining queries for effective search enhancements.

For instance, you can explore the SAP S/4HANA Cloud's Product entity from the SAP Business Accelerator Hub, alternatively you can also use the graph navigator. Business Accelerator Hub offers standard sample data, which may be customized to suit specific enterprise needs. Here, you can simulate and try out different services offered by SAP and also get the code snippets to speed up the development. Some customers establish their own Enterprise Business Accelerator Hub to efficiently publish their customizations and proprietary APIs, tailoring the solution to their unique requirements.

Here, you can also run test calls with different parameters. For example, adding the parameter $top=1 will show the first entry in the Product data entity.

Screenshot 2024-05-27 at 10.19.42.png

In the response you can see the result for one product.

Screenshot 2024-05-27 at 10.21.19.png

In SAP Business Accelerator Hub, you may notice that there are multiple entities inside this product entity. 

Screenshot 2024-05-27 at 10.24.01.png

By adding the $expand parameter to include the associated product description entities, the response will display the top result with the product descriptions in each available language. We can, of course, filter this for the language(s) of interest to us.

Screenshot 2024-05-27 at 10.25.48.png
Similarly, here you can simulate different responses with the available functions.

Searching Multiple Entities and Filtering Results:

Let’s say you want to search for a product. You can directly search for the product using the query below.

 

/Products?$filter=contains(Product, 'PRA')&$select=Product

 

In the query, two key parameters are utilized:

  1. $filter: the filter condition checks if the Product field contains the substring 'PRA', which represents the keyword typed by our user
  2. $select: specifies that only the Product field should be included in the response.

In order to improve the quality of search, you can extend the search criteria for the products to different fields and entities. As you can see in Business Accelerator Hub, there are many fields in each entity. To provide better search results we can extend the search to include other fields such as GlobalTradeNumber, product name, and product description. You can also narrow the query to display only the results from a specific sales organization or use other appropriate criteria.

To do this, first determine under which entities these fields are located. The description of the product is under ProductDescription, the sales organization is under ProductSalesDelivery, and the global trade number is under ProductUnitOfMeasure.

The search query should use a keyword to search within the product description, product sales delivery, and global trade number, and be limited to a specific sales organization

Below is a sample query for searching the products with keyword "PRA" and filtering based on the sales organization "1010".

 

/Product?$expand=_ProductDescription($filter=Language eq 'EN'), _ProductSalesDelivery($filter=ProductSalesOrg eq '1010';$select=ProductSalesOrg),_ProductUnitOfMeasure($filter=contains(GlobalTradeItemNumber,'PRA');$select=GlobalTradeItemNumber)&$filter=_ProductSalesDelivery/any(o: o/ProductSalesOrg eq '1010') and (contains(Product, 'PRA') or _ProductDescription/any(o: contains(o/ProductDescription, 'PRA') and o/Language eq 'EN') or _ProductUnitOfMeasure/any(o: contains(o/GlobalTradeItemNumber, 'PRA')))&$select=Product&$top=10

 

Let's break down the query step by step:

1. Resource Path: 

/Product: Specifies the resource or entity type you are querying, which is "Product.

2. Expand Clauses:

$expand=_ProductDescription($filter=Language eq 'EN'),_ProductSalesDelivery($filter=ProductSalesOrg eq '1010';$select=ProductSalesOrg),_ProductUnitOfMeasure($filter=contains(GlobalTradeItemNumber,'PRA');$select=GlobalTradeItemNumber)
Indicates that you want to expand associated entities. It includes three expansion clauses:

  1. _ProductDescription($filter=Language eq 'EN')
    Expands the "_ProductDescription" entity with a filter to include only descriptions in English.
  2. _ProductSalesDelivery($filter=ProductSalesOrg eq '1010';$select=ProductSalesOrg)
    Expands the "_ProductSalesDelivery" entity with a filter to include only entries where the "ProductSalesOrg" is '1010' and includes the value of the field in response
  3. _ProductUnitOfMeasure($filter=contains(GlobalTradeItemNumber,'PRA');$select=GlobalTradeItemNumber)
    Expands the "_ProductUnitOfMeasure" entity with a filter to include only entries where the "GlobalTradeItemNumber" contains 'PRA' and selects the "GlobalTradeItemNumber" field.

3. Filter Clause:

$filter=_ProductSalesDelivery/any(o: o/ProductSalesOrg eq '1010') and (contains(Product, 'PRA') or _ProductDescription/any(o: contains(o/ProductDescription, 'PRA') and o/Language eq 'EN') or _ProductUnitOfMeasure/any(o: contains(o/GlobalTradeItemNumber, 'PRA'))).

Specifies conditions to filter the results This query includes multiple conditions:

  1. _ProductSalesDelivery/any(o: o/ProductSalesOrg eq '1010')
    Checks if there is any related sales delivery with "ProductSalesOrg" equal to '1010'.
  2. (contains(Product, 'PRA') or ...
    Checks if the product name contains 'PRA' or if there is any description in English that contains 'PRA' or if there is any unit of measure with a global trade item number containing 'PRA'.

4. Select Clause:

$select=Product
Indicates that you specifically want to retrieve the values of the "Product" field for the products that meet the specified criteria. This means that the response to the query will include only the names or identifiers of the products that match the search criteria and filters applied in the query.

5. Top Clause:

$top=10
Limits the result set to the top 10 records.

ds.png

 

In summary, this query requests a list of Product records from the S/4 system by searching for the keyword “PRA” in expanded associated entities such as descriptions, sales deliveries, and unit of measure details. It also limits the entries returned by specified sales organization information. The query applies various filters based on product sales organization, product name, description in English, and global trade item number. The results are limited to the top 10 records, with only the "Product" field selected for retrieval.

The request URL would be like

 

https://sandbox.api.sap.com/s4hanacloud/sap/opu/odata/sap/API_PRODUCT_SRV//Product?$expand=_ProductDescription($filter=Language eq 'EN'), _ProductSalesDelivery($filter = ProductSalesOrg eq '1010';$select =ProductSalesOrg), _ProductUnitOfMeasure( $filter =contains (GlobalTradeItemNumber,'PRA');$select=GlobalTradeItemNumber)&$filter=_ProductSalesDelivery/any(o: o/ProductSalesOrg eq '1010') and (contains(Product, 'PRA') or _ProductDescription/any(o: contains(o/ProductDescription, 'PRA') and o/Language eq 'EN') or _ProductUnitOfMeasure/any(o: contains(o/GlobalTradeItemNumber, 'PRA')))&$select=Product&$top=10 

 

Building the Query in SAP Build Apps

Now, to implement this query in SAP Build Apps, first, you would need to setup the BTP destination. For initial testing, you may use Basic Authentication with a technical user in the destination, but to ensure better security and auditability, you should use  Principal propagation with SAP Build Apps and S/4HANA Cloud to connect with the S/4 system.
OData integrations of SAP systems are straightforward using SAP Systems integration 

Screenshot 2024-05-27 at 14.13.07.png

When using this simplified method to access OData services in SAP Build Apps, we cannot use some advanced filters like contains, select, etc. To get around this limitation you can use this destination as a REST API destination instead. You can find more information on this workaround in the blog post by @Dan_Wroblewski  "OData Filtering in SAP Build Apps"

Screenshot 2024-05-27 at 14.15.16.png

Make sure to select the right destination and give a name to the data entity. The base URL is created through destinations and the query can be configured in the relative path of the data connection.

Screenshot 2024-05-27 at 16.07.04.png

Now use record collection logic to retrieve the data from the S/4HANA Cloud Product entity, and use the formula to construct the query.

Taking what we learned above, below is the example for creating a query to retrieve the global trade number, name of the product, sales organization, and description of the product for each entry found. For the keyword we can use page variable assigned to a search input field (here pageVars.Item.InputProduct). For the sales organization we can either hardcode the sales organization ID, or use an app variable to reference the sales organization ID determined based on the user logged in (here appVars.CurrentUserSettings.SalesOrganization).”

 

/Product?$expand=_ProductDescription($filter=Language eq 'EN'),_ProductSalesDelivery($filter=ProductSalesOrg eq”+ STRING(appVars.CurrentUserSettings.SalesOrganization)+“; $select = ProductSalesOrg),_ProductUnitOfMeasure($filter=contains(GlobalTradeItemNumber ,'"+STRING(pageVars.Item.InputProduct)+"'); $select=GlobalTradeItemNumber)&$filter=_ProductSalesDelivery/any(o:o/ProductSalesOrg eq”+STRING(appVars.CurrentUserSettings.SalesOrganization)+”)and(contains(Product,'"+ STRING(pageVars.Item.InputProduct)+"')or_ProductDescription/any(o: contains(o/ProductDescription,'"+STRING(pageVars.Item.InputProduct)+"') and o/Language eq 'EN') or _ProductUnitOfMeasure / any(o:contains(o/GlobalTradeItemNumber,'"+STRING(pageVars.Item.InputProduct) +"')))&$select=Product&$top=10

 

Formatting the Response

When the query is run, you receive a JSON response with the products based on the search criteria. Ideally, we want this response to be in a list format. Now, let’s explore how we can map the response into a list.

You can use a data variable to store the response from S/4HANA Cloud and bind it to a list component to view the response in the app. Generally, a data variable requires a schema to save the data. We usually create a data variable from the respective data source, where the schema is automatically generated based on the response like in the example below.

Screenshot 2024-05-27 at 16.08.45.png

 In the response schema, you may notice that there are more fields than needed. When binding the data variable to the list component, you can filter the fields you want to use. This is one way you can easily format the response from the query.
Screenshot 2024-04-30 at 14.15.30.png 
To optimize the data pipeline from S/4HANA Cloud to SAP Build Apps, alternatively, you can create an on-device storage entity to define a schema for your data variable, including only the fields you need for your list component. On-device storage is generally used to store data locally on the user's device or the web browser, in this case we are using it to cache the response from the S/4HANA Cloud. For example, you can name the entity "MaterialList" and add the necessary fields from the response. Now this entity will work as a data source, you can create a data variable with the desired schema for use in your list component. 

Screenshot 2024-05-27 at 16.10.36.png

Now in the logic editor, add a set data variable logic to the Record collection of the product information and map the response to the MaterialList1 data variable.

Screenshot 2024-05-27 at 16.11.24.png

In the binding menu you can map the raw response of Get record collection to MaterialList1 .

Screenshot 2024-05-27 at 16.12.08.png

Now, when you search for a product, the list component will show you the top 10 results of the products with GlobalTradeNumber, Product description in the sales organization of the user searching. 

Screenshot 2024-05-27 at 16.03.35.png

Key Takeaway

In summary, mastering efficient querying in SAP Build Apps is crucial for seamless data retrieval. This blog explores techniques for expanding searches, filtering results, and implementing precise queries. These practices, when integrated into SAP Build Apps, promise a streamlined user experience, presenting top results seamlessly. Let these insights be your guide in creating applications that Excel in data efficiency within the SAP ecosystem.

To learn more trips and tricks in using SAP Build Apps, check out and participate in the community challenge 
(Citizen) Developer Challenge – SAP Build Apps

 

1 Comment