Hey OutSystems family!
While working on a reactive web app, I needed to filter a list of names that start with a specific letter, like "S". I assumed there would be a "StartsWith()" function… but turns out it's not available in Aggregates.
So after a bit of digging, I found a simple and clean workaround using the "Index()" function!
-Here's the expression I used in the Aggregate filter:
Index(Entity.Name, "S") = 0
Why this works:
--"Index()" returns the 'position' of the substring in the string.
-- If the position is '0', it means the string starts with that letter.
-- Works perfectly in Aggregates – no need for client-side logic or custom JavaScript!
If you're displaying the filtered data, don’t forget to "sort by Name" in the Aggregate's Sort tab to keep things clean and alphabetical.
Hope this helps someone out there working on similar logic!
If you’ve got alternative methods or improvements, I’d love to hear them.
Thanks,
Raksha Sharma
Hi Raksha ,
You can also use the LIKE operator. If you want to search for a Name that starts with S you would add the following filter to your aggregate: Entity.Name LIKE 'S%' .
Hi Beatriz,
Great point! [LIKE 'S%'] is such a clean and SQL-friendly solution.
Thanks for sharing it! I’ll definitely keep that in mind.
Quick question: Have you noticed any performance differences when using LIKE vs Index() in larger datasets?
I’m curious if one is more efficient or optimized in OutSystems’ backend.
Hi Raksha,
I've never used the Index() function in an aggregate, so I don't know.
Using a LIKE on an indexed attribute without a "%" at the start will be (a lot) faster than using Index/Substr with large datasets, as Index/Substr needs a full table scan, while LIKE can use an index if available (again, if you do not start with "%", because then you'll also get a full table scan). But on smaller data sets the difference will be negligeable.
Thanks for explaining that! Makes total sense.
I didn’t realize LIKE could take advantage of indexing while Index() and Substr() can’t. Definitely something to keep in mind when working with larger datasets. Appreciate the performance insight!
You're most welcome :).
Like Beatriz wrote, using LIKE here is the best solution.
Apart from that, there's also the Substr() built-in function, which I think is a more logical choice than Index(). It can also be used in an Aggregate filter, e.g.:
Substr(User.Name, 0, 1) = "S"
Thanks for bringing that up! I’ll give it a try too and compare how it works alongside LIKE and Index().
Really appreciate the insight!
Hi @Raksha Sharma ,
Refer this OML,I hope it will helpful to you
Sudha Narayanan
Thank You Sudha Narayanan! for the suggestion.
Substr() definitely looks like a clean and readable option. I’ll try it out and see how it compares with the other methods. Appreciate the help!