Skip to Content (Press Enter)
UserImage.jpg
Dawn_
87Views
4Comments
Solved
Best way to filter data in aggregate that ignore whitespaces
Question
Aggregate
Reactive
Best Practices
OutSystems 11
Application Type
Reactive
Service Studio Version
11.54.3 (Build 62213)

Hello! Looking for some advice here!

I wanted to create a a filter in aggregate where when the SearchKeyword is "John doe" it will return all of the following results:

  • johndoe
  • JOHN DOE

I know there are a few approach to achieve this:

  1. I can use the built in Replace function in the filter so it became: ToUpper(Replace("Customer.Name", " ","")) = ToUpper(Replace("SearchKeyword", " ","")) 
  2. Create another attribute in the entity to store the formatted value of the name (no whitespaces and and all uppercase)
  3. Create a functional attribute in the aggregate to store ToUpper(Replace("Customer.Name", " ",""))  and do the filter on this attribute

But will these cause problems if there are a lot of records in the entity? Which one is the best way to achieve this? I've looked into creating a functional index on the entity but I'm not sure how to implement that.

Thanks beforehand!

0
0
07 Apr 2023
Copy Link
2021-10-17 12-36-16
Amreen Shaikh
Solution

Hi @StillLearning ,


As per Outsystems best practicies and documents the aggregates are optimized at the background and gives the result.


I have tried practically  with 35K records and it was working smooth for me.

I dont know how much you have in your db.


Thanks and happy cooding



Reagrds,

Amreen



See solution in context
0
0
07 Apr 2023
Copy Link
2023-02-09 12-36-42
Damian Fonville
Solution

@StillLearning 

Performing a SQL replace operation on 50,000 records should not cause any significant performance issues. 

However, if you still have concerns about performance, you can consider adding an additional field to the entity that stores the same data but without any whitespace characters. You can then use this field for searching the records, which should provide faster search results.

See solution in context
0
0
07 Apr 2023
Copy Link
2021-10-17 12-36-16
Amreen Shaikh

Hi @StillLearning ,


Please refer :

https://www.outsystems.com/forums/discussion/59812/aggregate-like-operator-search-for-words-even-if-not-sequencial/

Content.Title like "%" + Replace(Session.Content_SearchKeyword, " ", "%") + "%"  this syntax should work.


Reagrds,

Amreen


0
0
07 Apr 2023
Copy Link
UserImage.jpg
Dawn_

Hi Amreen Shaikh,

Thanks for reaching out! I've read through the link you posted and tried it out. While it does do the job, my concern is whether it will cause performance issue when the entity grow into having ten thousands or even more of records? I'm sorry as it seems I didn't phrase my question properly before.

Best regards,
StillLearning

0
0
07 Apr 2023
Copy Link
2021-10-17 12-36-16
Amreen Shaikh
Solution

Hi @StillLearning ,


As per Outsystems best practicies and documents the aggregates are optimized at the background and gives the result.


I have tried practically  with 35K records and it was working smooth for me.

I dont know how much you have in your db.


Thanks and happy cooding



Reagrds,

Amreen



0
0
07 Apr 2023
Copy Link
2023-02-09 12-36-42
Damian Fonville
Solution

@StillLearning 

Performing a SQL replace operation on 50,000 records should not cause any significant performance issues. 

However, if you still have concerns about performance, you can consider adding an additional field to the entity that stores the same data but without any whitespace characters. You can then use this field for searching the records, which should provide faster search results.

0
0
07 Apr 2023
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.