Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member202465
Contributor

From time to time I encounter the question of performing a Join operation with Visual Composer. I will try to show one way to handle this.

Example Join scenario

In the following scenario  get customers and orders data using two different JDBC Stored Procedures:

The application looks like this - note the input values we pass to get part of the orders table:

We now want to present the data in one table in the UI, namely: for each order show also the details of the relevant customer. Where we had the customers:

and the orders:

we now need to have one table, the connection made by the customer_id:

Visual Composer does not support a Join operation in modeling. This is no surprise: In other programming languages we refrain from doing this in the application level as well.

Using the database to perform the Join

In this case we need to build the scenario differently, handling the Join on the database.

1. Create a new Stored Procedure for the Join operation:

As the original getOrders service required input for start and end dates, we need to have the same in the new Stored Procedure, filtering on the ordered_on field.

2. In Visual Composer consume the new operation:

3. The application looks this way now:

4. We could group the results using the Group by option to get a better view (optional):

5. Here is the application, we now have grouping on the customer name, plus the collapse / expand arrows.

Join and Updates

In some scenarios we need to update data before or after the Join, for example:

1. Present the customers and orders tables, and allow updates for each.

2. Show the joined table.

Option 1:

1. Updating data in the tables triggers update the database through a Data Service.

2. Presenting the Joined data calling the Data Service that performs the  Join, which gets the updated data.

Option 2:

If relevant for the specific scenario, show only the Joined data, and allow updates through this UI - calling relevant Data Services for update.