Make data searchable in OutSystems

Extending the Master-Detail sample with a flexible search functionality

In my last two-part article, I described how to handle master-detail data in OutSystems. As an example, we implemented a small application that allows you to create and edit purchase orders and their order items. However, the example lacks a list of purchase orders and a way to navigate to the respective detail screens. In this article, I would therefore like to add this missing function to the example and show a sample for implementing a flexible search functionality that can be used throughout the application.

What you need

If you want to follow up the implementation on your own, you need:

• An OutSystems account (a free personal environment is sufficient)
• OutSystems Service Studio
• The sample application implemented in part one and two of my last article:
  • Master-Detail data in OutSystems (1) CS
  • Master-Detail data in OutSystems (2) UI

This tutorial is also suitable for OutSystems newcomers. However, a little prior experience in using the Service Studio is useful.

Getting a list of purchase orders

Architectural and performance considerations

The function of getting a list of orders is so fundamental that it should be part of the core functionality, right? So let’s start with the core service again. 😀
But to be serious: According to our software architecture, it is the core service’s job to store data and allow other modules to access it. So we should start the implementation here.

So far, Order_CS only provides the server action Orders_GetOrder for receiving orders. This receives the OrderId as input and returns the order with all its order items in the Order_Detail structure. The simplest solution to obtain a list of all orders would be to simply create a copy of this server action and remove the OrderId parameter and the filtering. However, this has some disadvantages:

• Instead of selecting all orders at once, you usually only need a subset that matches certain search criteria.
• To meet the requirements of end users, the result lists need to be sorted by one or more attributes.
• Often only a paginated list is displayed, as transferring large quantities of results to consumers at once costs a lot of data traffic.
• As can be seen in the following illustration, Orders_GetOrder has been optimized to select a single order with all its order items, which would be very performance-intensive and irrelevant for overview lists.

So from a performance perspective we want to have a server action that is optimized to

• retrieve a selection of purchase orders that match given search criteria,
• already sort the results by given sort attributes,
• paginate the returned data rows,
• and return only a projection of the attributes needed for overview lists.

Additional data structures

Let’s first create the additional data structures that are required in Order_CS for the new search function.

As you can see, I organized the structures in folders: Order holds all structures used for purchase orders, Data is for general helper structures.

• PaginationInfo
  This public structure is used to control pagination. It defines the zero-based index of the first result and the maximum number of records to be returned. Both attributes are optional, so by default the pagination will start at index 0 and contain the max. possible Integer count of records (2147483647).
• TotalRows
  This private structure is only used as output structure for advanced SQL queries to receive the total number of rows that match the given search criteria.
• Order_Result
  Similar to the public structure Order_Detail, which was implemented in my last articles, this one is to represent a single result of a purchase order search. It contains nearly the same attributes, but stripped down to the really relevant ones for result lists.
• Order_SearchCriteria
  This public structure contains all attributes, the purchase order search is allowing to filter for.

/**
 * @structure PaginationInfo
 * Pagination control
 * Public: Yes
 *
 * @attribute {Integer} [StartIndex]
 * Index of first returned result
 * Default: 0
 *
 * @attribute {Integer} [MaxResults]
 * Max. number of results returned
 * Default: 2147483647
 */

/**
 * @structure TotalRows
 * Total number of rows
 * Public: No
 *
 * @attribute {Integer} Count
 * Total number of matching rows
 */

/**
 * @structure Order_Result
 * Result of a purchase order
 * Public: Yes
 *
 * @attribute {Order Identifier} OrderId
 * Id of the purchase order
 *
 * @attribute {Date} OrderDate
 * Date of the purchase order
 *
 * @attribute {Integer} ItemsCount
 * Number of order items
 *
 * @attribute {Currency} TotalAmount
 * Total amount of all order items
 *
 * @attribute {Date Time} CreatedOn
 * Timestamp of creation
 *
 * @attribute {User Identifier} CreatedBy
 * User who created the record
 *
 * @attribute {Text} [CreatedByName]
 * Name of user who created the record
 *
 * @attribute {Date Time} UpdatedOn
 * Timestamp of last update
 *
 * @attribute {User Identifier} UpdatedBy
 * User who last updated the record
 *
 * @attribute {Text} [UpdatedByName]
 * Name of user who last updated the record
 */

/**
 * @structure Order_SearchCriteria
 * Criteria to search for orders
 * Public: Yes
 *
 * @attribute {Date} [OrderDate_Start]
 * Earliest order date to match
 *
 * @attribute {Date} [OrderDate_End]
 * Latest order date to match
 */

One server action to search them all

For the search feature we will use an advanced SQL query with a dynamic ORDER BY clause. To prevent a SQL injection we need to preprocess the dynamic part. One possible way is described in this OutSystems documentation article. For a project I was working on earlier, I reworked this method to be suitable for multiple fields and include a default sort order as a fallback option. In the meantime, I improved it again and published the most recent version in the Forge:

EncodingSortForSQL

After installing the component into our environment, we can add a dependency to its helper function EncodingSortForSQL.

Now everything is prepared to start with low-coding the public server action Order_Search.

As you can see, the new server action Order_Search has many parameters, but the flow is quite simple. Of course, we start by creating all the input and output parameters:

/**
 * @serveraction Order_Search
 * Returns a list of matching orders
 * Public: Yes
 *
 * @input {Order_SearchCriteria} [OrderSearchCriteria]
 * Criteria to search for orders
 *
 * @input {PaginationInfo} [PaginationInfo]
 * Pagination control
 *
 * @input {Text} [SortField]
 * String with comma separated SQL sort attributes (e.g. "CreatedOn DESC", "OrderDate,TotalAmount DESC")
 * Default: "OrderDate,TotalAmount DESC"
 *
 * @output {Order_Result List} OrderResults
 * List of orders
 *
 * @output {Integer} TotalCount
 * Total number of matching orders
 */

Next we implement the advanced SQL query. The parameters can simply be copied from Order_SearchCriteria and PaginationInfo. SortField is a bit special as we need to set Expand Inline to Yes. By doing so, OutSystems will no longer encode this parameters value and allows us to directly pass SQL code into the query statement. This is why we will encode it later with the helper function added as a dependency before. The last parameter NullDate is used to pass OutSystems NullDate() value, so the database can compare stored values against it.

In an advanced SQL query, you always have to define the expected output structure so the results can be assigned correctly. This can be one or more entities or structures whose attributes match the result columns of the SQL query in sequence and data types. So in our app we can use Order_Result for the purchase order data followed by TotalRows to receive the total number of matching records.

WITH BASE_CTE AS
(
  SELECT
      {Order}.[OrderId]                AS OrderId,
      {Order}.[OrderDate]              AS OrderDate,
      COUNT({OrderItem}.[OrderItemId]) AS ItemsCount,
      SUM({OrderItem}.[Quantity] *
          {OrderItem}.[Amount])        AS TotalAmount,
      {Order}.[CreatedOn]              AS CreatedOn,
      {Order}.[CreatedBy]              AS CreatedBy,
      CreatedByUser.[Name]             AS CreatedByName,
      {Order}.[UpdatedOn]              AS UpdatedOn,
      {Order}.[UpdatedBy]              AS UpdatedBy,
      UpdatedByUser.[Name]             AS UpdatedByName
  FROM {Order}
  LEFT JOIN {OrderItem} ON {OrderItem}.[OrderId] = {Order}.[OrderId]
  LEFT JOIN {User} CreatedByUser ON CreatedByUser.[Id] = {Order}.[CreatedBy]
  LEFT JOIN {User} UpdatedByUser ON UpdatedByUser.[Id] = {Order}.[UpdatedBy]
  WHERE
      (@OrderDate_Start = @NullDate OR {Order}.[OrderDate] >= @OrderDate_Start) AND
      (@OrderDate_End = @NullDate OR {Order}.[OrderDate] <= @OrderDate_End)
  GROUP BY
      {Order}.[OrderId], {Order}.[OrderDate],
      {Order}.[CreatedOn], {Order}.[CreatedBy], CreatedByUser.[Name],
      {Order}.[UpdatedOn], {Order}.[UpdatedBy], UpdatedByUser.[Name]
),
COUNT_CTE AS
(
  SELECT COUNT(*) AS Count FROM BASE_CTE
)

SELECT * FROM BASE_CTE, COUNT_CTE
ORDER BY @SortField
OFFSET @StartIndex ROWS FETCH NEXT @MaxResults ROWS ONLY;

As you can see, we are using common table expressions here. This way we can preselect all matching purchase orders with BASE_CTE on the database server so we can count the total number of matching records with COUNT_CTE.
I recommend using column aliases that match the attribute names in our return structure Order_Result. By doing so, a consumer of the service knows what column names to use for sorting in parameter SortField.
The joins should be self-explanatory. When joining with the same entity multiple times, like {User} in this example, I also use speaking aliases for better readability.
In the where clause we now need to filter for all of SearchCriteria. For this we use the so called conditional where clause pattern: Every search criterion is separated in parentheses, starting with the selective condition @OrderDate_Start = @NullDate, followed by an OR and the filter condition itself {Order}.[OrderDate] >= @OrderDate_Start. So the selective condition controls if the filter condition is to be applied. The individual search criteria are linked with an AND, so they are subtractive.
The grouping includes all selected purchase order attributes and is necessary for the aggregation functions used to get the order items count and total amount.
The last three lines contain the final select statement that fetches the data to be filled into the output structures Order_Result and TotalRows. Where the first one is identical to the selected columns of BASE_CTE and the second one only contains one attribute for the total rows count selected in COUNT_CTE. The order by statement is where the dynamic sort comes into place and you can see, why it’s so important to sanitize the value of SortField to prevent SQL injections. The last line is handling the pagination of the result set.

Of course, we need to pass in all the defined SQL parameters in the flow. In SortField we can directly use the sanitization of the forge component EncodingSortForSQL hence both parts are provided as functions: BuildSafe_SortClause(TextToSortAttributes(SortField, "OrderDate,TotalAmount DESC")). The two parts are separated in the component to allow for manipulation of the sort attributes between SortField parsing and sanitization.

Finally we assign the fetched rows and the total count to the output parameters of the server action Order_Search.

Presenting the results to the user

In the last articles we already created a UI to view and edit a single purchase order. What was missing is the list of purchase orders to navigate to the detail screen Order implemented so far.

So let’s begin by adding a dependency to the new Order_Search server action in Order_CS. The used structures will be selected automatically.

A screen to show them all

Next we create a new screen named Orders with the following local variables to control which purchase orders to display:

/**
 * @screen Orders
 *
 * @variable {Order_SearchCriteria} OrderSearchCriteria
 * Criteria to search for orders
 *
 * @variable {Text} TableSort
 * String with comma separated SQL sort attributes (e.g. "CreatedOn DESC", "OrderDate,TotalAmount DESC")
 *
 * @variable {Integer} StartIndex
 * Index of the first result on the page
 */

If you have already implemented a paginated list view as shown in the If you have already implemented a paginated list view as shown in the OutSystems online courses, you may be missing a fourth variable that contains the number of records per page. Here I would like to demonstrate a different approach that allows the end user to select this value from a dropdown menu. To maintain the setting between screens, we store it in a client variable instead:

/**
 * @clientVariable {Integer} ResultsPerPage
 * Max. number of results per page
 * Default: 20
 */

To fetch the list of purchase orders on the screen we also need to add a data action with two output parameters:

/**
 * @dataAction GetOrders
 * Fetches the list of purchase orders of the current page
 * Fetch: At start
 *
 * @output {Order_Result List} OrderResults
 * List of purchase orders
 *
 * @output {Integer} TotalCount
 * Total number of results
 */

The action flow is pretty simple: First Order_Search from the core service is called with the corresponding input parameters and then the results are assigned to the output parameters.

The screen contains all necessary controls:

• the title,
• a button to create a new order (by navigating to the existing Order screen with an empty OrderId parameter),
• input fields for the search criteria (OrderSearchCriteria.OrderDate_Start and OrderSearchCriteria.OrderDate_End),
• the table for the results (GetOrders.OrderResults) with a edit icon linked to the existing Order screen with the parameter OrderId set to GetOrders.OrderResults.Current.OrderId,
• and a pagination widget called PageNavigation.

Navigate page by page

As you can see, the pagination widget is a custom web block. Of course, we could simply use the OutSystems UI control Navigation\Pagination, but this is lacking of the option to choose the number of results per page. So I created a wrapper here:

/**
 * @webblock PageNavigation
 * Wrapper of Navigation\Pagination extended by a dropdown to select the
 * number of results per page
 *
 * @input {Integer} StartIndex
 * Set the initial index to start pagination.
 *
 * @input {Integer} TotalCount
 * Total records of list.
 *
 * @variable {Integer List} ResultsPerPageOptions
 * Options in the dropdown list
 */

Everything is wrapped in an outer container with Style Class display-flex containing a container with Style Class flex1 for the Navigation\Pagination control of the OutSystems UI and a Popover Menu to mimic the dropdown. To align this one with the standard pagination control we add the Style Classes popover margin-top-m margin-right-s. The popover control has two placeholders: Top Content is always visible and can be clicked to open the menu, and Bottom Content holds the menu items. As top content we add a container with Style Class pagination-button padding-x-s full-width containing an expression with Value set to Client.ResultsPerPage and a caret-down icon. The options in bottom content are dynamically generated with a List bound to ResultsPerPageOptions containing a Link with Style Class display-block text-neutral-9, the On Click event handler ResultsPerPageOnClick and an expression bound to ResultsPerPageOptions.Current.

The logic of ResultsPerPageOnClick updates the client variable with the clicked option: Client.ResultsPerPage = ResultrsPerPageOptions.Current. After this the OnNavigate event ist raised with NewStartIndex set to 0. To close the popout menu finally a short javascript is called, that simply triggers a click on the body:

document.body.click();

So where are the options in the local variable ResultsPerPageOptions coming from? Those need to be initialized in the OnInitialize handler of the block. Here the OutSystems built-in action ListAppendAll is very handy to append several options at once.

Wire everything up

Back to our new screen Orders, we need to wire everything up with some client actions:

• OnInitialize
  handles the screens initialization event and sets the default filter configuration: OrderSearchCriteria.OrderDate_Start = AddMonths(CurrDate(),-1) and OrderSearchCriteria.OrderDate_End = NullDate().
• Refresh
  updates the list of fetched orders. I normally implement this wrapper as sometimes more than one data action need to be refreshed.
• OnFilter
  is a handler to react to changed filter criteria and in our sample handles the OnChange events of the two filter inputs. It sets StartIndex to 0 before calling Refresh so the pagination is reset to the first new results.
• PaginationOnNavigate
  works the same as the standard handler built by OutSystems scaffolding. It sets StartIndex = NewStartIndex and refreshes the data.
• OnSort
  also follows the standard scaffolded implementation to set or invert the sort order in TableSort, reset the pagination and refresh the data.

That’s it! We can now publish the application.

You can download the complete application from the OutSystems Forge:

Master-Detail data in OutSystems (Demo)

I would be pleased if you follow me on my social media accounts. In case you have any questions left open or have tips for me to improve, please contact me via Medium or my OutSystems profile.