Using the IN clause in an Aggregate

Using the IN clause in an Aggregate

Is there a way to use the IN clause with an Aggregate filter?

This is what I'm trying to do (it works fine in an SQL widget):

SELECT {Task}.* 
From {Task}
WHERE {Task}.[Owner_Email] IN (@EmailList)
Order by {Task}.[Owner_Email]

As far as I know this is not available in aggregates yet.  Every time I needed to do something like that I settled for the Advanced SQL.
There is a way to do it.
If you build a string with all the emails on the list separated by a character of choice and then using the function index as a filter in the aggregate looking for the specific email on the string you just created.

email_string = "|||ijk@lmn.op||etc...."
and then using this filter in the aggregate
Index(email_string,"|"+{Task}.[Owner_Email]+"|") <> -1
If the funcion does not find the email on the list it returns -1 and will not list the element.
Thank you Filipe.  I'll try this the next time I need it.  Looks like it should work!
Filipe -

I'd be very cautious about that approach. It will perform a table scan, which will kill performance on a larger table.

An "IN" gets translated to a bunch of "OR"s, which means that indexing on the column will still work. If the table is large and you need this functionality, convert to an Advanced SQL and use IN, and index the column.