21
Views
13
Comments
Solved
Filter SQL by list values
Question

Hi All,

I have a question relating the filtering of a SQL statement based on a filtered list. I already found this topic https://www.outsystems.com/forums/discussion/16470/use-a-list-of-values-in-a-filter-inside-an-aggregate/  but it is mostly about mobile.

The problem is as followed: 

I have created a dashboard with data from 2 different sources. 1 part comes from an API inside a list and the other part is local stored. The API list contains a name and an ID. The ID is present as a foreign key in a local table so by that information I can join both to create a dashboard with the name and all other information which is in the local table. 

I have created a SQL statement that contains all the files that have specific object to them and I match these with the list coming from the API to assign a name to it. 

Now I need to filter the tablerecords that I created based upon a SQL query by name. To do this I created a variable called Filter which holds the entered value and parses it on to the preparation of it own screen. In the preparation I created a listfilter with an index to search for the Filter value. This works fine. 

Now I need to make sure that the SQL list gets filtered based upon the records that are inside the ListFilter.FilteredList. The output that I get right now is the same table but only the names that match the Filter show up (half way there). 

Anybody have ideas on how to do this? 


Rank: #132
Solution

Hi Max,


You can build a comma separated text with the ids you want to filter.

I will give you an example and you can adapt to your scenario. In this example, I want to get users filtering by a list of users


1. You create a local variable that will hold the SQL filter separated by commas (in my case UserFilterSQL)
2. I start building my comma separated filter and in the end, remove the comma (by now, that variable should be filled by something like 1,2,3,4)
3. Now for the filter of the query (it has to be an Advanced SQL), you add the parameter and set the Expand Inline to True and you can filter the query:


I hope this example will help you.

Be careful that if the variable is empty an error will occur since the SQL syntax will have an error and Service Studio would not help you since it is Expand Inline.


Cheers,

João

Rank: #384

Hi Max de Groot,

You can also use the BuildSafe_InClauseTextList from the Sanitization API, which receives a text list(In your case should be the ListFilter.FilteredList) and then inject the result of it in an Expand Inline Input parameter of your SQL like João Marques mentioned. 

Thanks!

Rank: #10490

Hi Joao,

It is an error. This is how my input is assigned: 

This error shows up when testing the query with the Expand Inline set to 'yes'. If I set it to 'no' without changing anything else in my query, the query itself works on test but breaks on execution. I think that has something to with passing multiple Id since that works if I only pass 1 id.

Rank: #132

The values are ok, are you writing the filter like this: "IN (@EngagementIdFilter)"?


Can you share your query, please?

It would be easier to find out.

Rank: #132

The filter has to have the quotes ' only if the field being filtered is of type text. Otherwise no quotes, and filters should be applied on ids (better for performance and default indexing).

Rank: #203

Max,

In order for this approach to work, you have to set the expand inline property of this variable to YES.

Also if the type of the attribute you are trying to filter is a text, you have to use simple quotes for it, like:


'13','4','15','7'


But this is only for text values.


As Samuel pointed, Sanitization API have both, an action for build var given a List of integers (BuildSafe_InClauseIntegerList), and an action for build  a var given a List of Type Text (BuildSafe_InClauseTextList).


This is important to secure you application, so use these functions if possible.


Rank: #203

Yes João!
Thanks for clarifying, as I said it is just for text attributes.

And when I say text attributes, is the type of the attribute in your entity and not the variable in your SQL Element!

(Maybe this was confused in the post)


For Example, if you have an Employee Entity,

And have a Primary Key named Code, of type Text Identifier you should use quotes. However if it is a normal Identity Identifier of Type Long Integer, you shouldn't use quotes.


The same is applied for other attributes that are not Identifiers.

If the attribute in the DB you are trying to filter in your SQL Element is a Text, use quotes, otherwise don't use them!


And give preference to build the vars with the sanitization api actions, since it helps in security concerns. 


Cheers and Regards,


RR :)

Rank: #10490

Hi Joao, Raphael,

Thanks for explaining! I missed the quotation marks around the input parameter. Added them and it works like a charm now! 

Wish I could set both of your answers as solution haha

Regards,
Max

Rank: #203

No problem Max
Happy to help :)


Just to make sure your attribute were of types text?

Otherwise it should work without quotes too... as João said it is better for performance...


I commented to discard this as a problem... if your attributes were of type text!

Have this in mind.


Cheers and Regards,

RR :)