19
Views
10
Comments
Solved
problem with search
Question

hello developers, i have the list record with data type of text for both column. and i created the search function for the list record

when i search with text "Gender Contains M AND Active Status CONTAINS Active" in the search box, the result not appear. but if i search with "Gender Contains M" or "Active Status CONTAINS Active" the result appear. The problem is that i can't put "AND" in search text.

And i have filter in aggregate

TableName.ColumnName like "%"+Search+"%"

what is wrong with "AND", how can i solve that?


Regards, Phone.


Rank: #456
Solution

Hi Phone,

You should be using the Advanced SQL here. Please note that OutSystems is generating the complete query when you use Aggregate and thus, your AND keyword is messing up. Understandably, OutSystems is using the SQL keyword to generate the Query. I hope this clarifies the need.


@Jitendra Yadav, what you are doing is local list filtering not the Aggregate call. Try to manually run the aggregate and check the result.

mvp_badge
MVP
Rank: #128

Phone wrote:

hello developers, i have the list record with data type of text for both column. and i created the search function for the list record

when i search with text "Gender Contains M AND Active Status CONTAINS Active" in the search box, the result not appear. but if i search with "Gender Contains M" or "Active Status CONTAINS Active" the result appear. The problem is that i can't put "AND" in search text.

And i have filter in aggregate

TableName.ColumnName like "%"+Search+"%"

what is wrong with "AND", how can i solve that?


Regards, Phone.


Hi,


I don't think any issue what let me guess maybe because AND is reserved keyword in SQL.

I will try this.


Rank: #43

Phone wrote:

hello developers, i have the list record with data type of text for both column. and i created the search function for the list record

when i search with text "Gender Contains M AND Active Status CONTAINS Active" in the search box, the result not appear. but if i search with "Gender Contains M" or "Active Status CONTAINS Active" the result appear. The problem is that i can't put "AND" in search text.

And i have filter in aggregate

TableName.ColumnName like "%"+Search+"%"

what is wrong with "AND", how can i solve that?


Regards, Phone.


Hi

I think as per my understanding you need to use advance sql like below.

Select * from Person where Age IN (@Parameter) and Name like '%'@Name'%'


Rank: #1243

Yeah, i think AND should not be reserved because it is in the text.

mvp_badge
MVP
Rank: #128

use () braces for every clause before OR.

I mean like below 

(DynamicGroupRec.Title <> "" and DynamicGroupRec.Title like "%"+DynamicGroupRec.Title+"%")

Rank: #456
Solution

Hi Phone,

You should be using the Advanced SQL here. Please note that OutSystems is generating the complete query when you use Aggregate and thus, your AND keyword is messing up. Understandably, OutSystems is using the SQL keyword to generate the Query. I hope this clarifies the need.


@Jitendra Yadav, what you are doing is local list filtering not the Aggregate call. Try to manually run the aggregate and check the result.