Use a List of Values in a Filter inside an Aggregate

Use a List of Values in a Filter inside an Aggregate

  
Hello I do have an aggregate and as one of the filters i would like to do something like string variable in (list of values (Record List.string field). Is that possible?

Thanks,

Thiago
Hi Thiago,

Do you want add a filter which will take string value from a Record List and then search for the same?

Regards,
Suraj Borade
Hi Thiago,

Unfortunately, there's no equivalent to SQL "IN" in an aggregate. It's a real miss, imho. If the list is limited, you'll get by with OR's, if its longer, you need to handcraft an Advanced Query.

Hi

Instead of using an aggregate, you can use an SQL block and pass the comma separated values to it as an input parameter and use this list for filtering the data. Just remember to mark the Expand Inline property of the query parameter to Yes. Refer the image below



For example, you want to filter the data for the selected products, you can create a comma separated string with all the selected values as "'Product A', 'Product B', 'Product C'". Please note that each value is enclosed in single quotes as the parameter type is text (you'll not need it for integers or decimals). Once you have this string, you can assign it to a variable and that variable could be passed as a query parameter to the SQL block. Once you have passed the value to the query parameter, set its Expand Inline property to yes and use this query parameter for filtering the data as below

SELECT * FROM {TableName} WHERE {TableName}.[ProductName] IN (@QueryParameter)


This should do the trick. Let me know if this solves your problem.

Biswadeep Ghosh wrote:

Hi

Instead of using an aggregate, you can use an SQL block and pass the comma separated values to it as an input parameter and use this list for filtering the data. Just remember to mark the Expand Inline property of the query parameter to Yes. Refer the image below



For example, you want to filter the data for the selected products, you can create a comma separated string with all the selected values as "'Product A', 'Product B', 'Product C'". Please note that each value is enclosed in single quotes as the parameter type is text (you'll not need it for integers or decimals). Once you have this string, you can assign it to a variable and that variable could be passed as a query parameter to the SQL block. Once you have passed the value to the query parameter, set its Expand Inline property to yes and use this query parameter for filtering the data as below

SELECT * FROM {TableName} WHERE {TableName}.[ProductName] IN (@QueryParameter)


This should do the trick. Let me know if this solves your problem.


But this won't work for mobile Apps. Outsystems must implement several  "misses". SQL on mobile app. Local Storage Indexes, Filter by List, etc.

Kilian Hekhuis wrote:

Hi Thiago,

Unfortunately, there's no equivalent to SQL "IN" in an aggregate. It's a real miss, imho. If the list is limited, you'll get by with OR's, if its longer, you need to handcraft an Advanced Query.

Outsystems must implement several  "misses". SQL on mobile app. Local Storage Indexes, Filter by List (web and mobile),  etc.


It is possible, you can use the INDEX( function in the filter of an aggregate. Build a coma separated string of the IDs and then use INDEX to see if your ID is in the string. Works much the same way as the SQL IN statement, basically it is just looking for the ID using the string index function and returns -1 if nothing is found otherwise it returns the position of the substring. 

So something like Index(ReportFilter.ReportTypeId, "," + @ReportTypes + ",") >= 0 would return true if ReportTypeID is contained inside ReportTypes.


HOWEVER, it is very inefficient so only use it if you are expecting a small number of IDs.



John Williams wrote:

It is possible, you can use the INDEX( function in the filter of an aggregate. Build a coma separated string of the IDs and then use INDEX to see if your ID is in the string. Works much the same way as the SQL IN statement, basically it is just looking for the ID using the string index function and returns -1 if nothing is found otherwise it returns the position of the substring. 

So something like Index(ReportFilter.ReportTypeId, "," + @ReportTypes + ",") >= 0 would return true if ReportTypeID is contained inside ReportTypes.


HOWEVER, it is very inefficient so only use it if you are expecting a small number of IDs.




Thank you for your help. I wiil do this. But its sad that outsystem lead us to this.

IAMAR ZUZA DE ARAUJO wrote:

Thank you for your help. I wiil do this. But its sad that outsystem lead us to this.

Iamar,

OutSystems is a great tool that will save you loads of time, and has an enormous amount of features. Yes, an IN in an Aggregate for Mobile would be useful, as would numerous other features people have requested in the Ideas section. But to say that it is "sad" that OutSystems "leads you" to work around a missing feature sounds very... ungrateful. As if OutSystems owes you something. It's not like not having an IN makes the product worthless, or something.


Kilian Hekhuis wrote:

IAMAR ZUZA DE ARAUJO wrote:

Thank you for your help. I wiil do this. But its sad that outsystem lead us to this.

Iamar,

OutSystems is a great tool that will save you loads of time, and has an enormous amount of features. Yes, an IN in an Aggregate for Mobile would be useful, as would numerous other features people have requested in the Ideas section. But to say that it is "sad" that OutSystems "leads you" to work around a missing feature sounds very... ungrateful. As if OutSystems owes you something. It's not like not having an IN makes the product worthless, or something.


I agree, OutSystems is a very important tool, and i understand  that it must be "numerous other features", but, on a colaborative spirit, i'd like to say that classics concepts like (full SQL support)  should be in the top of the list. Because who bet in outsystem may be discover too late that such classics items are  missing. For example, Index on mobile local storage, there is an idea posted with more than one year with no reponse of the team. The implementation of this functionality is simple since the sqlite would do almost all the job. As i progress on my implementation and discover this missings i becoming more concerned. But thank you for your attention.



Well, concerning the index on mobile, the design philosophy is that you should keep your mobile data as flat as possible, and as simple as possible. Having complex indexes kinda defeats that goal. But I understand why somebody would want them.