Literature Review: OutSystems vs IN Operator

Challenges and Solutions of Implementing the IN Operator in OutSystems

8 min readNov 6, 2023

The OutSystems platform seems to have a bittersweet relationship with the IN operator. In this article I’ll discuss this relationship, i.e. the challenges of implementing the IN operator in OutSystems, and explore the available solutions to work around this gap, taking into account what can be found in the literature.

The IN Operator in OutSystems

The IN operator is an essential tool in database operations that allows filtering results based on a list of values. However, the absence of IN Operator in OutSystems Aggregate has been a consistent topic of discussion within the developer community. This absence can complicate common tasks, such as filtering records based on specific values.

Much has already been written by OutSystems itself or in the forums in order to provide solutions or workarounds with regard to the IN operator, in Aggregates and Advanced SQL.

For several years now, many OutSystems community members have been asking for the IN operator to be inserted into the OutSystems platform, but without success. For instance, in 2015 an idea was posted in the community, the IN clause in Aggregates, by Alexandre Scheurkogel. And over the years, many have posted the same idea, which was eventually merged with the original idea. It’s true that it’s something that’s already on the OutSystems radar, but it’s such a popular idea that more than 500 people have already supported/requested that it be implemented. It’s actually the most trending and liked idea in the Aggregates & Queries category.

IN Operator Idea from OutSystems community.

I like some of the comments on the idea page, and here are the comments of some OutSystems MVPs:

OutSystems MVPs comments regarding the IN Operator idea.

This idea is so necessary that Cláudia Rosalino addresses it in her Master’s Thesis. “This dissertation aims to increase the level of expressiveness of Aggregates by proposing different solutions for implementing IN and NOT IN clauses without compromising the overall experience for any type of user.” Imagine, a thesis in which the aim is to find solutions to this problem 🤯.

But until this much-desired idea is implemented, there are a few ways to overcome the situation.

Workaround

Here we’ll explore the 3 ways in which the community has worked around this problem, the first two solutions only use Aggregates and the third one uses the OutSystems SQL tool:

Filter after fetching data (Stefan Weber’s solution)
Using the Index function in Aggregate
Use the SQL tool (see forge component)

From what can be found in the literature and on forums, the workarounds are as follows:

Workaround #1: Filter after fetching data (Stefan Weber’s solution)

Without using SQL IN, which is definitely the cost-effective way to go, one “solution” would be, that you:

Cycle through your selected categories
Use the ListFilter Action on the Aggregate result
Append the Filtered List to a Result List
Display the Result List

In other words, it means applying the filtering process after using the Aggregate for the retrieval of all the records.

Filter after fetching data. (Source of the image)

The problem with this solution is that it requires a bunch of logic components, and it also requires the retrieval of all the data, and only then the filtering is applied, which results in an efficiency problem.

I must emphasize that, as Stefan Weber points out, this is a “solution” that, although it works, should be used, only in an extreme case.

Workaround #2: Using the Index function in Aggregate

An alternative, in order to keep using the Aggregate instead of a SQL node, is using the Index function, to filter for Ids within a specific string keyword.

Based on this screenshot from Raymark Cosme, the first Aggregate returns all the Ids, to be used in ListAppendAll to map the field that is needed. On the String_Join you map again the field that is needed and use “#” as a separator.

String_Join maps the field needed and uses “#” as a separator. (Source of the image)

On the second Aggregate, use the following filter:

Index("#"+ String_Join2.Text + "#", "#" + Technician.TerritoryId + "#") <> -1

The Index function can be used inside the filters of an Aggregate and, together with a Text variable that the user can build as a concatenation of Text values that he wants to use to filter. It can achieve a similar behavior to the one from the IN SQL operator.

Index function used inside the filters of an Aggregate, underlined in yellow. (Source of the image)

This performs the IN operator. The return or test values should look like “#21#22#23#” and so on.

However, this solution can cause a table scan and poor performance on large tables.

Example of a comment against the usage of the Index function in Aggregate. (Source of the comment)

There are many voices completely against this approach, and those who say they are against Workaround #2 are certainly making harsher comments about Workaround #1.

And as for the words of Cristiana Umbelino “even though this can help you, it’s not a good approach in terms of best practices — hard to understand, too much code to get a dataset.”

But there you go, these are workarounds, and they should be taken into account, understanding exactly what the advantages and disadvantages are.

In fact, whenever possible, and especially if the table is large, and you need this functionality, convert to an Advanced SQL, use the IN operator, and index the column.

As Remco Dekkinga states in “OutSystems 101: Aggregates vs SQL Queries,” Aggregates are used when creating a SELECT query to retrieve a list of data, without the need for SQL operators like ‘IN,’ ‘EXISTS,’ ‘ANY,’ or ‘ALL’. On the other hand, Advanced SQL is preferred when dealing with bulk operations or when one of the SQL operators mentioned previously is needed.

Workaround #3: Use the SQL tool

Now, the solution recommended by OutSystems’ own documentation is to use the SQL tool. You can find more information in this document.

The Dynamically building a “WHERE … IN (…)” clause document specifies that “to properly build the values for the “IN” clause, you should always use one of BuildSafe_InClauseIntegerList and BuildSafe_InClauseTextList functions available in the Sanitization extension.”

Sanitization API that provides methods used to avoid code injection.

In other words, do sanitization to prevent code injection in SQL snippets containing untrusted content, such as data collected from end users.

In such cases, don’t forget to enable the Expand Inline property for the query parameter. This is required so that the input variable is evaluated as SQL and not just as a string.

Example of *BuildSafe_InClauseIntegerList* *API from Sanitization extension.*

By doing all this, you’ll avoid seeing the SQL Injection Warning in Service Studio. This warning means that your application can be vulnerable to SQL injection if you don’t take the necessary precautions.

It’s crucial to emphasize that using a ‘for each’ loop to construct the string intended for the IN operator is NOT advisable. While it may seem similar on the surface, these two approaches are fundamentally different.

The incorrect (on left) and correct (or right) approaches to constructing the “WHERE … IN (…)” clause.

Potential Solutions to Address the Problem

In the community, there are some suggestions for possible solutions to this problem.

There is a solution proposed by José Gonçalves. He suggests that “if there was an area where you need only to choose an attribute and associate a variable (text type) would make things quicker”

Suggestion of a possible solution. (Image by José Gonçalves)

But also Cláudia Rosalino in her thesis, comes up with a set of alternative approaches, as you can see in the image below.

Alternative approaches to overcome the problem. (Source of the image)

I have to say that by searching the community forum, I’ve often found them being suggested by other members of the OutSystems community, especially the 1st solution proposed by her, as shown in the figure below.

Add the IN operator in the Aggregate filters expression editor

In other words, this is a problem that the community is seeking to resolve, both in terms of workaround and possible developments.

Why the Problem Persists: The Official Explanation

According to Fernando Moitinho, Principal Product Manager at OutSystems, the reasons are:

Priority

Implementing the IN operator is a matter of priority, and OutSystems’ engineering teams have been preoccupied with other initiatives (like ODC) that demand resources.

Complexity

Adding the IN to an Aggregate requires some serious experience design as it is not as simple as adding a simple filter with a list of hardcoded values.

Final Thoughts

The challenge of implementing the IN operator within OutSystems has been a recurring topic of discussion and interest in the OutSystems community.

The absence of this feature has led to various workarounds, each with its advantages and drawbacks. Workarounds such as filtering after data retrieval and using the Index function have been proposed, but they come with their own set of limitations, such as reduced efficiency and complex code.

While the community has put forward alternative approaches and suggestions, the problem remains unresolved officially. OutSystems users have voiced their need for this feature, as evidenced by the popularity of related ideas and discussions. The delay in implementing the IN operator in Aggregate is attributed to the complexity and priority of other initiatives within OutSystems.

Share your thoughts on these challenges and potential solutions. What do you think about the current workarounds, and how do you envision the development of this feature in OutSystems?