Hello All,


I am trying to filter a local aggregate on a mobile app using a list. Normally this would be done with an IN clause in SQL but I have read that there is no OutSystems equivalent so I have built the below code knowing that I will only have a maximum of 3 items in my list.

--------------

If(Locations.Length > 0, LocalLocations.Id = Locations[0].Id, False) or

If(Locations.Length > 1, LocalLocations.Id = Locations[1].Id, False) or

If(Locations.Length > 2, LocalLocations.Id = Locations[2].Id, False)

--------------

To my understanding, my Locations list should only be evaluated if there is enough items in the list to be evaluated. In other words, I will only compare LocalLocations.Id to Locations[0].Id if Locations.Length > 0

However when this aggregate is loaded I get an out of bounds error. 

The same logic put into an expression that simply writes text to the screen works. It just isn't working inside an aggregate. Can someone please point out what I might be missing or how I might filter my aggregate with my list locally?

Thank you. 

Hi Ruvan Muthu,

you can try use a listFilter component or if you prefere make an iteration to get the results that you want, see information in these two links https://www.outsystems.com/forums/discussion/26506/listfilter-usage/ 

https://www.outsystems.com/forums/discussion/48604/filtering-with-a-list/

Hope this help 

Best Regards


Hi Omar,

Thank you for those links. 

The solution of filtering the list of an aggregate (list1) with another list (list2) and then saving that as a final list (list3) is valid. However it seems very inefficient because now instead of filtering via SQL we are filtering each record of one list against each record of another list using list operations (which I'm guessing are less efficient than SQL)

I would accept this approach if I could make a single action which input two lists and a comparison rule and output a final list. The action would have to be able to take lists of any type so that I can reuse this same code in other situations. Do you know if I would be able to make an action input two generic lists and output a final filtered list?


Finally, I still don't see why my formula for the filter in the aggregate didn't work, if I can get that working then I believe that this could be the most efficient solution.


Thanks,

Ruvan

Hi Ruvan,

An workaround to use IN in aggregate is use index(<aggregate field you want to check>,<text variable with List of values>)>0

Regards,

Marcelo

Marcelo Ferreira wrote:

Hi Ruvan,

An workaround to use IN in aggregate is use index(<aggregate field you want to check>,<text variable with List of values>)>0

Regards,

Marcelo

Thanks Marcelo,

This does look like an interesting and plausible solution. I will ty it now.

Marcelo Ferreira wrote:

Hi Ruvan,

An workaround to use IN in aggregate is use index(<aggregate field you want to check>,<text variable with List of values>)>0

Regards,

Marcelo

Hi Marcelo,

I created a list of text values to use as the second argument to the index function however I am returned an error saying that the function requires a text data type instead of a text list data type. See below


Did I interpret your answer incorrectly? 

Essentially I have a list of Ids which I want to filter my aggregate against. 

Thanks,

Ruvan 

Hi Ruvan,

The second argument needs to be a test variable.lets say you want to return the IDs 11 2 and 4, that text variable should contain something like this ",11,,2,,4,". All the IDs should be between commas so you know the beginning and end of the Id and you don't return the 1 while you were looking for 11. The first argument should be ","+locallocations.id+","

Regards,

Marcelo

Marcelo Ferreira wrote:

Hi Ruvan,

The second argument needs to be a test variable.lets say you want to return the IDs 11 2 and 4, that text variable should contain something like this ",11,,2,,4,". All the IDs should be between commas so you know the beginning and end of the Id and you don't return the 1 while you were looking for 11.

Regards,

Marcelo

Okay. My understanding is that if I use Index(Location.id, ",11,,2,,4,") > 0 then the index function will literally be looking for an Id that is ",11,,2,,4," which would never return greater than zero. If you think that this will not be the case and the function will instead return > 0 if the Location.Id is 11, 2 or 4 then this is great. 

I will try it now in any case. Thank you for your help.

Ruvan


Index check if the first argument exists on the second one that's why the IDs are between commas and the first argument should be ","+locallocations.id+","

Regards,

Marcelo