Hi Team,
Good day! I have my first aggregate that returns all the Ids and I want to use them to my second aggregate as a filter. How do I perform that, like in traditional SQL I want to put them in IN() operator?
Thanks,
J P
J P wrote:
Hi J P,
Based on my screenshot, my first aggregate returns all the Ids and I used ListAppendAll to map the field that I need. On the String_Join I have map again the field that I need and use "#" as a separator.
On the second aggregate, I used "Index("#"+ String_Join2.Text + "#", "#" + Technician.TerritoryId + "#") <> -1" as my filter. This performs the IN operator. The return or test values should look like "#21#22#23#" and so on.
Hope this helps.
Regards,
Raymark
Raymark Cosme wrote:
This is exactly what I needed right now! Thank you so much. Works great!
Thanks @Raymark Cosme ,
This works for me too.
Thanks a lot for sharing it.
Yogesh
This works for me! Thanks!
Even though the sample Raymark posted can help you, it's not a good approach in terms of best practices - hard to understand, to much code to get a dataset.
Aggregates do not have the IN operator and this is already an Idea with a lot of votes, please check this to help the idea get stronger: https://www.outsystems.com/ideas/2224/in-clause-in-aggregates
Sometimes the IN operator is not the best solution (has limitations and can be a bottleneck performance-wise). Depending on what you want to achieve, using a JOIN or filter the results afterwards can be a simpler and better solution.
Be mindful of what you want to achieve before jumping to a solution :)
Good one, Raymark. Very insightful.
Super Like. I was struggling a bit to get the results
@all How does this performs (performance wise)?
Hi , Raymark Cosme
this is very old topic but very helpful for me , how I can apply more than one index function in aggregate similar to your previous answer. Single index function work for me on one attribute but when I applying two index function with AND on second attribute doesn't showing output .
Thanks in adv.
I tried to use index in aggregate but it became a bug. Here's the scenario.
I was trying to get the records of the ContractVersion table where I have the specific list of IDs called VersionIDs (comma separated) passed to my function.
So I used the filter index(ContractVersion.Id,VersionIDs) <> -1
Now it became a bug in the sample test data:
VersionIDs: (234,123)
The aggregate returned a record of ContractVersionId 123,234,1234 because if you index 234 of course it can be found in 1234 and any other IDs that contain 234